texts: Adding to MongoDB a new shard. How to minimize Impact on server loads

texts:
Is the possible to minimize Impact on server loads while adding to MongoDB 4.2 a new shard?

I have found this recommendation. It could help but I sure should be few more thing which we can do.

  Prior to a new shard, I would first explore setting your read
  preference to SecondaryPreferred. This will allow your primary node in
  each RS to leverage more resources for Write operations. (c) lost

Thanks


texts: Running multiple SQL Server versions on Always On

texts:
I need to install 3 instances of SQL Server, using 3 difference SQL versions on 2 VM's.

I need to install (all Enterprise Ed) 2008, 2012 and 2014.
I want to use Always On, however not sure how I can do this given it is not available with 2008 so I was thinking I can do:
2008 : Active/Passive failover using Windows clustering
2012/4: Always-On
But the above sounds a bit clunky ...

Anyone done this before or idea on the best way to implement the above please?

Thank you


texts:
i have a problem with EXECUTE sp_executesql  and stuck with it.
my query is as follows

   DECLARE @TABLE_NAME VARCHAR(5)='#T'
   DECLARE @TABLE_ID INTEGER=0
   DECLARE @QRY NVARCHAR(MAX) =''
   DECLARE @TABLE VARCHAR(3)
 DECLARE CRSR_WAGES CURSOR FOR SELECT Code,Name , ROW_NUMBER() OVER (ORDER BY U_Prrty, DocEntry) Slno, COUNT(Code) OVER (PARTITION BY OBJECT) ColNos
                                     FROM  [@CCS_WAGE]
                                     WHERE  U_DISPP = 'Y' AND U_DEDWT='E'
OPEN CRSR_WAGES
FETCH NEXT FROM CRSR_WAGES INTO @WAGECOD,@WAGE_DESC,@SI_NO,@COL_NOS
WHILE @@FETCH_STATUS=0
BEGIN
    SET @TABLE=@TABLE_NAME+CONVERT(VARCHAR(2),@TABLE_ID)
    SELECT @TABLE AS 'TABLE'
    SET @QRY='(SELECT empno, amt AS '+'['+@WAGE_DESC+']'
    SET @QRY=@QRY+' INTO '+@TABLE
    SET @QRY=@QRY+' FROM CCS_PYMONTHRESULT WHERE wgcod='''+@WAGECOD+''')'
    SELECT @QRY
    EXECUTE sp_executesql @QRY
    SET @TABLE_ID=@TABLE_ID+1
    DECLARE @STAR AS NVARCHAR(50)='SELECT * FROM ' + @TABLE
    SELECT @STAR
    EXECUTE sp_executesql @STAR
    FETCH NEXT FROM CRSR_WAGES INTO @WAGECOD,@WAGE_DESC,@SI_NO,@COL_NOS
        END
CLOSE CRSR_WAGES
DEALLOCATE CRSR_WAGES

but the problem is when execute this error message comes like this
Invalid object name '#T0'.
Invalid object name '#T1'.
Invalid object name '#T2'.


texts: How to find what tables are stored in what data files

texts:
I know that tables can span across multiple data files

The tables are already created. I did not create them myself. I'm trying to find out what table(s) are using the highest amount of IO. I was able to find out what data files are using high IO using the sys.fn_virtualfilestats function.

Question : Is there a way to find out what files the tables are associated with?


texts: Resource Governor seems to increase resource usage

texts:
We are in the unfortunate position of running a data warehouse on the same instance as our production OLTP system.  Recently a "power user" joined our company and has begun running SQL statements against the DW that are hogging resources.  Yesterday he ran a query that sent our page life expectancy from the usual 200K+ seconds down to 39.

I have thought of two solutions to this problem.  The first is to move the data warehouse to an Azure database.  This protects our OLTP systems from running out of resources, but it comes with a monetary cost.  The second solution is to use Resource Governor to limit the resources available for either specific users or the entire DW database.  This seems like a free solution, so it is the one that I am investigating first.  I have never used Resource Governor before, so I set up a few tests to make sure it would work as expected.  I first ran a few big queries with no Resource Governor set up and recorded the CPU usage, Reads, and Duration.  Then I created the proper Resource Governor settings and re-ran the queries.  The duration rose as expected, but I was surprised to also see the CPU and Reads increase drastically.  The CPU usage more than doubled and the Reads increased tenfold.  It did protect the Page Life Expectancy from dropping.  Is that a typical occurrence when using Resource Governor?  Does anyone have any other ideas for limiting specific users or databases from hogging resources?


texts:
I have frequent access error log like below:

  Error: 17806, Severity: 20, State: 14.
  SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: 222.186.XX.XX]

I see many IPs that are trying to login to my servers.

Tell me please, I don't know what I have to do in this situation, Do you prefer to change my SQL SERVER port or not?

I follow this answer but I don't get right answer:
SQL Server error log entry : Error: 17806, Severity: 20, State: 14


texts:
I have copied this link from StackOverflow as it is marked as out of topic there and is waiting to be transfered here. So, here it goes:

Occasionally, without any identifiable pattern, MySQL restarts. There are no entries in the error.log or mysql.log (other than booting and checking tables).

Only thing that I could have identify is this exempt from dmesg:

[58129723.252413] type=1400 audit(1405923841.567:549): apparmor="DENIED" operation="open" parent=1 profile="/usr/sbin/mysqld" name="/dev/tty" pid=12848 comm="mysqld" requested_mask="rw" denied_mask="rw" fsuid=105 ouid=0
[58129724.419314] init: mysql main process (26338) terminated with status 1
[58129724.419347] init: mysql main process ended, respawning

Does this mean that AppArmor is killing mysqld because it is try to access it's /dev/tty and the access is denied?

Why would mysqld need to access it's /dev/tty and more should it be allowed by mysqld profile? If it constantly needs access to /dev/tty shouldn't it be killed right away, and not occasionally like this? Period between two 'kills' can be a month sometimes.

Thanks in advance for quick answer


texts: How to get last and first occurrence of items into separated columns?

texts:
I do have the fallowing table:

id name     value
1  Daniel   3
2  Daniel   7
3  Daniel   2
4  Carol    9
5  Carol    4
6  Carol    9
7  Ray      5
8  Charles  1

I would like to get the first and the last value (ordered by id) in a table like:

name    value_1st  value_last
Daniel  3         2
Carol   9         9
Ray     5         NULL
Charles 1         NULL

How can I write a query to get the last and first occurrence of values grouped by name and ordered by id, like this?


texts: SQL Server Profiler showing an update with duration 0ms, but then 5 second delay

texts:
I'm not even sure how to phrase this question - apologies if it falls outside the realm of this site... I guess my main question is: What to look for when an update seems quick, but the overall transaction is deadly slow?

Situation

Application is timing out as a result of slow-running queries.

Used SQL Server Profiler to inspect the operations being performed in the timeout period.

Duration values are all 0ms or close to 0ms.

There regular, specific queries that start and then there's no activity shown in the monitor for 5 seconds/10 seconds (as shown in start/end timestamps), but duration still 0ms...

These regular, specific queries are always the same queries, always UPDATE TABLE1 SET INNER_ID = @p1 WHERE PRIMARY_KEY_ID = @p2 or INSERT INTO TABLE2 ...

Running the same queries directly in MSSQLMS, they seem to run fine in good time (00:00:00).

Both the tables in question have a fair few indexes on them.

I've tried...

At first, I noticed all the primary key indexes were non-clustered. So I've dropped/recreated each primary key with a clustered index. This helped initially to get the durations down from ~150ms to 0ms.
The database was an old SQL Server 2005 database that has been updated several times (now SQL Server 2016). It still had compatibility level set at 2005, so I've upped that to 2016. No change...
I'm convinced it's index-related, so I've inspected the indexes on these tables and tried dropping them - no change - recreating them - no change...
A colleague suggested enabling Legacy Cardinality Estimation and Query Optimizer Fixes. I had no other straws to pull, so I tried those... no change.

Question

What more can I look at here?

What is happening for 5 seconds?


texts: Agent Xps getting disabled automatically

texts: In one of SQL Server 2012 server, agent Xps is getting disabled automatically. There is no set pattern for the same and the issue started all of a sudden. Through profiler it was found that application named SQL AGent- Enable\disable Agent Xps is disabling the XPs. And the later is enabled by SQL Agent-Initial BootProbe.


texts: What type of datastore should I use for high volume time series data?

texts:
Imagine a project which requires to be able to process 50,000+ (tiny ints) writes per second. The data, once inserted, won't change. The data is a measurement on a particular time. Sub second resolution is not required (two measurements of the same device can be a second apart).

We are willing to buy/rent more hardware but not more expensive hardware (no fancy RAID etc).

The data is ordered in sets (per device), a couple of hundreds. And should thus be shown/graphed. There is no relation between devices but we do want to group them (showing, for example, 10 graphs).

The only other requirement is that the datastore is open source and *nix based and has reasonable (community) support.

What kind of datastore would you use?


texts: Update query taking 8x times in hot standby environment of Postgresql

texts:
I configured streaming replication (hot standby) and tested the update query on it. The update query is taking  8 times more when compared with stand-alone PostgreSQL (9.2 version) system.

Some of the postgresql.conf parameters I used were:

wal_level = hot standby
max_wal_senders = 5
max_wal_segsize = 32
archive_timeout = 60 sec
checkpoint_timeout = 5 min
archive_command = 'rsync .....'

How can I speed up the execution of update query on streaming replication?


texts: How to copy non clustered indexes in transactional replication

texts: I create transactional replication ,but forget to include non clustered indexes and now my subscriber have only clustered indexes (primary key) and I want to transfer all nonclustered indexes to subscriber.Based on this answer http://www.sqlservercentral.com/Forums/FindPost1325516.aspx is good idea to script all non clustered indexes on publisher and then execute it on subscriber or is there another way which is better ?


texts: Fail to login on Sql server with DNS but not with IP

texts:
I'm facing a strange problem :

I have a sql server on the IP 80.248.XXX.XXX

I've also create an Alias on SQL Server like servername.sql.mydomain.com with this tutorial :
https://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/

When I use SSMS, if :

I use the IP with user sa, it's working like a charm
I use the domain name servername.sql.mydomain.com, with user sa

I have an error : (in French)

Impossible de se connecter à servername.sql.mydomain.com.

===================================

Échec de l'ouverture de session de l'utilisateur 'sa'. (.Net SqlClient Data Provider)

------------------------------
Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
Nom du serveur : servername.sql.mydomain.com
Numéro de l'erreur : 18456
Gravité : 14
État : 1
Numéro de la ligne : 65536

And I do not understand why...


texts:
How can I concatenate two psql (PostgreSQL client) variables? I want to generate an absolute path by concatenating a directory path variable and a filename variable.

I've tried this:

\set path '/tmp/'
\set file 'foo'
\echo :path:file

But psql puts a space between the path and the file, and outputs:

/tmp/ foo


texts: Executing a stored procedure which selects and inserts into tables in SQL Server

texts: I am a bit confused on the permissions required to execute stored procedure. For example, if you have 2 tables and a stored procedure which reads data from Table1 and inserts data into Table2, will execute permission on the stored procedure and select permission on the tables be enough? In theory I am finding that this is given as a solution but practically I am finding that a user would need execute permission on the stored procedure, select permission on the tables and insert permission on Table2.


texts: Getting Visual Studio to deploy a UDF to Return NVARCHAR(MAX)

texts:
I have a SQLCLR stored procedure with the following signature in Visual Studio 2013:

[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlString ExecSql2Json(SqlString sql)

Or alternatively I've tried:

[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlChar ExecSql2Json(SqlChar sql)

Either way the parameter and return type gets defined as NVARCHAR(4000). I'd like them to be NVARCHAR(MAX).

I know I can manually write my own CREATE PROCEDURE statement to have fine grain control. Is there any option anywhere to change the deploy DDL generated by SQL Server or do I just have to manually add a script to drop and read the UDF with the proper signature?


texts:
There is a table on my database that stores a path for video files used by my website. Those files are stored in a SSD disk.

path character varying(255)
id serial

The path will always be like this:

/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)

Here are some examples of valid paths:

/files/client/743052/attachment/2123598/main
/files/client/743052/attachment/2123598/thumbnail
/files/client/1475296/user/3541234/main
/files/client/1475296/user/3541234/thumbnail

Question:
How can I create a Trigger that checks the value WHEN INSERTED and, if the row is less than the minimal path (/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)) then raise an exception?

I'm using PostgreSQL 9.1.

UPDATE:

Some more examples here:

/files/client/45345/user/3542341234/main -- PASS
/files/client/45345/user/3542341234/thumbnail -- PASS
/files/client/253623/attachment/35334/main -- PASS
/files/client/253623/attachment/35334/thumbnail -- PASS
/files/client/45312341245/users/12545/main -- WRONG!
/files/client/45312341245/users/12545/thumbnail -- WRONG!
/files/client/45345/attachment/1223545/mains -- WRONG!
/files/client/45345/attachment/1223545/thumbnails -- WRONG!


texts: Does an empty entity make any sense in an entity-relationship diagram?

texts:
I have an entity that has nothing else than a surrogate key as a primary key. I use it as an element made of a group of other elements
For example :

  TABLE SchoolClass (classNumber INTEGER PRIMARY KEY)

  TABLE Students (SudentName TEXT PRIMARY KEY, studentClass INTEGER
  FOREIGN KEY)

Does it make sense ? The School Class entity does not store any information, but it's still used to regroup all the students in the same class.


texts: Need some help building a query to get info from several tables

texts:
I have this semi-complex table structure:

And I need to return this data:

{
  "id": "10111001",
  "rep_id": "30000001",
  "targets_id": "60011001",
  "brands_id": "10010001",
  "brand_name": "Quillivant XR",
  "messages_id": "10111001",
  "message_text": "Thank you for taking time to speak with me today. Based on our discussion, I&apos;ve attached additional materials about Quillivant XR&trade; (methylphenidate HCI) CII. I look forward to following up with you in the near future. Thank you.",
  "media": [
    {
      "media_id": "10011001",
      "title": "Pfizer -- Largest Pharmaceutical Company In T",
      "description": "The New York-based giant is one of the largest pharmaceutical companies in the world, based on revenues. Known for famous drugs like Lipitor and Viagra, Pfizer Inc. (PFE) has acquired several big names over the years including Pharmacia, Warner-Lambert and Wyeth to become one of the world's largest research-based pharmaceutical companies. It has also been the target of lawsuits and has had to settle with the US Department of Justice, four times in the last 10 years.",
      "thumbnail_url": "http://www.underconsideration.com/brandnew/archives/pfizer_logo_detail.gif",
      "viewedAt": "2013-06-27 20:18:33"
    },
    {
      "media_id": "10011002",
      "title": "Proxy Statement for 105 Annual Meeting of Sha",
      "description": "A proxy statement is a statement required of a firm when soliciting shareholder votes. This statement is filed in advance of the annual meeting. The firm needs to file a proxy statement, otherwise known as a Form DEF 14A (Definitive Proxy Statement), with the U.S. Securities and Exchange Commission.",
      "thumbnail_url": "http://www.underconsideration.com/brandnew/archives/pfizer_logo_detail.gif",
      "viewedAt": "2013-06-27 20:18:33"
    }
  ],
  "category": "sent",
  "createdAt": "2013-06-27 20:18:33",
  "openedAt": "2013-06-27 20:18:33",
  "viewedAt": "2013-06-27 20:18:33"
}

As you can see from targets to reps all the tables are almost involve on the result. I started this query:

SELECT brands.id, brands.name, targets.id, reps.id, messages.* FROM messages WHERE reps.id = :rid LIMIT 10

But I don't know how to properly do the FROM clause and/or how to order the JOINS and if will be better to use LEFT or just JOIN. Also I am not sure if I can get every media for each row or I need to separate that into two queries. Can any help me to build this queries to get the expected output?


texts: what is more important on a query execution plan, cardinality or selectivity?

texts:
The paper "How good are query optimizers, really?" says that cardinality is the thing that most influence to find a better query execution plan in RDBMS. Summary of this paper can be found here. However, other posts that explain the difference between cardinality and selectivity point that selectivity is more important.

Example:

  select max(price) from tickets where country = ‘CANADA’;

  Let us say we have a table: tickets with 180 rows. Based on the above
  example, there are 10 rows in the table for country = ‘CANADA’. The
  query returns only one row, the max(price). selectivity = ?
  cardinality = ?

  selectivity = number of rows accessed/total number of rows = 10/180 =
  0.05 (5% of the rows were accessed) cardinality = number of rows accessed = 10

My opinion is that selectivity is only found once we have the cardinality, therefore cardinality remains the more important thing to find the best query execution plan. What do you think? Could you please answer with a detail explanation about your understanding? Thanks


texts: mysql 8 datetime default throwing and error when forward engineering

texts:
I am trying to create a schema by forward engineering a model eer diagram. But its giving an error on the datetime fields when using the default current_timestamp which is allowed in mysql 8 for datetime fields.
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 ;
USE `test` ;

-- -----------------------------------------------------
-- Table `test`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`users` (
  `user_id` CHAR(36) NOT NULL,
  `email` VARCHAR(225) NOT NULL,
  `password` VARCHAR(20) NOT NULL,
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME(6) NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

ERROR: Error 1067: Invalid default value for 'created_at'

I don't know why this is. I thought it had something to do with the variables mysql workbench sets when forward engineering. But I can't figure out what it is as it looks like they are set in a normal way.
Is it due to the variables or the workbench? Also why are the variables used? Can I run the script without them or are they necessary? To be frank, I am a bit put off by the use of the work "OLD" in the variables. Is it using version 5.6 defaults? Because I want the all the new settings on mysql 8 to be used by default.


texts:
Here are the columns of an SQL Table:

Course_Code
Course_Name
Tutor_ID
Tutor_Name
Student_No
Student_Name
DateOfBirth
Gender
LastAttDate

By checking all the NF1 requirements below, I believe this table is NF1.

All data is atmoic
There is no grouping of columns.
Each column can be identified by a primary key. (Course_Code)

But in this website where I got that question, they make some modifications on the table to make it NF1. I believe that these modification was unnecessary and table was already in NF1.

am I missing something here?


texts: How to start mysqld with read-only data directory?

texts:
I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag to my.conf, I get the following errors:

root@2380610236d1:/# mysqld --innodb-read-only
140807 19:19:38 [Warning] Can't create test file /var/lib/mysql/2380610236d1.lower-test
140807 19:19:38 [ERROR] mysqld: Can't create/write to file '/var/lib/mysql/aria_log_control' (Errcode: 13 "Permission denied")
140807 19:19:38 [ERROR] mysqld: Got error 'Can't create file' when trying to use aria control file '/var/lib/mysql/aria_log_control'
140807 19:19:38 [ERROR] Plugin 'Aria' init function returned error.
140807 19:19:38 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
140807 19:19:38 [Note] InnoDB: Started in read only mode
140807 19:19:38 [Note] InnoDB: Using mutexes to ref count buffer pool pages
140807 19:19:38 [Note] InnoDB: The InnoDB memory heap is disabled
140807 19:19:38 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
140807 19:19:38 [Note] InnoDB: Compressed tables use zlib 1.2.8
140807 19:19:38 [Note] InnoDB: Using Linux native AIO
140807 19:19:38 [Note] InnoDB: Using CPU crc32 instructions
140807 19:19:38 [Note] InnoDB: Disabling background IO write threads.
140807 19:19:38 [Warning] InnoDB: Unable to open "./ib_logfile0" to check native AIO read support.
140807 19:19:38 [Warning] InnoDB: Linux Native AIO disabled.
140807 19:19:38 [Note] InnoDB: Initializing buffer pool, size = 256.0M
140807 19:19:38 [Note] InnoDB: Completed initialization of buffer pool
2014-08-07 19:19:38 7f2bc5a247c0  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
140807 19:19:38 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
140807 19:19:38 [ERROR] InnoDB: The system tablespace must be writable!
140807 19:19:38 [ERROR] Plugin 'InnoDB' init function returned error.
140807 19:19:38 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140807 19:19:38 [Note] Plugin 'FEEDBACK' is disabled.
140807 19:19:38 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Couldn't start tokudb because some other tokudb process is using the same directory [/var/lib/mysql/] for [environment]
140807 19:19:38 [ERROR] Plugin 'TokuDB' init function returned error.
140807 19:19:38 [ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.
140807 19:19:38 [ERROR] Unknown/unsupported storage engine: InnoDB
140807 19:19:38 [ERROR] Aborting

140807 19:19:38 [Note] mysqld: Shutdown complete

How can I start the database server with a read-only data directory?


texts: Find percentage of NULL storage in Oracle table

texts:
I have a table in Oracle 11g Standard One Edition:

table1

col1 col2 col3 col4 col5 col6 col7 col8       col9 col10 col11
1    NULL 2    3    4    5    NULL NULL       19   21    22
1    NULL 2    3    4    5    NULL 1 Jan 2009 19   21    22
1    NULL 2    3    4    5    NULL NULL       19   21    22
1    9    2    3    4    5    A    NULL       19   21    22
1    NULL 2    3    4    5    B    NULL       19   21    22

The table desc is:

Name                 Null Type
-------------------- ---- -------------
COL1                      NUMBER
COL2                      NUMBER
COL3                      NUMBER
COL4                      NUMBER
COL5                      NUMBER
COL6                      NUMBER
COL7                      VARCHAR2(255)
COL8                      DATE
COL9                      DATE
COL10                     DATE
COL11                     VARCHAR2(255)

I need to find out what is the percentage of storage a table consumes with values NULL?

Example: table1 storage consumed is 1 GB, and NULLs inside it consumes 100MB, so, NULL takes up 10% of the storage.

Also, are there alternate representations of NULL in ORACLE?


texts: Not able to make a query that give me the result that I want in MySQL with 4 tables junction

texts:
I need help to adjust my query to be able to have the result show in bottom.

This is my DB illustration:

And this is the command  CREATE TABLE for each table:

CREATE TABLE `produits` (
  `sku` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `produits` (`sku`, `description`) VALUES
('1', 'Produit 1'),
('2', 'Produit 2'),
('3', 'Produit 3');

CREATE TABLE `produitssite` (
   `pssku` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
   `psloc` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
   `psabcd` varchar(10) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `produitssite` (`pssku`, `psloc`, `psabcd`) VALUES
('1', 'A', 'd'),
('1', 'B', 'd'),
('2', 'A', 'c'),
('3', 'A', 'b'),
('4', 'C', 'd');

CREATE TABLE `stocksminmax` (
  `smmsku` varchar(250) CHARACTER SET latin1 NOT NULL,
  `smmloc` varchar(250) CHARACTER SET latin1 NOT NULL,
  `smmphy` float(20,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `stocksminmax` (`smmsku`, `smmloc`, `smmphy`) VALUES
('1', 'A', 10.00),
('1', 'B', 20.00),
('2', 'A', 20.00),
('3', 'B', 5.00),
('4', 'B', 10.00);

CREATE TABLE `ventes` (
  `ventesku` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ventelocalisation` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `vente1805` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `ventes` (`ventesku`, `ventelocalisation`, `vente1805`) VALUES
('1', 'A', 500),
('1', 'B', 250),
('2', 'B', 600),
('3', 'A', 400),
('4', 'A', 100);

Here are the three requests that I try to obtain in 1:

SELECT * FROM produits RIGHT JOIN stocksminmax ON sku = smmsku

SELECT * FROM produits RIGHT JOIN produitssite ON sku = pssku

SELECT * FROM produits RIGHT JOIN ventes ON sku = ventesku

But I also try to JOIN the location on each table in my request : smmloc, psloc and venteloc

This is what I try to obtain:

Thanks for your help.


texts: How do you submit a List of items to a Stored Procedure to limit results?

texts:
Possible Duplicate:
  Passing array parameters to a stored procedure

Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read:

WHERE CustomerID in (@CustomerIDs)

Is this possible?

Edit:

To clarify and answer Nick's question, I'd like to pass in a List of values from C# to SQL Server and be able to do a select similar to the SQL I posted above.  This would be preferable to getting more data than needed and having to search through it since there is an enormous amount of data.


texts: Recover innodb database from ibdata1 and *.frm files only

texts:
I have a problem on my server.

I had a hard disk error on my server, and I restarted it. After that, I started wamp server software, but mysql services were not started.

I checked all mysql data, there were mydb/mytable.frm, mydb/db.opt, ib_logfile0, ib_logfile1, ibdata1 and other Configuration Settings files available; but mydb/mytable.ibd was not available.

Is it possible to recover my data? If yes, how?


texts: Trace flag and which need to be turned off and Why

texts: In SQL server there are so many trace flags. Why some of them need to be turned off. Some where i saw that trace flag 8017 need to be turned off. So i want to know which trace flag need to be tuned off and Why?


texts: MongoDB "select x from y where id in" another table

texts:
I'm trying to understand how can I achieve this in MongoDB:
select *
from Table1
where id in
(selct id from table2)

I'm trying to aggregate but I just can't understand how JSON works:
db.Collection1.find
    (
            {"Field_from_Collection1":
                {$nin:db.Collection2.distinct("The_Field_I_want_to_compare")

                }

            }
    ).limit(1)

I need to find all information from collection1, but only if the ID is in collection2.
The reason I'm doing this is that I need to find the documents in Collection1, that has specifics IDS, and it's 600k IDS to look for.
as a SQL Server DBA, I'm really struggling to do this.


texts: Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?

texts:
It is well-known that SCHEMABINDING a function can avoid an unnecessary spool in update plans:

  If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure you specify the SCHEMABINDING option during creation of the UDFs. This will make the UDFs schema-bound and ensure that the query optimizer does not generate any unnecessary spool operators for query plans involving these UDFs.

Are there any other advantages of SCHEMABINDING a function, even if it does not access data?


texts: Updating column in table a from top 1 in column b with foreign key relationship

texts:
I've got two tables:

Table A:
PlayerProfileId (PK),
Email,
HighScore,
Name,
UserName,
Date

1   g@s.com 37  Gustav  MegaBoi 2018-09-14 09:44:07.4505232

Table B:
ScoreId (PK),
Points,
Date,
PlayerId (FK)

3   36  2018-09-11 14:18:21.9372840 2

So every Player has a one to many relationship to their scores in table b.
I'm trying to write an update function that can update every row (players) highscore column.

Doing something like this works:

update PlayerProfile
set PlayerProfile.HighScore = a.Points
from
    (select top 1 * from Scores where Scores.PlayerProfileId = 3 order by Points desc) as a
    where PlayerProfile.PlayerProfileId =  3

But I don't want to have to manually change the id number of the the players to update.

How can I change my update to automatically to this for all players?

What I was thinking is doing something some kind of join on table A and B and then only selecting the top 1 of each "group" and then update the highscore of that selected row. But I'm stumped as to how I can do this.

select *
from PlayerProfile
join Scores on PlayerProfile.PlayerProfileId = Scores.PlayerProfileId

If I could (for each playerId) only take the row with top Points and then use update the highscore through that row that would be great.


texts: How to Move a whole partition to another tabel on another database?

texts:
Database: Oracle 12c

I want to take single partition, or a set of partitions, disconnect it from a Table, or set of tables on DB1 and move it to another table on another database. I would like to avoid doing DML to do this for performance reasons (It needs to be fast).

Each Partition will contain between three and four hundred million records.
Each Partition will be broken up into approximately 300 Sub-Partitions.

The task will need to be automated.

Some thoughts I had:

Somehow put each partition in it's own datafile upon creation, then detaching from the source and attaching it to the destination?
Extract the whole partition (not record-by-record)
Any other non-DML Solutions are also welcom

Example (Move Part#33 from both to DB#2, preferably with a single, operation):

 __________________              __________________
| DB#1             |            | DB#2             |
|------------------|            |------------------|
|Table1            |            |Table1            |
|   Part#1         |            |   Part#1         |
|   ...            |            |   ...            |
|   Part#33        |    ---->   |   Part#32        |
|      Subpart#1   |            |                  |
|      ...         |            |                  |
|      Subpart#300 |            |                  |
|------------------|            |------------------|
|Table2            |            |Table2            |
|   Part#1         |            |   Part#1         |
|   ...            |            |   ...            |
|   Part#33        |    ---->   |   Part#32        |
|      Subpart#1   |            |                  |
|      ...         |            |                  |
|      Subpart#300 |            |                  |
|__________________|            |__________________|


texts: Stop SQL Server 2012 From Highlighting Implicitly Passed Temp Tables "Invalid Object"

texts:
I tend to use Temp Tables to pass large amounts of data from one stored procedure into another stored procedure. However, something that's always annoyed me is that, because it's implicitly passed/shared with the session, the sub stored procedure has no knowledge of it and constantly highlights table/column references as "Invalid Object".

Is there any way to "force" knowledge of a Temp Table's schema into a stored procedure?

--Yes, this is awful.  Quick and dirty pseudo code.
create procedure usp_myProc
(
    create table #temp
    (
        id int
        ,data int
    )

    insert into #temp(id, data)
    values(1, 1)

    exec usp_mySubProc;
)

create procedure usp_mySubProc
(
    --#temp and Data will both be highlighted as Invalid Objects
    --Because I'm updating the table, a Table Valued Parameter won't work
    while(1)
    (
        update
            #temp
        set
            Data = 1
    )
)


texts: The effect of a Primary Key on a blocking situation

texts:
I'm studying about "Transaction and concurrency","Deadlock" and related stuff for some time. I have a question regarding this matter.

The situation is that two records (AccountNumber AW00012597 and AW00012596) are updated by two different sessions :

SESSION 60:

 begin transaction
 update CustomerTest
 set ModifiedDate = getdate()
 where accountnumber = 'AW00012597'

SESSION 63:

 begin transaction
 update CustomerTest
 set ModifiedDate = getdate()
 where accountnumber = 'AW00012596'

The problem is when I put a clustered index on column AccountNumber (Or set this column as PrimaryKey) , Both Transaction complete without any problem but when I drop the index (When the table doesn't have any indexes or primarykeys) session 63 gets blocked !!

I want to know what exactly is the reason behind this? Cause the transactions are updating different rows and they are holding "Exclusive Locks" on different rows (According to what I've read so far) . So what would be the effect of a clustered index?

Thank in advance


texts:
I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true.

After I run the query delete from abc.test, I want to calculate the database size of abc. Here is the query I use:

SELECT
    table_schema "name",
    sum( IF(engine = "MyISAM", data_length + index_length -  data_free,
    data_length + index_length)) "size"
FROM information_schema.TABLES
where table_schema like "abc";

The strange thing is that I find the database size doesn't decrease at all, however the data in "test" is gone.

I've done this kind of test many times, this strange behavior happens sometimes.

I'm using percona mysql server 5.5.29-rel29.4.

Can anybody tell me what is wrong?

Update:

Actually, I use another thread to check the database size periodically.


texts: Maintaining SQL Server 2008 R2 on Windows 10

texts:
I have to run SQL Server 2008 R2 on Windows 10 but it is not supported and so I am not getting any updates. Installing a later version of SQL Server is not an option. I can find all the service packs and cumulative updates online.

Questions:

Is there a way to get updates automatically?
If not, do I have to install all the service packs in sequence, or can I install only the most recent.


texts:
I am attempting to attach a .mdf file to my SSMS install.  I go through the steps and at the very end I get this message.

What is causing the .mdf file to not attach?

Also, I have verified that the .mdf file is NOT read only.

EDIT
If I run Select @@Version I get the output that I have 2008 which is what my .mdf file requires..


texts: Reviewing ways to generate monthly tables using PL/SQL automatically

texts:
I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL.  The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic variables, etc.) and are later JOINed to produce customer level table with all extracted metrics.

As examples, toy datasets for each table can be created with the following code:

CREATE TABLE customerData
(msisdn number(10),
  yrmonth varchar(20),
  crdt number(10));

insert into customerData (msisdn, yrmonth, crdt)
values (05512315, '201401', 200);

insert into customerData (msisdn, yrmonth, crdt)
values (05516347, '201402', 100);

insert into customerData (msisdn, yrmonth, crdt)
values (05567892, '201401', 200);

insert into customerData (msisdn, yrmonth, crdt)
values (05571236, '201401', 300);

insert into customerData (msisdn, yrmonth, crdt)
values (05551234, '201401', 200);

CREATE TABLE redemptionData
(txnID number(4),
 txnDate Date,
 msisdn number(10),
 partner varchar2(30),
 redempAmount number(10));

insert into redemptionData (txnID, txnDate, msisdn, partner, redempAmount)
values (1212, To_Date('11-nov-2014'), 05512315, 'Google', 250);

insert into redemptionData (txnID, txnDate, msisdn, partner, redempAmount)
values (1141, To_Date('12-nov-2014'), 05516347, 'Apple', 200);

insert into redemptionData (txnID, txnDate, msisdn, partner, redempAmount)
values (1542, To_Date('13-nov-2014'), 05567892, 'Microsoft', 150);

CREATE TABLE customerRevenue
(msisdn number(10),
Revenue number(10)
);

insert into customerRevenue (msisdn, Revenue)
values (05567892, 1200);

insert into customerRevenue (msisdn, Revenue)
values (05571236, 200);

What is expected from this automation task

All tables being produced previously be produced automatically for each month.

For example, if we produced a table redemptionData_Jan2015, it is expected that a new table will be automatically produced for Feb 2015 too, by querying and applying filter conditions on the redemptionData base table.

In view of this requirement of creating monthly tables for each metric and then JOINING them, I figured that the only way to accomplish this task is to use Dynamic SQL.

My question is:
Would you agree that NDS is the way to go here? If not, what other options would you suggest that I consider?

Any comments + suggestions are welcome. I am trying to broaden my understanding of PL/SQL interactively, after spending some time in lone reading a couple of relevant books. If it helps, I am using Toad for automation.


texts: sql server full text search - fuzzy searching?

texts:
Is there a way to configure fuzzy searches in sql server full text search. Meaning if I search for a term called POWDER, I must get matches (i.e. strings) which contain any variations of it within an allowable distance, like for e.g. the matches can be strings which can contain the following variations of the previously mentioned word:

PWDR (distance = 2)
PWDER (distance = 1)
WDER (distance = 2)

I remember reading somewhere you can do this kind of thing, however this specifically requires you to specify the allowable edit distance. How or where do you do this?


texts: Forget login from connection drop down menu

texts:
Recently I logged myself as a different user (testUser) but now that user seems to be persisted in the drop-down menu of connection dialogue box and I couldn't find a way of getting rid of it. Even worse, it seems to be the default login as whenever I change my connection I have to go to the drop-down menu and choose my usual user otherwise the connection will be made in testUser's context.
How can I get rid of testUser from connection dialogue box?
Thank you.


texts: Getting errors when I try to group by CTEs

texts:
I would like to do a group by on object_name on my last cte in this query but I get the following error :

Column 'CTE1.TimesExecuted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8120, Level 16, State 1, Line 85 Column 'CTE1.CountExec' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

WITH CTE1 AS(
    select  Object_name
            , max(Date_of_Object_Analysis) as MaxDate
            ,count(Object_name) as CountExec
            ,min(Date_of_Object_Analysis) as MinDate
            , SUM([Number_Times_Executed]) AS TimesExecuted
            from
    _Admin_Database_Objects_Analysis_Tbl
    --where Object_Name = '_Admin_Client_Bing_GeoCordinates_Tbl'
    Group by Object_name
    )
    ,
    CTE2 AS (
            SELECT c1.Object_name
            ,cast(c1.CountExec as int)*cast(c1.CountExec as int ) as CountMultiplied
            ,c1.CountExec+c1.CountExec+c1.CountExec+c1.CountExec as Addedup
            FROM _Admin_Database_Objects_Analysis_Tbl c2
            iNNER join CTE1 c1
            on c1.Object_name= c2.Object_Name

    ),
    --select * from CTE2

    CTE3 AS (

        SELECT c3.Object_name

            --,c2.CountMultiplied/c1.CountExec as Final_result
            --,c2.CountMultiplied
            ,c1.TimesExecuted/c1.CountExec as New_new_result
            --,c2.Addedup
            ,c1.MinDate
            FROM _Admin_Database_Objects_Analysis_Tbl c3
            INNER join CTE2 c2
            on c3.Object_name= c2.Object_Name
            INNER JOIN CTE1 c1
            on c3.Object_Name = c1.Object_Name
    Group by c3.Object_name

    )select * from CTE3


texts: Recreating Database from IBD files (no FRM)

texts:
I am running OS X with mySQL 8.0.22 running natively. A few weeks ago had to reinstall mySQL as I could not get it to start. I made a backup of all the folders under '/usr/local/mysql/data'.
When mySQL was installed again it had wiped all the databases.
The data inside these DB is in no way critical, but I remembered today I was working in a WordPress site with a page builder, hence all info is stored in the DB tables.
I would like to find a way to restore this database, but every article I have read mentions "ibd and frm" files.
There is not a single frm file from the backup I made.
Is there any way of recreating this DB at all or is it a lost cause?
Thanks!


texts:
I have a master DB running on internal network and an AWS RDS slave replica from the Master DB. When I upload a fresh copy of the master, the slave will replicate for few days with no issues. After a couple of days, the slave status says its correct but records will be missing on the DB.
I ran both

show master status

AND

show slave status

The Master_log_file and Read_Master_log_Pos are matching.
One thing to mention we ran a huge dump on the master daily from a legacy system.

What could be the reason for the discrepancy  between the two and how to fix it ?

Thanks in Advance.

Update: Today we got yesterday data. It is possible that the amount of data we are replicating everyday cause the slave to get clogged and take time to get update ?

Update 2

After leaving Slave during the weekend. the Seconds_behind_Master went
 down to zero. Also here the result of show slave status

Slave_IO_State:    Waiting for master to send event,
Master_Host:    master_host_url,
Master_User:    replica,
Master_Port:    3306,
Connect_Retry:  60,
Master_Log_File:    mysql-bin.000325,
Read_Master_Log_Pos:    33015975,
Relay_Log_File: relaylog.003679,
Relay_Log_Pos:  54412,
Relay_Master_Log_File:  mysql-bin.000325,
Slave_IO_Running:   Yes,
Slave_SQL_Running:  Yes,
Replicate_Do_DB:    ,
Replicate_Ignore_DB:    ,
Replicate_Do_Table: ,
Replicate_Ignore_Table: mysql.plugin,mysql.rds_monitor,mysql.rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.config_options,innodb_memcache.cache_policies,
Replicate_Wild_Do_Table:    ,
Replicate_Wild_Ignore_Table:    ,
Last_Errno: ,
Last_Error: ,
Skip_Counter:   ,
Exec_Master_Log_Pos:    33015975,
Relay_Log_Space:    142937,
Until_Condition:    None,
Until_Log_File: ,
Until_Log_Pos:  ,
Master_SSL_Allowed: No,
Master_SSL_CA_File: ,
Master_SSL_CA_Path: ,
Master_SSL_Cert:    ,
Master_SSL_Cipher:  ,
Master_SSL_Key: ,
Seconds_Behind_Master:  ,
Master_SSL_Verify_Server_Cert:  No,
Last_IO_Errno:  ,
Last_IO_Error:  ,
Last_SQL_Errno: ,
Last_SQL_Error: ,
Replicate_Ignore_Server_Ids:    ,
Master_Server_Id:   1,
Master_UUID:    ,
Master_Info_File:   mysql.slave_master_info,
SQL_Delay:  ,
SQL_Remaining_Delay:    ,
Slave_SQL_Running_State:    Slave has read all relay log; waiting for the slave I/O thread to update it,
Master_Retry_Count:Master_Bind: 86400,
Last_IO_Error_Timestamp:    ,
Last_SQL_Error_Timestamp:   ,
Master_SSL_Crl: ,
Master_SSL_Crlpath: ,
Retrieved_Gtid_Set: ,
Executed_Gtid_Set:  ,
Auto_Position   ,


texts: SQL Server Data Tools 15.5.0 - Script Component Validation Error

texts:
When trying to use the script component in an SSIS dataflow I am getting a validation error meaning the script and my package will not work:

  Severity  Code    Description Project File    Line    Suppression State
  Error     Validation error. Data Flow Task: Data Flow Task:
  Microsoft.SqlServer.Dts.Pipeline.CannotCreateUserComponentException:
  Cannot create user component class. Make sure there is one class
  marked with SSISScriptComponentEntryPointAttribute in your script.
  at
  Microsoft.SqlServer.Dts.Pipeline.VSTAComponentScriptingEngine.CreateScriptObjectHelper(Assembly
  scriptAssembly)    at
  Microsoft.SqlServer.Dts.Pipeline.VSTAComponentScriptingEngine.GetScriptEntryPoint(String
  versionGuid)    at
  Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserScriptInstance()       Package2.dtsx   0

I get this validation error on a brand new script component which builds perfectly fine. My package is targeting SQL Server 2012 and current versions are Visual Studio 2017 15.5.2 with SSDT 15.5.0. I also had the issue with 15.4.0 preview, when updating to 15.5.0 the script component did work initially and has since stopped working again.

Has anyone else encountered this issue and found a fix?

The only way I have been able to overcome this is to copy a script component from an existing package and then edit it.

I have reported this but no response as yet.


texts: Why is DTA recommending non-clustered indexes that match the clustered Primary Key index?

texts:
I'm only vaguely familiar with SQL Server's Database Engine Tuning Advisor (DTA) but I'm using it to suggest recommendations for indexes on my database.

Most of the tables in the database at present have a single index which is the Primary Key. It is defined during the tables' creation as:

CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([ID] ASC)

When the DTA runs, it is advising that I add additional non-clustered indexes on some tables containing only the [ID] column, e.g.:

CREATE NONCLUSTERED INDEX [_dta_index_Employee_18_1170844725__K1] ON [dbo].
[Employee]
(
    [ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

What I'm wondering is why it's recommending I create an index which exactly matches an index that already exists. Is there some benefit to the query optimizer of there being a non-clustered index as well as the clustered one?

For what it's worth, in most instances the [ID] column is also an IDENTITY column.


texts:
Is there a setting in SQLServer 2008 and newer for default data type of date-time columns, i.e. datetime or datetime2? Actually more like something that would tell SQLServer to use datetime2 instead of datetime when creating a table/view even if the latter is requested explicitly.

UPDATE: Thank you everyone. With this issue I am actually on the pitching side, that is creating tables from Java application that are supposed to have datetime columns, but end up having datetime2. Thought it might be due to DBMS configuration, but, probably, it's due to intricacies of one of our frameworks. Oh well, back to digging code :)

P.S. Special thank you goes to @srutzky for detailed response with multiple options, but accepted @John M's answer as being the most spot on.


texts:
I've seen a couple of questions about the subject backfiring so I'll try to be specific about my concern...

I have an old (MS Access) frontend I'm trying to rebuild and I left the part that troubles me most for last, so here I am.
The task is to 'grab' a file (used to be an XLS), now it would more that likely be by-product of it, say CSV for the sake of the example.

So this is my first steppingstone, I'm concerned about the result of my import, according to Pinal Dave BULK INSERT is a non atomic operation:

  If there is any error in any row it will be not inserted but other
  rows will be inserted.

This is not appealing to me since I tend not to trust the user input, I want more control thus being able to trigger an error and not importing any part of the file would be better. I'm not sure if this effectively wipes the possibility of using BULK INSERT for this process?

I also tried reading line by line and inserting using a LOOP cycle, this is effective but unfortunately its takes to long even after optimizing my code I just went from 15 minutes to 4, and that's only for 850 records.

I'm a bit mad that the process worked so smoothly when running from the Access frontend. That was because of the DoCmd.TransferSpreadsheet VBA method. This allowed for fast XLS import into a temp table and from that one the execution carried on updating tables.

I would like to know what other options have guys tried when importing data from flat file sources into SQL Server. Admittedly I'm a bit confused at this point.

Any help would be appreciated.


texts: pgAdmin4 v4: TypeError: string indices must be integers

texts:
When I open pgAdmin4 v4 on Windows 10, the dashboard displays
An error occured whilst rendering the graph
on all charts. The link (1) told me that the logs on windows are at
Windows log location: C:\Users\YOUR_USERNAME\AppData\Roaming\pgAdmin\pgadmin4.log
The logs contained numerous times the same error

2019-05-10 10:01:32,139: ERROR  flask.app:  string indices must be integers
Traceback (most recent call last):
  File "C:/Program Files (x86)/pgAdmin 4/v4/venv/Lib/site-packages\flask\app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:/Program Files (x86)/pgAdmin 4/v4/venv/Lib/site-packages\flask\app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "C:/Program Files (x86)/pgAdmin 4/v4/venv/Lib/site-packages\flask_login\utils.py", line 261, in decorated_view
    return func(*args, **kwargs)
  File "C:\Program Files (x86)\pgAdmin 4\v4\web\pgadmin\dashboard\__init__.py", line 251, in wrap
    return f(*args, **kwargs)
  File "C:\Program Files (x86)\pgAdmin 4\v4\web\pgadmin\dashboard\__init__.py", line 371, in dashboard_stats
    for chart_row in res['rows']:
TypeError: string indices must be integers

I can also not open any databases(pressing the databases button doesn't unfold the list of databases) via pgAdmin4 v4.

Since I was working with the permissions of my users on certain databases, this has probably to do with it. The commands I recall running are

REVOKE ALL ON DATABASE Database_Name FROM Database_User;
REVOKE ALL ON DATABASE Database_Name FROM PUBLIC;

To verify that I did not completly corrupt my databases and the error is just weird I logged in with the admin user via psql. Running \du and \l did not show anything was off.

What Should I do to fix this?

(1) https://stackoverflow.com/questions/41766323/pgadmin4-dashboard-does-not-display-sessions

-- edit --
DataGrip doesn't seem to have the problem either.


texts:
I have a DB 150 GB , contains 138 tables one of the tables is 120 GB. I need to archive around 80 GB from this table. Is there a way to reclaim this space without using DB shrink?

thanks


texts: How to design a query to pop up input with two text boxes in MS Access

texts:
I ask this question while I could not find any similar question neither on Google nor DBA Stackexchange, this question might be duplicated.
I wrote this query in MS access to check for first name and surname. But every time I run the query, I see two little input boxes which come after each other. I want to change my query so that I enter first name and surname in One/Single input box.How can I design something like this?
Here is what I achieved up until this point:
SELECT tblGuests.firstname, tblGuests.surname
FROM tblGuests
WHERE (((tblGuests.firstname) Like "*" & [Enter Firstname] & "*") OR ((tblGuests.surname) Like "*" & [Enter Surname] & "*"));


texts: MySQL dead-locking with UPDATE JOIN and INSERT SELECT statements

texts:
We've run into a considerable number of dead-locks recently and they seem to be happening at this query:

  INSERT
    INTO link_click (link_id, created_at, weight)
  SELECT :link_id, :created_at, lw.weight
    FROM link_weight lw
   WHERE lw.link_id = :link_id AND lw.start_at < :created_at
ORDER BY lw.start_at DESC
   LIMIT 1

Where link_id and created_at are passed in as PDO parameters
This is part of a larger transaction.

The dead-locks only appear to occur when the following separate query is running on a cron every 30 minutes:

UPDATE owner o
  JOIN (
    SELECT l.owner_id,
           SUM((lc.state = 1) * lc.weight) total_pending,
           SUM((lc.state = 2) * lc.weight) total_confirmed,
           SUM((lc.state = 3) * lc.weight) total_awaiting_processing,
           SUM((lc.state = 4) * lc.weight) total_processed,
           SUM((lc.state = 5) * lc.weight) total_invalid
      FROM link_click lc
      JOIN link l
        ON l.id = lc.link_id
     WHERE (:owner_id IS NULL OR l.owner_id = :owner_id)
       AND lc.state != 0
  GROUP BY l.owner_id
       ) raw
    ON raw.owner_id = o.id
   SET o.total_pending = raw.total_pending,
       o.total_confirmed = raw.total_confirmed,
       o.total_awaiting_processing= raw.total_awaiting_processing,
       o.total_processed= raw.total_processed,
       o.total_invalid = raw.total_invalid

Where owner_id is passed in. This is a one shot query not in a transaction but takes roughly 30s to complete.

I wasn't aware that INSERT SELECT or UPDATE JOIN would lock any rows beyond themselves and cause such a dead-lock.

I guess my question is whether separating out the SELECTs (I don't need a FOR UPDATE lock) from each query would help this situation?

Or is it more likely that the dead-lock is coming from the first query's transaction and the second query is just slowing down the database and that's making the dead-locks more frequent?


texts: Relationship between two tables so their value changes together

texts:
First of all I'm not sure if this website is for purely experts but sorry if it is.. the main page was a little vague

I was wondering if it'd be possible to have a relationship, for example, in my case it would be Customer to saidProduct..

If Customer has a field 'saidProduct' and the table 'saidProduct' has a field 'active'

(I have no knowledge of stored procedures but if this is how it can be done I will figure out how.. that's the only guess I have though)

Can I make it so that if I update Customer.saidProduct = true (was false) so that saidProduct.active = true (was false) as well?

Not sure if it's relevant but I want to simplify my code in VS but I figured asking dba was a better idea


texts: SQL Server: Getting string before the last occurrence '>'

texts:
I have column called assocname.

Sample data:

1. FirstParty>Individual:2
2. FirstParty:3>FPRep:2>Individual
3. Incident>Vehicle:2>RegisteredOwner>Individual3

I want to get the string before the last occurrence '>'. Here is the result:

1. FirstParty
2. FirstParty:3>FPRep:2
3. Incident>Vehicle:2>RegisteredOwner

How can I do that ?


texts: Are there disadvantages to contained databases?

texts:
SQL Server 2012 introduced the concept of "contained" databases, where everything (well, mostly everything) the database needs is contained within the database itself. This offers big advantages when moving databases between servers. I would like to know, then, if this should be my default strategy when designing a new database.

MSDN lists several disadvantages to contained databases, and the big ones are a lack of support for change tracking and replication. Are there others? If I'm not going to use these features, is there any reason not to use contained databases?


texts: ORA-01017 when connecting remotely as sysdba

texts:
Good morning,

While installing a database for Oracle Entreprise Manager Cloud Control (OEMCC) I can't login as sysdba, neither localy using sqlplus SYS@MYSID as sysdba nor remotely using the OEMCC installer :

[oracle@myserver ~]$ sqlplus SYS@MYSID as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 29 10:14:28 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

I did some research :

Here : “ORA-01017: invalid username/password” while connecting to an instance remotely as sysdba
On oracle support : Troubleshooting ORA-1031 or ORA-1017 Errors When Connecting As SYSDBA (Doc ID 730067.1)

It seems to be a different case :

Localy I can connect using the system user (not as sysdba) : sqlplus system@MYSID then enter password, it works. So tnsnames.ora is correct. (Either localy and remotely)
The password file is correctly created, using : ${ORACLE_HOME}/bin/orapwd file=${ORACLE_HOME}/dbs/orapwmysid password="*********" force=y (just to be sure I connected using sqlplus / as sysdba then did alter user sys identified by "*********" account unlock;)
The parameter remote_login_passwordfile is set to EXCLUSIVE :

    SQL> show parameter remote_login_passwordfile;

    NAME                       TYPE    VALUE
    -------------------------  ------  ---------
    remote_login_passwordfile  string  EXCLUSIVE

The orapw file rights are correct :

    [oracle@myserver ~]$ ls -l $ORACLE_HOME/dbs/orapwmysid
    -rw-r-----. 1 oracle dba 6144 May 29 10:14 /opt/oracle/product/12.2.0/dbhome_1/dbs/orapwmysid

Remote login using system works :

[oracle@anotherserver ~]$ sqlplus system@MYSID

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 29 13:18:52 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Tue May 28 2019 15:41:20 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

Do you have an idea, something I could investigate ?


texts:
I have been looking at an issue with performance with a vendor SQL query, usually with this vendor I can see indexes that will improve performance but this query is currently a little out of my league in terms of performance tuning. Hopefully after this request I will learn something new.

The issue with the SQL appears to be a Merge Join (Inner Join) and I am not sure on the best way to try and improve the performance of these.

I would really appreciate someone pointing me in the right direction or if there is any more information I can provide then please let me know.

Here is the execution plan and the SQL query.

There are not many indexes on this one as I have tried applying indexes but they seem to have no effect reducing the Merge Join. Therefore the plan I have given you is using the vendor's default indexes.

Update 1: I have added indexes to query but they have little affect, here are the indexes and new plan

New SQL Plan and the Indexes

I also updated statistics on all tables involved in the query.

Using the option (hash join) reduces the run time down from 30 seconds to 15 seconds. However this is vendor SQL run from behind a screen so I cannot force this.

MAXDOP on the server is set to 0 and Cost Threshold is 50 so not sure why it doesn't want to go parallel.

UPDATE 2: It appears that having the following unique indexes on the table atstsehourdetmap causes the issue. If I drop these then the query runs in less than a second.

CREATE UNIQUE INDEX [aiatstsehourdetmap2] ON [dbo].[atstsehourdetmap] ( [oid] ) ;

CREATE UNIQUE INDEX [aiatstsehourdetmap1] ON [dbo].[atstsehourdetmap] ( [tsehourdet_id] ) ;

Any ideas why?


texts: PostgreSQL: cache, disk, replication. What order?

texts:
I am thinking of using two servers in Master/Slave replication
I want to add following condition: RAM size = dataset size * x, where x > 2.
After that, I plan to force PGSQL to keep all data in RAM cache as long as possible
Question
A write request comes. Which of the following will happen ?

Will it be first cached, then replied, then replicated
Will writing precede replication, and replication precedes caching


texts:
I have a table of products that can be measured by weight and size, and the user can enter which unit he wants to use for the measurement of any product. In the future he might need to see these measurements converted to different units.

I've looked up existing questions about this, and imo they had some complicated solutions using a table for the units and another table for converting from unit to unit, but I don't think I need all that.

My idea so far is to always store the measurement in the smallest unit I'll use (millimeter for distance and milligrams for weight) alongside with a column that tells in which unit this measurement should be displayed, that way I can even make calculations in the db queries, and in the GraphQL API layer or event in the client I'd convert the measurement the desirable unit.

What are the pitfalls to this approach?


texts: How is data of multi valued attribute say phone number of person relation is stored in table?

texts:
I have searched for the answers referencing my books as well as web but i could not find one. So here i am.
I am not sure what this question is trying to ask so help if anyone could.


texts:
I have a table of around 30 million retail products, with various INT columns that describe sales counters and pricing at different retailers.

I'm supposed to display well over 70 different rankings on every individual product's page.

Example: #23 best seller in China, #732 best seller in USA, #13 cheapest price in Norway, etc.

I've thought of various solutions to this problem:

Create ranking fields - I guessed it wouldn't work since just a single drop in price would mean updating millions of rows to reduce their ranking field by 1 for example.
Use COUNT - COUNT the amount of products cheaper than X : display ranking. I'm still considering this, however a single query takes 200 ms on average, so running 70 of them looks like it would take ages.

Really looking forward to advice from experienced database admins on this topic, as my knowledge feels very basic.

Additional information:

Hardware is dual Xeon CPUs, 15k RPM hard drives, 32 GB DDR3, LEMP stack, running latest Percona, all tables are XtraDB engine.

Edit: Posting CREATE statement as requested

CREATE TABLE `products` (
  `id` varchar(64) COLLATE utf8_bin NOT NULL,
  `retailer_id` int(10) unsigned NOT NULL,
  `sales_usa` int(10) unsigned NOT NULL,
  `sales_eu` int(10) unsigned NOT NULL,
  `sales_asia` int(10) unsigned NOT NULL,
  `sales_africa` int(10) unsigned NOT NULL,
  `sales_france` int(10) unsigned NOT NULL,
  `sales_germany` int(10) unsigned NOT NULL,
  `sales_russia` int(10) unsigned NOT NULL,
  `sales_china` int(10) unsigned NOT NULL,
  `sales_india` int(10) unsigned NOT NULL,
  `sales_uk` int(10) unsigned NOT NULL,
  `sales_spain` int(10) unsigned NOT NULL,
  `sales_norway` int(10) unsigned NOT NULL,
  `sales_sweden` int(10) unsigned NOT NULL,
  `sales_japan` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Sample ranking statement:

SELECT `id` FROM `products` WHERE `sales_japan` > 12345

After which I perform a mysql_num_rows() on the result to get that product's ranking in Japan for example.


texts: Dealing with subtypes and supertypes in Database design

texts:
I'm currently doing a database project for a class and I am having some difficulty dealing the subtypes and supertypes.
This is the list of entities and their attributes:

Piece (PIECE_ID, Description)
   In_house (PIECE_ID)
      Bulk (PIECE_ID, Location)
      To order (PIECE_ID, Lead time)
   Purchased (PIECE_ID, Cost)
Order (OID, Order date)
Supplier (SID, Name, Postcode, City )
Supplier piece (SPID, Price, description)

To clarify, Piece is the supertype with two subtypes, In_house and Purchased. In_house then has two subtypes, Bulk and To Order.

My first instinct was to keep a table Piece(PIECE_ID, description), another one for Purchased(PIECE_ID, cost), and then delete the In_house one and keep only the subtypes as Bulk(PIECE_ID, Location) and To_order(PIECE_ID, Lead Time).

First question: is this better as opposed to keeping the In_house table and put the Bulk and To_order info all in that table, generating some NULL values?

Also, how does the system know that there can't be any repeated PIECE_ID in the Purchased,  Bulk and To_order tables? Do I need to create a separate ID for these individual tables or can they just the foreign key for the Piece table?

Finally, the Purchased table has a 1:N obligatory on both relationship with Supplier part table. Therefore, I have to put the primary key of the Purchased table in the Supplier piece table. If the primary key on Purchased is just PIECE_ID, how can it associate that those PIECE_ID are from the Purchased table and not from the other tables that have PIECE_ID as a primary key?

Please let me know in comments if clarification is needed.


texts: When to apply normalization in database design

texts:
Hello database administrators.
Recently, I have been reading about normalization in order to improve my database design skills. However, I am a bit confused about when to apply this technique. Prior to learning about normalization I would usually read the scenario/description in the requirements and draw up an ER diagram (using the Chen's notation) using nouns as entities and verbs as clues to their relationships then go straight to carrying out the SQL statements to create the tables.

At the moment it seems to me that normalization is carried out after designing an ER diagram then applying it to any entity that contains a multi valued attribute.

Am I correct in thinking this? Or am I supposed to carryout normalization prior to creating an ER digram, perhaps after listing the possible attributes of a system?


texts:
I would like to create some tables (including users table and products table) inside MySql Database with those index for each tables:

Private Index (uses int), for relational table purpose only
Public Index (uses 32 chars filled with UUID), will be stored at URL and visible to users

The question is should I use public index only or keep those configuration? Please for the advice, thank you!


texts: Ola Hallengren IntegrityCheck Excludes TempDB

texts:
According to SQL Server Maintenance Solution - SQL Server Integrity Check, the SYSTEM_DATABASES value includes only master, msdb, and model.

Why is TempDB excluded, when it too can have hardware issues causing page corruptions?

Interestingly enough, DBCC CheckDB FAQ: Check for Corruption in SQL Server by Kendra Little talks about the need to check TempDB but points to the Ola Hallengren script, which does not appear to be checking TempDB by default.


texts: Postgres migration local to remote, issue with terminal command

texts:
I'm trying to use the first answer here to transfer a local postgre db to elephantSQL

https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server

The command given is: pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

I have no chance to enter a password in the following:

owner@G700:~$ pg_dump -C -h localhost -U rep1 repdb1 | psql -h baasu.db.elephantsql.com -U bgpacbpe bgpacbpe
Password:
Password for user bgpacbpe: pg_dump: [archiver (db)] connection to database "repdb1" failed: fe_sendauth: no password supplied

How can I resolve this?

If there is a less error prone way to do this I would also like to try that.

owner@G700:~$ psql --version
psql (PostgreSQL) 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)


texts: Execution plan not understanding correctly

texts:
I am running a query:

SELECT * FROM message where fr_user = 1 ORDER BY created DESC LIMIT 10;

Without the LIMIT there would be 170 rows returned where fr_user = 1;

I am now trying to understand how efficient this query (not trying to improve the query, just to see how efficient it CURRENTLY is) is.

I have ran the query and looked at the execution plan:

In the execution plan there is a query cost of 43.00... what does this mean/how is it calculated?
What do the four fields (Read, Eval, Prefix, DataRead) mean?
Why does the ratio of rows examined to the ratio of rows produced = 100% when there are 170 rows examined and 10 rows produced? I have an index (fr_user, created) so why are all the rows (170) being examined? If I have to examine all the rows it would seem pointless to have an index then?


texts: I dropped MySQL's `performance_schema` database, how can I create it?

texts:
While fixing a ibdata/log problem, I accidentally dropped my performance_schema database, I'd like to create a new one.

mysql> SHOW VARIABLES LIKE 'perf%';
+---------------------------------------------------+---------+
| Variable_name                                     | Value   |
+---------------------------------------------------+---------+
| performance_schema                                | ON      |
| performance_schema_events_waits_history_long_size | 10000   |
| performance_schema_events_waits_history_size      | 10      |
| performance_schema_max_cond_classes               | 80      |
| performance_schema_max_cond_instances             | 1000    |
| performance_schema_max_file_classes               | 50      |
| performance_schema_max_file_handles               | 32768   |
| performance_schema_max_file_instances             | 10000   |
| performance_schema_max_mutex_classes              | 200     |
| performance_schema_max_mutex_instances            | 1000000 |
| performance_schema_max_rwlock_classes             | 30      |
| performance_schema_max_rwlock_instances           | 1000000 |
| performance_schema_max_table_handles              | 100000  |
| performance_schema_max_table_instances            | 50000   |
| performance_schema_max_thread_classes             | 50      |
| performance_schema_max_thread_instances           | 1000    |
+---------------------------------------------------+---------+
16 rows in set (0.06 sec)

Those variables appear to be fine to me.

The following question asks the same thing, however the user concludes that they were able to create it by following documentation that I could not find such instructions in.

mysql: deleted performance_schema, is it a problem?

Any thoughts?


texts: SQL script Scope in Microsoft SQL Server Management Studio - runs against active DB or all DB's?

texts:
I'm not very familiar with SQL Server, more familiar with MySQL.

I'm used to seeing everything in code, whereas SQL Server, some things are done with buttons. In MySQL, I'd execute USE testing_database and then I'd know my subsequent queries would execute against that database.

In SQL Server Management Studio, I see that my "Testing Database" is selected in the dropdown select. Does that mean that the queries in the script will execute ONLY against that database?

The script does not include any references to database names. It uses sp_msdependencies to populate a list of Views and then acts on that list. Will sp_msdependencies execute only against the current database?

There are several other databases besides the testing database that I do not want to touch with this test.


texts: How to Log All mysql queries into log file?

texts: I am using Mysql 5.6.12 under Wamp server environment. Now I want to log All queries into ".log" file, the queries which are running by PHP or from PHPMyAdmin, I want to log them...


texts: MySQL relay log rotates too soon and too often

texts:
I have a simple master/slave setup where the slave is just a backup. Sometimes I notice that the relay log on the slave rotates too often and too soon, when it is still very small:

4:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860318633
               Relay_Log_File: vps-relay-bin.000350
                Relay_Log_Pos: 922

5:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860324526
               Relay_Log_File: vps-relay-bin.000351
                Relay_Log_Pos: 6176

7:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860331661
               Relay_Log_File: vps-relay-bin.000352
                Relay_Log_Pos: 7418

9:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860333988
               Relay_Log_File: vps-relay-bin.000353
                Relay_Log_Pos: 501

Notice that the master log is the same and its position didn't change much. But the relay log rotated several times, and didn't get very big. MySQL .err shows:

2014-03-23 14:35:14 4971 [Note] Slave I/O thread: connected to master '<removed>',replication started in log 'mysql-bin.000051' at position 860316289
2014-03-23 15:59:50 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 16:59:52 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 18:59:56 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 20:35:53 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 21:35:55 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 22:35:57 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

Master's log doesn't show anything.

What could be happening here?


texts: Query tuning help needed (Hash Match and Table Scans)

texts:
I need help optimizing the following query (returning ~8k rows):

SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name)
                                FROM (
                                SELECT A.sys_id, AppSvr.name
                                FROM GTS_DataStage.SN.cmdb_ci_appl A
                                LEFT JOIN GTS_DataStage.SN.cmdb_rel_ci X
                                    ON A.sys_id = X.parent
                                LEFT JOIN GTS_DataStage.SN.cmdb_ci AppSvr
                                    ON X.child = AppSvr.sys_id
                                    AND AppSvr.dv_sys_class_name
                                      IN ('UNIX Server', 'Windows Server',
                                      'IBM Mainframe', 'Application Server')
                                WHERE A.dv_sys_class_name = 'Application'
                                 AND A.dv_u_used_for = 'Production'
                                 AND A.dv_operational_status = 'Deployed') T
                                WHERE A.sys_id = T.sys_id
                                FOR XML PATH('')), 3, 4000) AS NVARCHAR(4000))
FROM GTS_DataStage.SN.cmdb_ci_appl A
WHERE A.dv_sys_class_name = 'Application' AND A.dv_u_used_for = 'Production'
    AND A.dv_operational_status = 'Deployed'

These tables are on the DataStage and I am not allowed to use indexes. Total rowcounts are:
cmdb_ci_appl: ~20k; cmdb_rel_ci: ~1200k; cmdb_ci: ~800k

The following image shows a section of the execution plan (I can upload the sqlplan file if needed but there's not much else to see):

Any help is appreciated.


texts: MySQL 5.6 Slave Stops Processing Binlog on Master Restart

texts:
I have a pair of Debian 7 servers with MySQL 5.6.23. The slave server is a clone of the master (but the uuid has been regenerated on the slave).

For some reason, whenever MySQL is restarted on the Master via "service mysql restart," the Slave will continue reading the Master_Log_File and Read_Master_Log_Pos, but won't process the Relay_Master_Log_File.

Slave I/O and Slave SQL are both running, but the Seconds_Behind_Master continue to increment.

The Slave_SQL_Running_State says "Waiting for Slave Worker to release partition."

Obviously, I've tried to stop and start the slave, restart the MySQL service on the slave and master, and restart the slave and master servers.

To fix the issue, I have to blank and recreate the slave. I do this via a script I've made to dump the master file and position, use mysqldump to pull the database onto the slave, and then start slaving from the recorded master file and position. The mysqldump flags are --skip-lock-tables --flush-logs --hex-blob --master-data=2 --single-transaction --comments --routines

Am I missing something? Any help is greatly appreciated.


texts: Why isn't oracle using an index for distinct query ?


texts: Running Aggregation logic on multiple shards of MongoDB

texts:
Suppose we have 5 shards in MongoDB having collection of data, and I have to write an Aggregation logic which should work on each of 5 shards in my cluster and collect data from these shards. Should it be taken care at the application developement side? like connecting to each Shard separatley by its shard key and get data Or once I write the aggregation logic and deploy my jar on this cluster it will be handled my MongoDB itself to read from these shards and work aggregation logic on these shards data?

Like in Cassandra MapReduce it will be handled by a Job tracker to send job to appropriate nodes.


texts:
I see similar questions, but most have to do with datetime or fairly different requirements.

I have a CSV report that has a time column stored as HH:MM[:SS] (if over 1 day, it adds :00 for seconds... annoyingly, and I don't have control over how the report itself runs.) I think, in the derived column task, we can drop seconds.

Examples:

23:54 = 23H 54M
27:35:00 = 27H 35M 00S

My questions are:

If the CSV loads the column as text, what expression do I need for the derived column?
What should I set the data type as in the SQL database?

Pertinent info: SQL Server 2014 and SSDT for VS 2013

Thank you,
Wes


texts: Query Takes more time in SQL Server 2008

texts:
I have a table in SQL Server 2008 with 553,386 rows and every day it is increasing by about 400 rows. Now when I full scan of the table (Select * from) it is taking 23 sec. Is there any solution to speed up the query?

My PC configuration is, CPU: Intel Core 2 Duo 2.0 GHz, RAM: 4GB, OS: Windows 7 32 bit

Thanks in advance!

Table structure is given below:

USE [dbCPFL_Test]
GO

/****** Object:  Table [dbo].[PriceData]    Script Date: 10/04/2013 19:56:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PriceData](
    [TradeDate] [date] NOT NULL,
    [Ticker] [varchar](50) NOT NULL,
    [ClsPRC] [float] NOT NULL,
    [No_of_Share] [float] NOT NULL,
    [TradeStatus] [char](1) NOT NULL,
    [Adj_MKTCap] [float] NOT NULL,
    [deList] [char](1) NOT NULL,
 CONSTRAINT [PK_PriceData] PRIMARY KEY CLUSTERED
(
    [TradeDate] ASC,
    [Ticker] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


texts: MySQL replication much slower on MySQL 5.7 compared to MySQL 5.5

texts:
I have two databases setup for testing (so no live data or connections).

The first is a MySQL 5.5.57 Database acting as the master. The second is a MySQL 5.7.23 Database acting as a slave.
It is running Statement Based Replication on MySQL 5.5 and Row Based Replication on MySQL 5.7.

For some reason larger queries seem to take significantly longer on MySQL 5.7. For example I have the following table:

CREATE TABLE `test1` (
  `col0` int(11) DEFAULT NULL,
  `col1` int(11) DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3 datetime DEFAULT NULL,
  `col4` int(11) DEFAULT NULL,
  `col5` int(11) DEFAULT NULL,
  `col6` int(11) DEFAULT NULL,
  `col7` int(4) DEFAULT NULL,
  `col8` int(11) DEFAULT NULL,
  `col9` datetime DEFAULT NULL,
  `col10` datetime DEFAULT NULL,
  `UniqRef` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col11` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `col12` tinyint(1) unsigned zerofill DEFAULT NULL,
  `col13` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`UniqRef`),
  KEY `col0` (`col0`),
  KEY `col3` (`col3`),
  KEY `col4` (`col4`),
  KEY `col8` (`col8`),
  KEY `col5` (`col5`),
  KEY `col10` (`Deleted`),
  KEY `col2` (`col2`),
  KEY `col12` (`col12`) USING BTREE,
  KEY `col13` (`col13`)
) ENGINE=InnoDB AUTO_INCREMENT=400046649 DEFAULT CHARSET=latin1;

The table is 21GB in size, and holds 83261829 rows (using count(*)).

If I run the query:
UPDATE test.test1 SET col9 = NOW() WHERE UniqRef IN ('397958600','397940686','397940704','397940678','397. . . . .

The query contains 6945 uniqref entries. It runs almost instantly on MySQL 5.5, but then it replicates to MySQL 5.7 and takes 90 seconds to run.

I have changed various settings, but they have so far made no difference:

SET @@global.slave_compressed_protocol = 0;

SET @@global.sync_binlog = 0;

set @@global.range_optimizer_max_mem_size = 0;

I have also tried changing read_io_threads and write_io_threads to various values, as well as changing the number of buffer pool instances, and disabling the binary logs.

Are there any other variables that may make a difference to try and speed this up that I have missed?

UPDATE 2018-10-17

I managed to speed the queries up when run locally (directly on MySQL 5.7) by setting SET @@global.range_optimizer_max_mem_size = 16777216;. After the this the queries went from 90+ seconds to 0 seconds.

However, when the same query was run on MySQL 5.5 and replicated across to MySQL 5.7, it still takes 90+ seconds to run.


texts: What is the difference between Oracle's VARCHAR and VARCHAR2 datatypes?

texts:
When migrating tables coming from other DBMSs to Oracle, one of the standard tasks is to replace all VARCHAR(n) fields with VARCHAR2(n) fields (provided n <= 4000).

Why does Oracle call this datatype VARCHAR2 and not just VARCHAR like other DBMSs?


texts: sql server stored procedure - collection of data for fields as input

texts:
I have a stored procedure to add a collection of values to a field in table.
How I implement it now:
from my .net app, I create a string of comma separated values from an array( eg: resource1, recource2, resoource3...). This string is then send to stored procedure, where it is split and each value is inserted into a table.
my code below
ALTER PROCEDURE  [dbo].[resourcetrackerupdate]
@resources varchar(max)

as begin

SET NOCOUNT ON;

DECLARE @MyCursor CURSOR;
DECLARE @ReourceName varchar(max);
BEGIN
    SET @MyCursor = CURSOR FOR
    select [name]  from dbo.splitstring(@resources)

    OPEN @MyCursor
    FETCH NEXT FROM @MyCursor
    INTO @ResourceName

    WHILE @@FETCH_STATUS = 0
    BEGIN

            BEGIN TRY
                INSERT INTO resourcetracker ([ResourceName]) VALUES (@ResourceName)
             END TRY
            BEGIN CATCH

             END CATCH
      FETCH NEXT FROM @MyCursor
      INTO @ResourceName
    END;

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

Now, my requirement is that I need to send multiple field data (as opposed to just one - resourcename - now). So , I will need to send resourcename, resourcesize, assignedto etc to stored procedure to insert into the table as new records.
FYI, these arrays are size of few thousands records.
The long comma separated string approach might not work anymore. How can I change my stored procedure to process all that data?


texts:
I have problem, I would like to use mysqlbinlog for the backup, I do not know what causes it but the backuped binlog seems to be 4KB behind the binlog. I made a small shell.
while [ $i -lt 30 ]
do
mysql -D almafa -e 'insert  into b values("0HHHHHH");commit;'
   echo 'NUMBER:'$i >>/tmp/fut.lof34
   ls -la /tmp/almafamariadb-bin.000034  >>/tmp/fut.lof34
   ls -la /var/lib/mysql/mariadb-bin.000034   >>/tmp/fut.lof34
   i=`expr $i + 1`
done
systemctl restart mariadb

the new binlog was mariadb-bin.000034
sync_binlog=1
result is:
...
NUMBER:17
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3476 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:18
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3650 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:19
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3824 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:20
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3998 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:21
-rw-r--r-- 1 root root 4096 Feb  3 10:31 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 4172 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:22


texts:
While studying different query plans to improve performance I noticed that FETCH is doing an implicit convert to bigint.

Example table and query:

CREATE TABLE checkPagintion
(
    Id INT NOT NULL PRIMARY KEY CLUSTERED,
    Name NVARCHAR(100)
 )

 DECLARE @paramPageNumber AS INT,
         @paramPageSize AS INT;

 SELECT *
 FROM checkPagintion
 ORDER BY Id
 OFFSET @paramPageNumber ROWS
 FETCH NEXT @paramPageSize ROWS ONLY

Execution plan for this query:

My question: should I use bigint for all pagination queries? If I use int will it a be a problem since implict_conversion is known for slow performance?


texts: Moving to Production database for first time

texts:
We currently have a development database where we store lot of data (all of them are external data coming in .csv or excel files and stored via SSIS packages) and business users are directly connected to the development sql server database via excel. Since the reports derived from the development server are used in the business, we are planning to move the database to a production server.

Can Business users connect to the production database via MS Excel? Does best practices allow that?
Can I connect to the production database via SSMS and do data analysis on that? Currently I use a Windows 8 system.
Can I connect to the production database from our SQL Server Data Tools or Business Intelligence Development studio? If I can't connect to the production server, how to develop ssis packages for Production server?

Thanks
Vagan


texts: Best way to store correlation data for searches

texts:
This is a long post where I outline a problem I faced recently and the steps I went to solving it. I ask for feedback on the solution I came up with and for recommendations for improvements.
Using an FP-growth algorithm in pyspark I created a dataset of correlated keywords for cities around the US, this would look something like
java&javascript, fort collins, python, .85
java, los angeles, c++, .65
python&php&mysql, New York, java, .4
This means in job postings in fort collins when both java and javascript appeared, python was likely to also appear with a correlation value of .85, and for jobs in los angeles that had java, c++ was likely to appear.
The problem I had with storing this data was how to search it in a table, The following table schema
id, keyword_id, city_id, correlated_keyword_id, value
Does not work as the number of keyword ids can range from 1 to N (where N is the total number of keywords) Having a table like
id, keyword1_id, keyword2_id ... keywordn_id
(where anything after keyword1 would be null by default) seems like an improper solution and not very scalable.
Another idea I had was to split it into multiple tables
id, cityid, correlated_keyword, value
id, correlation_id, keyword_id
(where correlation_id is a foreign key of the id column in the above table)
While this would work if I wanted to do a search on what was correlated with java&javascript&c++
I feel like I would need to do 3 seperate queries on the second table then do a join to find which coorrelation_ids appeared in all 3 searches.
While this seems slow it also fails to take into account if the user wanted  to find the correlated keywords to the above 3 only in a certain city, as what is correlated to java&javascript&c++ varies in each city.
The last solution is to store the keywords I would search on in varchar, which would allow me to search on just java or also java&javascript&...
Again this seems like a poor solution
The idea I settled on solved all of the problems of the previous two examples but only worked because the number of keywords we had to search on was less than 64.
I gave each keyword an id of a power of 2
1, 2, 4, 8, 16 ...
then the table I used to store the correlation data was
id, keyword_sum, city_id, coorelated_keyword_id, value
Then if a user wanted to search on
java&javscript&c++ I could sum those 3 keywords as that would be a unique value that no other combination of keywords would ever equal.
Also adding a multi column index on keyword_sum and city_id allowed me to do fast searches if I wanted to filter on city as well.
What are your thoughts on this solution, is this the only optimized way to allow users to search on a variable number of keywords and city? Or is there another solution I did not consider, perhaps a relational database is a poor DB to use, in that case what would be better? NOSQL, Mongo?


texts: FK relationships between schemas in azure SQL don't work

texts:
I'm having an issue with migrating my SQL 2014 database to an azure database.  I'm able to get everything migrated except for any FK relationships between tables that aren't the in same schema.  for example, 2 tables:

    CREATE TABLE [dbo].[User] (
        [UserGUID]          UNIQUEIDENTIFIER CONSTRAINT [DF_CoBRAUser_CoBRAUserGUID] DEFAULT (newsequentialid()) NOT NULL,
        [UserName]               NVARCHAR (400)   NOT NULL,
        [OrganizationName]       NVARCHAR (100)   NOT NULL,
        [Title]                  NVARCHAR (100)   NULL,
        [Department]             NVARCHAR (100)   NULL,
        [FirstName]              NVARCHAR (100)   NULL,
        [LastName]               NVARCHAR (100)   NULL,
        [UserImage]              VARBINARY (MAX)  NULL,
        [DateCreated]            DATETIME         NOT NULL,
        [DateLastModified]       DATETIME         NOT NULL,
        [MotherTongue]           NVARCHAR (50)    NULL,
        [LockoutEndDateUtc] DATETIME NULL,
[ClusterID]              INT              IDENTITY (1, 1) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY NONCLUSTERED ([UserGUID] ASC)
    );
GO
CREATE UNIQUE CLUSTERED INDEX [IX_User_ClusterID] ON dbo.User ([ClusterID])
GO
create schema [CConfigD]
    GO
    CREATE TABLE [CConfigD].[Alarm] (
        [AlarmGUID]   UNIQUEIDENTIFIER CONSTRAINT [DF_Alarm_AlarmGUID] DEFAULT (newsequentialid()) NOT NULL,
        [CreatedBy]         UNIQUEIDENTIFIER NOT NULL,
        [AlarmText]         NVARCHAR (1000)  NOT NULL,
        [DateCreated]       DATETIME         NOT NULL,
        [DateLastModified]  DATETIME         NOT NULL,
        CONSTRAINT [FK_Alarm_User] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[User] ([UserGUID])
    );

This works fine on my sql 2014 box, if i try to run a dacpac onto the azure database i get the following error:

  Msg 1776, Level 16, State 0, Line 8 There are no primary or candidate
  keys in the referenced table 'dbo.User' that match the referencing
  column list in the foreign key 'FK_Alarm_User'. Msg 1750, Level 16,
  State 1, Line 8 Could not create constraint or index. See previous
  errors.

is this just not allowed and i didn't know it?  Is there a setting i need to enable in order to allow this sort of foreign key?


texts: How to get a list of global temporary tables in the current database?

texts: I am trying to get a list of the global temporary tables that are currently active. There seems to be an issue with them bumping in to each other (same name). As the 'acting' dba, trying to sort out a mess here.


texts: Why does MySQL only sometimes use my index for a range query?

texts:
I have a table of IP address ranges and their country, here is the structure:

CREATE TABLE `geoIP` (
  `startBlock` int(11) unsigned NOT NULL DEFAULT '0',
  `endBlock` int(11) unsigned NOT NULL DEFAULT '0',
  `code` char(2) NOT NULL DEFAULT '',
  `country` varchar(60) NOT NULL DEFAULT '',
  KEY `startEnd` (`startBlock`,`endBlock`),
  KEY `country` (`country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The table is typically used to lookup the country of an IP address. I'm trying to understand why these queries do not always use the 'startEnd' index, as my slow query log is filled with entries like this:

# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 120967
SELECT code FROM geoIP WHERE INET_ATON('82.39.119.217') > startBlock AND INET_ATON('82.39.119.217') < endBlock;

120,967 is the number of rows in the table, so it's doing a full table scan. Here's the explain for that query:

id  select_type  table  type  possible_keys  key    key_len  ref   rows    Extra
1   SIMPLE       geoIP  ALL   startEnd       NULL   NULL     NULL  120967  Using where

Sometimes the index is used though, here's the explain for another - same query, the only difference is the IP address:

id  select_type  table  type   possible_keys  key       key_len  ref    rows    Extra
1   SIMPLE       geoIP  range  startEnd       startEnd  4        NULL   9579    Using where

both queries return 1 row.

Why does MySQL only sometimes use the index? Is there anything I can do to optimise further?


texts: mysql - query records to fetch by time stamp

texts:
I've a MySql 5.6 DB with a simple table (MyISAM) .

| UniqueKey |  TimeStamp |
-------------------------
| 1         | 1024125412 |
| 2         | 1024125413 |
| 3         | 1024125414 |

My table will have maximum 3 million records with indexed timestamps and one record for every secoond.

I need to "Fetch one record (first) per minute".

I've tried select * from table group by MINUTE(timestamp)

But, its taking 28 minutes to fetch locally. I'm looking for solution to fetch with in 10 sec.

Any suggestions for pre computations or using different DB to achieve same would be appreciated.

Edit:

@Vérace: I've chosen MyISAM, because my business requirement enforced me to have 1600 columns in single table and i dont need any relations . It seems only MyISAM allow me to have more than 1000 columns.

@Vérace: timestamp column already indexed.

@Mihai: Yes, sometimes. I may request for entire table. and sometimes in date range.

Edit:

some metrics per answers:

SELECT id,TIMESTAMP FROM t WHERE MOD(TimeStamp,60)=0 order by id desc LIMIT 1000; 59Sec
SELECT id,TIMESTAMP FROM t WHERE MOD(TimeStamp,60)=0 order by null LIMIT 1000; 5SSec


texts:
If a data-modifying CTE inserts into two tables t1 and t2 that both have triggers, is there any guarantee on the order in which statement-level triggers are run?

-- t1 and t2 both have insert triggers
WITH do_insert_t1 AS (
  insert into t1 ...
)
insert into t2 ...

I know that the cte sub-statement execution order is not guaranteed

  The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable.

and that triggers on the same table fire alphabetically

  If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name.

Is the order of "triggers on t1" vs "triggers on t2" also unpredictable?

(I am specifically interested in Postgres 12.)

Edit: clarified statement-level triggers specifically.


texts: Log file is growing up and is not releasing space

texts:
The database is under Full Recovery Method, the DBCC SQLPERF (LOGSPACE) shows 3 % used. Even after I take Log back ups frequently it is not releasing space. There is no active transaction and log_reuse_wait_desc reports as nothing.

How do i prevent the log from growing since the log back up is not truncating the log files