texts: Monstrous query hanging on "copying to tmp table" in MySQL

texts:
I have a huge query being run by one of my developers which seems to hang no matter which performance tunables I adjust.  Though I'm somewhat of novice at writing SQL queries, at first glance the query seems like it could use a lot of work.  I've already set tmpdir to a RAM disk, but this doesn't seem to help.  It would also appear that even though the mysql process state is in "copying to tmp table", nothing is actually being written there.  This is on an Intel Xeon L5520 (8-core machine w/HT) with 72GB RAM.

Here is the query:

http://pastebin.com/6mGUtncc

Here is an explain statement run on that query:

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    397520  Using temporary; Using filesort
2       DERIVED <derived3>      ALL     NULL    NULL    NULL    NULL    397520  Using temporary; Using filesort
2       DERIVED o       eq_ref  PRIMARY,ndx_local_order_date,ndx_merchant_id    PRIMARY 4       order_stats.master_ticket_id    1       Using where
2       DERIVED m1      eq_ref  PRIMARY,fk_merchant3    PRIMARY 4       ticketing_brevard_prod.o.merchant_id    1       Using where
2       DERIVED oi      ref     order_items1    order_items1    4       order_stats.master_ticket_id    4
2       DERIVED th      ref     PRIMARY,ticket_header2  ticket_header2  4       order_stats.master_ticket_id    2       Using where
3       DERIVED m       index_merge     PRIMARY,fk_merchant3    PRIMARY,fk_merchant3    4,5     NULL    2       Using union(PRIMARY,fk_merchant3); Using where; Using index; Using temporary; Using filesort
3       DERIVED o2      ref     PRIMARY,ndx_order_date,ndx_merchant_id  ndx_merchant_id 4       ticketing_brevard_prod.m.merchant_id    40603   Using where
3       DERIVED th2     ref     ticket_header2,ticket_header3,fk_ticket_header7 ticket_header2  4       ticketing_brevard_prod.o2.master_ticket_id      2
3       DERIVED customer_type   eq_ref  PRIMARY PRIMARY 4       ticketing_brevard_prod.th2.customer_type        1
3       DERIVED package_header  eq_ref  PRIMARY PRIMARY 4       ticketing_brevard_prod.th2.package_id   1
4       DEPENDENT SUBQUERY      mpc     eq_ref  PRIMARY,merchant_package_characs2,merchant_package_characs3     PRIMARY 60      ticketing_brevard_prod.th2.merchant_id,ticketing_brevard_prod.th2.package_id    1       Using where

I think it's clear to see from the EXPLAIN output that copying 397520 rows of data to tmp is the likely culprit, however I'm not sure what to suggest to the dev who wrote query on how to improve it.  It'd be great if someone could point me in the right direction.  Thanks in advance!

SHOW VARIABLES LIKE '%buffer%';

Variable_name   Value
bulk_insert_buffer_size 8388608
innodb_buffer_pool_shm_checksum ON
innodb_buffer_pool_shm_key      0
innodb_buffer_pool_size 60129542144
innodb_change_buffering inserts
innodb_log_buffer_size  4194304
join_buffer_size        16777216
key_buffer_size 67108864
myisam_sort_buffer_size 134217728
net_buffer_length       16384
preload_buffer_size     32768
read_buffer_size        16777216
read_rnd_buffer_size    33554432
sort_buffer_size        1073741824
sql_buffer_result       OFF


texts: File growth and location change for system health extended events

texts:
I am continuing the questions over system health extended event from my previous question How are the system health extended events files rolling over?

I see system health file location by default goes to log folder, can this be changed as its very hard to find for 200+ servers and thus making it a standard location across x servers
Need to understand below behavior per my comments on previous question when number of files were increased from default of 5 to 10 and size from 5 MB to 25 MB

a) Why don't we see additional 5 files yet listed once we change number of files to 10?

b) How are the files filling up the space as I see there is no sequential filing say oldest files [ 3 days old]  are still 3 MB while some of latest ones [ consider today] have already filled with 5 MB and going back to old ones to record data. How is this writing mechanism done? I need to understand this to figure out how many files and how big can they be created before making a change


texts: How to conduct integrity test on SQL Server database backup file?

texts: I was assigned to check the integrity of database backups from sql server every two weeks. I'm new in this field so I don't know how to correctly do this task. If someone(hacker) modified the data inside the database backup, is it a part of the integrity of the file? Is there a way to check it?


texts: Is VSS Backup a better solution than the native backup?

texts:
Since i started to work as a DBA, i'm making normal backups ( with scripts, ad hoc and etc ).

What would be the better solution for a backup? lets say I need to migrate a 2tb database to development environment and not affect the production environment.

I can't find much information about VSS backups. I'm trying to think now as a microsoft DBA and not like a normal DBA anymore.

I Just found This good site about VSS backups

What is the difference between a shadow copy and a backup?

  In the case of a hard disk drive backup, the shadow copy created is
  also the backup. Data can be copied off the shadow copy for a restore
  or the shadow copy can be used for a fast recovery scenario—for
  example, LUN resynchronization or LUN swapping. When data is copied
  from the shadow copy to tape or other removable media, the content
  that is stored on the media constitutes the backup. The shadow copy
  itself can be deleted after the data is copied from it.


texts: SQL Server - Logical Reads lowered, Execution time remained the same

texts:
A view executed Select columns from view where col= "date range" (view contains calculation in it Col1+Col2*col3 AS Col4). It took 7 seconds for 500K rows to return, logical reads some 144k.

Added covering index and now after execution it says: logical reads = 8365 (some 17x times less) but the time of execution remained the same, some 7 seconds for 500k to return.

I would expect that reduction of logical reads would lower execution time too, or I am doing something wrong? Any advice?

Thanks.

@Craig, I tired what you told me and execution plan shows me that it was ignoring hints in both cases. It was index seek every time (index I added to help time execution). When I tried it without doing freeing cache it executed in almost the same time for all three of them and with no physical reads for the first execution. No need since all data was in the memory already.
Here is the output of what you told me to try:

Warning: Index hints supplied for view 'Name of the view' will be ignored.
Warning: Index hints supplied for view 'Name of the view' will be ignored.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

do not use any indexes

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
(100000 row(s) affected)
Table 'Name'. Scan count 2, logical reads 2137, physical reads 7,
read-ahead reads 6510, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 939 ms.

let mssql choose what index to use

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
(100000 row(s) affected)
Table 'Name'. Scan count 2, logical reads 2137, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 799 ms.

force index name_of_index

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
(100000 row(s) affected)
Table 'Name'. Scan count 2, logical reads 2137, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 769 ms.


texts: mongoDB : Restoring space after db.repairDatabase

texts:
This is my first post in this forum and also I am very new to mongoDB. Before posting I searched for existing posts relating to repairDatabase but everything looks in advanced stage. My query is very basic.

If you look below series of simple operations i.e. insert documents and deleting documents and finding the occupied size of collection.

I am logged into mongo and this is the start of my screen:

2016-12-16T12:17:17.521+0000 I CONTROL  [main] Hotfix KB2731284 or later update is not installed, will zero-out data files
MongoDB shell version: 3.2.9
connecting to: 127.0.0.1:27017/test

> show dbs
local  0.000GB

> db
test

> show collections

>// No collection at this moment (I entered this text here and not from shell screen)

> use mc
switched to db mc

> db.mc.storageSize()
> // Nothing returned

> db.mc.totalSize()
> // Nothing returned

> db.mc.insert({x:1})
WriteResult({ "nInserted" : 1 })

> db.mc.storageSize()
4096

> db.mc.totalSize()
8192

> db.mc.insert({x:2})
WriteResult({ "nInserted" : 1 })

> db.mc.storageSize()
4096 // Same as above

> db.mc.totalSize()
8192 // Same as above

> for(i=1;i<100;i++){db.mc.insert({y:i})} ;
WriteResult({ "nInserted" : 1 })

> db.mc.count()
101

> db.mc.storageSize()
16384 // Size increased

> db.mc.totalSize()
32768 // Size increased

> db.mc.remove({})
WriteResult({ "nRemoved" : 101 })

> db.mc.count()
0

> db.mc.storageSize()
20480 // Size further increased

> db.mc.totalSize()
40960 // Size further increased

> db.repairDatabase()
{ "ok" : 1 }

> db.mc.storageSize()
20480 // Same after repairDatabase

> db.mc.totalSize()
24576 // Here size decreased after repairDatabase

> show dbs
local  0.000GB
mc     0.000GB

If I observe the numbers produced after above operations, I was under assumption that size should go back to empty, though show dbs shows mc as 0.000GB.

I am bit confused about the size before and after the repairDatabase. I was thinking space should get release. I haven't tried compact option

It would be great if anyone monitoring this forum could give their thoughts.


texts: Group records with same value in one row informix

texts:
I have 3 records in a table below called systables_growth:

tabname         aactn
total_size_kb   32
date_added      2019-08-26 13:00:00

tabname         aactn
total_size_kb   37
date_added      2019-08-27 13:00:00

tabname         aactn
total_size_kb   39
date_added      2019-08-28 13:00:00

SQL server layout:

tabname     total_size_kb   date_added
aactn       32              26/08/2019 13:00
aactn       37              27/08/2019 13:00
aactn       39              28/08/2019 13:00

I want to achieve the desired output below:

tabname                 aactn
2019-08-26 13:00:00     32
2019-08-27 13:00:00     37
2019-08-28 13:00:00     39

SQL server output required:

tabname 26/08/2019 13:00    27/08/2019 13:00    28/08/2019 13:00
aactn   32                  37                  39

Based on the above output wanted. I want to combine records with the same table name and use the date_added value as the column name. I want to try and achieve this using a case statement, but am struggling with the logic. Please note that this is for informix not sql server. I only provided the layout for sql server to make it perhaps easier to understand what I am trying to achieve.


texts: Optimizing DB for performance by archiving/partitioning

texts:
I have a table in my Database which would be loaded with huge amounts of data each day. These are basically status of the orders customers has placed. The UI in my application  which calls the DB for data would allow the customer to only check for the data for the day and one day previous and after. But due to the huge amounts of data, it is taking time in fetching those.

Basically, the UI is interested in data only for three days, but then I need to store the historical data. I was looking into partitions, but couldn't arrive at any solution yet.

What would you suggest in this case to get the maximum performance while fetching the data?


texts:
I need to import data from a csv file  which from database a schema a into another database b schema b.
I used the pgAdmin running the export and when I import the data using the same tool pgAdmin and/or directly from psql prompt, I get an error:

permission denied for schema a

both tables from schema a and schema b are the same information definition and attributes.  Not even csv from Oracle's table.
Just a csv, do I still needs to specify schema to schema and how?
"C:\\Program Files (x86)\\pgAdmin 4\\v4\\runtime\\psql.exe" --command " "\\copy b.sc_g28 (receipt_number, rep_ins_attny_id, rep_state_num, rep_volag, rep_rep_code, rep_last_name, rep_first_name, rep_middle_name, rep_firm_name, rep_street, rep_street_2, rep_city, rep_state, rep_zip, rep_province, rep_postal_code, rep_country, mig_filename, mig_insert_dt, mig_modified_dt) FROM 'C:/cidr/CIDR_D~1/EXP_SC~1.CSV' CSV HEADER QUOTE '\"' ESCAPE
ERROR:  permission denied for schema a

There is a trigger on both a.sc_28 and b.sc_g28.
I have 2 instances (databases) pscidr and cidr.
pscidr has 3 schemas and owned by postgres.
cidr has 3 schemas and each schema owned by each user/role.

I have no problem to export Oracle's table to csv and import to postgres pscidr schema's table without disable constraint but I can't import either one from Oracle's table and/or a.sc_g28 to b.sc_g28 of cidr db.  Keep receive the "permission denied a.sc_g28".  An a.sc_g28 is from pscidr database.
Can't disable the constraint b.sc_g28's pkey or turned deferrable/deferred on from pgAdmin.
alter table b.sc_g28 disable constraint sc_g28_pkey;

ERROR: syntax error at or near "constraint" LINE 2: alter table b.sc_g28 disable constraint sc_g28_pkey; ^ SQL state: 42601 Character: 38

or
ALTER TABLE b.sc_g28 ALTER CONSTRAINT sc_g28_pkey DEFERRABLE INITIALLY DEFERRED;

ERROR: constraint "sc_g28_pkey" of relation "sc_g28" does not exist SQL state: 42704

I try to alter trigger fine without error
ALTER TABLE cidrdata.sc_g28 DISABLE TRIGGER ALL;

but still can't import csv file into b.sc_g28.
ERROR:  permission denied for schema a.


texts: How to design tables such that a person can work for many companies?

texts: Database design such that a person can work for many companies?


texts: query regarding combining an update and an insert query into a single query in mysql

texts:
i want to track history of changes for a user, so that whenever he changes his profile, i need to take the old data and store in history and update with new data.

I can use a select to get the old data, an insert to history and finally an update to change data.

can i have all these in a single query in mysql without using stored procedures, triggers, etc.. like using locks etc.. if so give me a small sample.


texts: How to set host_name() value in a Sql Server Stored Procedure?

texts:
We have existing triggers which read the workstation id property of the connection string using host_name().   There are existing PowerBuilder client applications connecting to the database directly and this setting was used to pass the identity of the current user to the application (not my design).

We have newer web applications updating the database, and the triggers require the user identity information to be present in host_name.  However, we don't want to set the user id in the connection string due to the affect on connection pooling (connections are not pooled across all users).

We pass the identity of the user via stored procedure parameters and would like to somehow set the host_name in the stored procedure such that the trigger requirements are satisfied.

How might this be accomplished, if at all?


texts:
I'm currently working on MYSQL DB optimizations. I'm looking around some slow queries and I try to optimize some of them.

In my app, for statistics purposes, I'm querying and -very- big table (millions of records) which contains visitors log entries (clicks, opens, impressions,...).
That table contains :

ID (INT, Primary Key)
OpenDate (DATETIME)
ListID (INT)
UserID (INT)
...and some other fields

I need to retrieve number of rows per Day. So I need to convert DATETIME into DATE to be able to GROUP BY day value.

DATETIME is on format yyyy-mm-dd hh:ii:ss
In the facts, what I need is the only 10 first chars : yyyy-mm-dd

Currently, the query is :

SELECT COUNT(*) AS TotalOpens, DATE_FORMAT(OpenDate,  '%Y-%m-%d') AS DayOfWeek
FROM `stats_open` WHERE ListID='38' AND UserID='4'
GROUP BY crc32(DayOfWeek) ORDER BY TotalOpens DESC

But I wonder if I should change the DATE_FORMAT function to SUBSTR() function, so MySQL would work a little bit less ?
Isnt easier for MySQL to cut a string instead of reformat a date ?

Thanks for your help.

Joffrey


texts: PostgreSQL Generate create Foreign keys from information_schema or pg_constraint

texts:
Recently on a testing infrastructure, to restore multiple tables (via copy command) I dropped all the foreign keys.
I have restored this database schema dump into a different database before dropping the FK.
Is there any way that I can generate the create FK from information_schema or pg_constraint?
I tried to get it from a dump file, but the create table statement itself having the FK (not an Alter table statement).
Any suggestions?


texts: SQL Server: Should I overwrite existing file for additional backups?

texts:
In the SQL Server 2012 wizard to backup a database, I previously created a 'full' backup.

Now I want to create a differential backup. However, the destination specified the same file name and I'm not sure what will happen when creating a new backup to that file in terms of restoration.

ie.

Will this append to the same file?

Will the full backup be replaced by the differential file? (Obviously this is not helpful)

Will something else happen?


texts:
I'm learning locking basics and I have some doubts about how it works.
I'm using following query to examine transaction locks :

SELECT request_session_id AS spid ,
DB_NAME(resource_database_id) AS dbname ,
CASE WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_NAME(p.object_id)
END AS entity_name ,
index_id ,
resource_type AS resource ,
resource_description AS description ,
request_mode AS mode ,
request_status AS status
FROM sys.dm_tran_locks t
LEFT JOIN sys.partitions p
ON p.partition_id = t.resource_associated_entity_id
WHERE resource_database_id = DB_ID()
AND resource_type <> 'DATABASE' ;

Here i'm opening transaction and running some basic select query :

USE AdventureWorks ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN TRAN
SELECT *
FROM Production.Product
WHERE Name LIKE 'Racing Socks%' ;

I can see following output :

As you can see there exists key locks on clustered and non-clustered indexes

Now i'm trying to create another handmade table

CREATE TABLE [IsolationTest2Test]
(
col1 INT PRIMARY KEY ,
col2 VARCHAR(20)
) ;
GO
INSERT INTO IsolationTest
VALUES ( 10, 'The first row' ) ;
INSERT INTO IsolationTest
VALUES ( 20, 'The second row' ) ;
INSERT INTO IsolationTest
VALUES ( 30, 'The third row' ) ;
INSERT INTO IsolationTest
VALUES ( 40, 'The fourth row' ) ;
INSERT INTO IsolationTest
VALUES ( 50, 'The fifth row' ) ;
GO

So now there exists clustered index with primary key created and additioanly i added non-clustered index on column2

After all of it i'm running  following query :

USE [IsolationDB] ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN TRAN
SELECT *
FROM [dbo].[IsolationTest2Test]
WHERE col2 = 'The third row' ;

Then I examine locking and seeing following:

Question is why there is no key lock on clustred index in second query ? (Select from [IsolationTest2Test] )

Query plan of first statement :

Query plan of second query :

Now i can inderstand that first query that select all columns from the table   acquires S lock on non-clustered index key value and also it  acqiures S lock on clustered index key that has data on the leaf level.

Why second query do not use clustered index key col 1 to perform key lookup? Why it only use non-clustered index seek? Where it takes value for col1?


texts: How to SELECT multi-columns from a column?

texts:
I have a table with the structure

book_id int(11),
section_id int(11),
note_id int(11),
item_number int(11),
text varchar(255),
UNIQUE(book_id,section_id,note_id,item_number)

item_number can be 1 or 2 only.

How can I make a SELECT query to get text for item_number 1 and 2 together for each book_id,section_id,note_id

SELECT book_id, select_id, note_id, TEXT_number_1, TEXT_number_2

TEXT_number_1 = text (WHERE item_number=1)
TEXT_number_2 = text (WHERE item_number=2)

I considered two options:

GROUP BY, which can give me a CONCAT(TEXT_number_1,TEXT_number_2)
JOIN, which I am not sure what is the best query.


texts: DBCC CHECKDB command creating database file with same name but with space character in extension

texts:
I am running simple DBCC CHECKDB ('DBNAME') command on my machine every thing works perfect!

But when i execute same command on the live server the command executes without any error, but it creates one extra file int the same location as my mdf file.  For instance, if my mdf file name is "mydatabase.mdf" then this command is creating one more file with name "mydatabase.mdf ".  Additionally, I am not able to delete this file.

Whats wrong here?


texts: how effective creating 2 database with same content?

texts:
I'm in a dilemma if creating redundant database will speed up the access considering only the CRUD factor.

Is it effective to create a dedicated DB for creating/updating/deleting records
and a separate DB for viewing only?

Currently using MySQL.


texts: how to extract current year and combine with specific month and date as a date

texts:
I have to get the current year with June the 16th as a date.

For example, if I run the query now I want to get 2015-06-16.

I tried the following:

CONVERT(DATETIME,cast( (cast(YEAR(GetDate()) AS VARCHAR(4))+'-06-16 00:00:00.000' as varchar (24) ),102)

but it didn't work.


texts: Aggregate data from multiple rows into single row

texts:
In my table I have rows, and each row has some data and its version. When column is not-null, it overrides an old value otherwise nothing happens.

Pseudocode (in C#)

var resultTable = new List<Row>();
foreach (var group in table.GroupBy(x => x.ID).Select(g => g.OrderBy(x => x.Priority)))
{
    var row = Row.GetEmpty();
    foreach (var anotherRow in group)
    {
        foreach (var column in group.Columns)
        {
            if (column.GetValue(anotherRow) != null)
                column.SetValue(row, anotherRow);
        }
    }
    resultTable.Add(row);
}

Table is large enough (several tens of columns, several hundreds of thousands rows), so it's desired to do it in single table scan (if possible).

I appreciate any help and any possibility to achieve it without any bias, SQLCLR, DSQL and so on, everything is applicable if it works fast and makes its job.

Current script for a test table:

CREATE TABLE [dbo].[T](
    [id] [int] NOT NULL,
    [A] [int] NULL,
    [B] [int] NULL,
    [C] [int] NULL,
    [Priority] [int] NOT NULL
) ON [PRIMARY]

GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20160204-122857] ON [dbo].[T]
(
    [id] ASC,
    [Priority] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (1, NULL, 3, 4, 1)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (1, 5, 6, NULL, 2)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (1, 8, NULL, NULL, 3)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (2, 634, 346, 359, 1)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (2, 34, NULL, 734, 2)
GO

input:

id  A       B       C       Priority
1   NULL    3       4       1
1   5       6       NULL    2
1   8       NULL    NULL    3
2   634     346     359     1
2   34      NULL    734     2

desired result:

id  A   B   C
1   8   6   4
2   34  346 734

In this example table is small and has only 5 columns, but in real table it will be much larger. I really want this script to work fast. I tried do it myself, but my script works for SQLSERVER2012+ so I deleted it as not applicable.

Numbers: table could have 150k of rows, 20 columns, 20-80k of unique ids and average SELECT COUNT(id) FROM T GROUP BY ID is 2..5


texts: Group by without temporary table and filesort

texts:
My aim is to make the following query faster.

SELECT SQL_NO_CACHE Links.id,Links.link,sum(ID_Links_Words.count) AS wordCount
 FROM Links LEFT JOIN ID_Links_Words ON Links.id = ID_Links_Words.link
 WHERE ID_Links_Words.word IN ('1025','1056','2353') GROUP BY Links.id

My table Links has the columns

id,link,title,description and timestamp

and the keys id (Primary),link (index), timestamp (index), (link,title,description) (fulltext)

The table ID_Links_Words has the columns

link, word, count

and the keys (link,word) (UNIQUE), link (index), word (index)

 |id | select_type | table         | type | possible_keys     | key | key_len | ref                | rows | Extra
 |---------------------------------------------------------------------------------------------------------------------------------------------------------|
 | 1     SIMPLE     ID_Links_Words   range link,link_2,word    word     4       NULL                 6        Using where; Using  temporary; Using filesort|
 |---------------------------------------------------------------------------------------------------------------------------------------------------------|
 | 1     SIMPLE     Links           eq_ref   PRIMARY           PRIMARY   4     ID_Links_Words.link   1
 |---------------------------------------------------------------------------------------------------------------------------------------------------------|

Sorry for this bad explain table :/ Hope you can read it.
I would like to get rid of the using temporary and I'm not sure if it's possible. Are there any options to improve this sql statement? I might have to deal with about 100,000 words and 1,000 links.


texts: How do you declare a set-returning-function to only be allowed in the FROM clause?

texts:
I was just reading this answer by Erwin Brandstetter. Informative as usual, it makes note that (multi-parameter) unnest is only allowed in the FROM clause. I had never tried it in the SELECT clause so I'm not that shocked, but I never saw function that could control for execution context: FROM vs SELECT set-returning-functions.

Docs for unnest(anyarray, anyarray [, ...]) (the version with multiple parameters)

  expand multiple arrays (possibly of different types) to a set of rows. This is only allowed in the FROM clause; see Section 7.2.1.4

How does one declare their set returning function to be FROM-clause only, or is this only permissible with unnest?


texts: Sleep Processes are Running Longer Than Allowed

texts:
From time to time my mysql server will run into a "too many connections" error.  I believe part of the problem is large amount of sleep processes that are running, some over 1000 seconds.

When looking into the issue, I saw this and investigated.  I checked my timeout values with

show variables like "%timeout";

And both interactive_timeout and wait_timeout are set to 600.

How/Why would these sleep processes be running so long, if they're supposed to stop at 600?


texts: Cannot add an account [non domain account ] to SQL Server 2012

texts:
Background:

I have a application pool [ABCDEMO] in IIS. The identity of the appool is "NetworkService". I am trying to add IIS APPPOOL\ABCDEMO to my SQL SERVER 2012 on a Windows SERVER 2012.

When I try to add to the Logins I get the following error.

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

Create failed for Login 'IIS APPPOOL\ABCDEMO'.  (Microsoft.SqlServer.Smo)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateLoginData.LoginPrototype.ApplyGeneralChanges(Server server)
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateLogin.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

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

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

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

Windows NT user or group 'IIS APPPOOL\ABCDEMO' not found. Check the name again. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476

------------------------------
Server Name: USALVWCOQA10
Error Number: 15401
Severity: 16
State: 1
Line Number: 1

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)


texts:
I am creating a stored procedure that:

Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up
(If specified in the SP's parameter) Creates three merge replication publications
(What I need help doing) Generating the snapshots for the three publications using sp_startpublication_snapshot

Here is my new brick wall... On this DB server, I have a 'shell' db that they will be running the SP from, that has a history table so I can keep track of who created/deleted databases using my SP's... The only parameter for sp_startpublication_snapshot is @publication... I can give it the publication name, but since I am not running it from the publishing database, how do I specify the publishing database?

i.e.: the publication shows up as:

[WC48_Database1]: upb_Inspection_PrimaryArticles

but I am running the script from the database [WC_QACatalog]

Any ideas about how to accomplish this?

Thank you,
Wes


texts: pg_stat_statements causing DB to run out of memory

texts:
I enabled pg_stat_statements on an AWS RDS postgres 9.6.3 instance with 30GB RAM.

We got the error message below in postgres log, and an event saying OS killed DB server process due to excessive memory consumption.

UTC::@:[32358]:LOG:  server process (PID 9689) was terminated by signal 9: Killed"

We opened a case with AWS and they said server is too small for the load and to increase server size, we increase to a larger server with 64GB but after few hours (took slightly longer than last time) here also the DB went into recovery as OS killed DB process.

Disabling pg_stat_statements fixes the issue, we saw the same issue in other RDS instances also.

It seems there is some memory leak with pg_stat_statements as based on our setting below this extension should not use more than 100MB of RAM.

1.  pg_stat_statements.max=100000
2.  pg_stat_statements.save=1
3.  pg_stat_statements.track_utility=1
4.  Create extension from psql command line utility.
5.  shared_preload_libraries=pg_stat_statements

Any ideas how to fix this, as we want to use pg_stat_statements to look at DB performance.

Also is there an Oracle AWR equivalent on postgres.


texts: How can we configure the Streaming Replication(master-slave) on single SAN storage in PostgreSQL?

texts: I want to configure the Streaming Replication in PostgreSQL.My data directories of Primary and Standby are in same SAN storage.Is it Possible to do replication.


texts:
Google Cloud SQL supports the PostGIS extension, but I haven't seen any documentation regarding how to work with raster data on it. To load raster data into a table, we have to use raster2pgsql driver.

In this sense, does Cloud SQL support it? Also, it would be really useful if it had out-of-db raster support considering we could store data in Cloud Storage and query it from there.


texts:
I recently installed Oracle 12c on my system on windows10. On sqlplus " /as sysdba"
I get connected to an idle instance.
So I executed startup. Now I get error

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
'C:\ORACLE12C\12C\PRODUCT\12.2.0\DBHOME_1\DATABASE\INITORCL.ORA'

I checked the path, the Initorcl file does not exist at the path.

What is the next step to get this configured?


texts: What is the syntax of mongodump and mongorestore?

texts:
I have a problem in my exercise:

Export the db atelier. For that, use the command ./mongodump -d atelier My answwer : $ mongodump -d atelier
Import the contenu of the db atelier in the db atelier2. For that, use the command ./mongorestore (use --help for see options). My answer : $ mongorestore dump/atelier -d atelier2

It doesn't work. The contenu of atelier is never copied in the db atelier2, the db atelier2 give no results so it must contains the collections of the data base atelier. Why it doesn't work ?

Please help me

Thanks


texts:
I'm at a deciding point in my project of whether to implement the database using SimpleDB or Amazon RDS. The database needs to be accessed through an iPhone app and as well through a website. Here are some factors I've been considering in the decision:

Amazon has an iOS SDK (Beta) for iPhone that can be used with their SimpleDB --
Getting Started with the AWS SDK for iOS(Beta)
Cost. SimpleDB is practically free while running a small RDS instance is $80/month or around $240/ year. If the price wasn't such a difference I would just choose the RDS and be done with it.
If SimpleDB is all that I require for my app then I am fine using that, but I do not know enough about databases yet (I just began my Database I course today) to decide if it has the functionality I need.

The other thing is that I've already built the website using PHP/MySQL and I really like how easy it is to use this combination so having to re-build it using SimpleDB is kind of annoying.

I think that I should probably just pay the cost for a year's worth of an RDS instance and just be done with it, but I just wanted to see what other people have to say about this.


texts: when do lock actually happen in a transaction?

texts:
Let's say we have a transaction A:

BEGIN TRAN A;
//--------------------< time a
 SELECT productid, unitprice
 FROM Production.Products
 WHERE productid = 2;
//--------------------< time b
 UPDATE Production.Products
 SET unitprice += 1.00
 WHERE productid = 3;
COMMIT TRAN A;

We know that for the transaction A (under default Read Committed isolation level), a share lock (for productid 2) is needed and a exclusive lock(for productid 3) is needed.

My question is, for the exclusive lock for productid 3, when did the lock happen? At the beginning of the transaction, at time a or at time b when the transaction actually starts to do the update?


texts:
One of my queries is running in to a Row Lock Contention. I've tracked down which table is causing that - so now what's the next step ? Which session is causing this table to get locked ? How do I remove the lock ?

I'm using Oracle 10g


texts: Efficient pseudo-random entry constrained selection

texts:
I am looking for a SQL query to pick up a (pseudo)random entry that matches a WHERE clause from a table of ~1,000,000 entries. This query must be as efficient  as possible (must resolve in, at most, 0.01 seconds).

Version
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

The table is defined by the below schema:

CREATE TABLE `namepool` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pool` varchar(30) NOT NULL,
  `name` varchar(15) NOT NULL DEFAULT '',
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `namepool_pool_time` (`pool`,`time`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000 DEFAULT CHARSET=latin1 COMMENT='namepool table';

The requirements are as follow:

The retrieved entry must match the clause

WHERE pool = '<pool_name>' AND (time < now() OR time IS NULL)

The retrieved entry must be different (pseudo-random is enough) for consecutive queries
The number of entries in the table is ~1,000,000
Entries with the same pool value have consecutive id values in ranges from ~50 up to ~250,000 entries
Ranges of entries with the same pool value can potentially be interleaved with ranges of entries with other pool values.

After implementing what is adviced in:

https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast
http://jan.kneschke.de/projects/mysql/order-by-rand/
http://mysql.rjweb.org/doc.php/random

I came up with the following query, which virtually adds a new column which has the same value for all entries: a random number between 0 and the max value of id for an entry that matches the aforementioned WHERE clause. Then I select the first entry that matches the WHERE clause whose id value is >= the random number. It resolves in ~0.30 seconds when the pool I am searching in has ~250,000 entries.

SELECT * FROM namepool
AS r1 JOIN(
    SELECT (rand() *
        (
        SELECT max(id) FROM namepool
            WHERE pool = 'test1'
                AND (time < now() OR time IS NULL)
        )
    )
    AS id)
AS r2 WHERE r1.id >= r2.id
        AND pool = 'test1' AND (time < now() OR time IS NULL)
ORDER BY r1.id ASC
LIMIT 1;

I discovered that the main cause of this low performance is the statement

SELECT max(id) FROM namepool
WHERE pool = 'test1' AND (time < now() OR time IS NULL)

which performs a scan and then resolves in ~0.15 seconds under the previous circumstances.


texts:
Every time I restart my Mac OS X Version 10.8.3. and open PSQL Version 9.2.4.3 (14), the PSQL terminal appears for a second and then shuts down.

Below is the info from the docs http://postgresapp.com/documentation

Uninstalling

What I do to remedy the problem is type these 3 lines of code in bash:
    open ~/Library/Application\ Support/Postgres  --> I delete the Var folder
    sudo sysctl -w kern.sysv.shmall=65536
    sudo sysctl -w kern.sysv.shmmax=16777216

When I restart PSQL, it works. The problem I have an empty database. All my work is gone.
How can I get PSQL running permantely so that I do not have to restart with an empty database?


texts: MySQL 5.7 - JSON - Searching array of KV objects

texts:
Version: MySQL 5.7.25, GCP CloudSQL.

I'm looking to store Twilio SMS delivery status updates in MySQL. I wanted to try using JSON for this to store one record per SMS, but then have a JSON column to update an array of KV objects with the statuses. First time we're really looking at using MySQL's JSON functionality.

The CREATE table, roughly, looks like (ignore the lack of indexes):

create table sms_notification (
    id bigint(20) unsigned auto_increment primary key,
    msg_id varchar(190),
    company_id bigint(20),
    from_user_id bigint(20),
    to_number varchar(150),
    `body` text,
    created datetime default now() not null,
    status JSON not null
    ) charset=utf8mb4;

An insert into this table could roughly look like:

INSERT INTO sms_notification (id, msg_id, company_id, from_user_id, to_number, body, created, status)
VALUES (
  2, 'msg_ac829d...' 384, 2677, '+1xxx', 'Second message', '2020-01-28 22:12:41',
'[{"time": "2019-01-01 00:00:00", "status": "sent"}, {"time": "2019-01-02 00:00:00", "status": "failed"}]'
);

Key here is storing all status updates we get from a twilio webhook about a specific message in one record.

Playing around with queries I am running into limitations of how to search for certain things

Grabbing all failed messages for a user can be done with a %-like query:
select * from sms_notification where to_number = '+1xxx' and status like '%failed%';

But querying json like that seems... to not really make it any different than a text field.

I've attempted to use the json_contains and json_search but MySQL seems to struggle with array searches - or more likely I have my query set up incorrectly.

Searching array paths on the status field can be $[*].status, or $**.status if I understand correctly, but neither appear to be able to be used in a WHERE?

Neither
select * from sms_notification where json_extract(status, '$[*].status') = 'delivered';
nor select * from sms_notification where json_contains(status, 'delivered', '$**.status') appear to work. Neither do combinations of different path searches in either json function.

Am I completely misunderstanding how to use json in searches, or is this a limitation of searching an array of objects in MySQL?


texts: Is there a way to order a PostgreSQL table by its primary key automatically?

texts:
I want to dump the whole content of a table, ordered by its primary key. Intuitively, I would like to do a

SELECT * FROM %table_name% ORDER BY %primary_key_of(table_name)%;

This would be run by an external tool, knowing the list of the tables, but not knowing their primary keys.

If there is no better option, we can assume that all primary keys are single-column.

I am using PostgreSQL-9.5, but an upgrade is possible.


texts: Master/Slave mysql Error replication a foreign key constraint fails

texts:
I have some issues regarding the DB Master/Slave out of sync, it's been 10 days when the issue started and everyday i tried manually to sync those DB by skipping the error

==="Error 'Cannot add or update a child row: a foreign key constraint fails" ===

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Secondary2-mysql-bin.000019
          Read_Master_Log_Pos: 444543195
               Relay_Log_File: Hostname1-relay-bin.002176
                Relay_Log_Pos: 435496347
        Relay_Master_Log_File: Secondary2-mysql-bin.000019
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: ipcr_db
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: table=test_db.log,test_db.heartbeats
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1452
                   Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (`test_db`.`column2`, CONSTRAINT `column3` FOREIGN KEY (`participant_id`) REFERENCES `parts` (`part_id`) ON DELETE CASCADE ON)' on query. Default database: 'test_db'.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 435496173
              Relay_Log_Space: 444543605
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1452
               Last_SQL_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (`test_db`.`column2`, CONSTRAINT `column2` FOREIGN KEY (`part`) REFERENCES `part` (`part`) ON DELETE CASCADE ON)' on query. Default database: 'test_db'.
             Master_Server_Id: 2
                  Master_UUID: 4642e16c-2050-11e5-a03c-000c29859f45
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 170727 08:18:41
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

..but skipping the error , data from Master will not be sent towards Slave so in the end I inserting manualy the missing data from master to slave, to have the same data and to be correctly synced.

To skip the error or to correct Read_Master_Log_Pos: with  Exec_Master_Log_Pos I am typing:

mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

If anyone db expert can help me i would appreciate this a lot.

Regards,


texts: Is it possible to enforce corresponding hierarchies?

texts:
I constructed the following toy example to illustrate my question.

The left side of the schema describes a an abstract product consisting of its parts.

The right side of the schema describes actual produced instances of these products/parts. The horizontal relations indicate from which "template" the instance was built. Note that it is possible that a part_instance is initially not yet assigned to a product_instance.

Here's the challenge:

Assigning a part_instance to a product_instance should only be possible if the part from which the part_instance was built is actually a part of the product from which product_instance was built from. So the (optional) hierarchy on the right should be enforced to correspond to the hierarchy on the left.

Or as an example: Let's assume part1 belongs to product1 and part2 belongs to product2. Now let's create productInstance1 linked to product1, productInstance2 linked to product2, partInstance1 linked to part1 and partInstance2 linked to part2. It should be allowed to assign partInstance1 to productInstance1, but not to productInstance2. Similarly it should be allowed to assign partInstance2 to productInstance2 but not to productInstance1.

Question:

Is it possible to enforce this rule in the database?


texts: Creating database/tables in SequelPro (or MySQL) from External FTP Server

texts:
I just started getting a backup file (*.7z) from the folks that run our database once a week. They're sending it to me through an FTP server (I use FileZilla) -- but the file is pretty huge: 5.8 billion bytes. I could download the file to my computer, but it would probably take about seven hours to do so.

Can somebody help walk me through if it's possible to grab their .7z file through MySQL (or specifically SequelPro would be amazing, since that's what I use) from their server, so that I don't have to download it every time? I've never logged on to use MySQL before from anything but my 'root' login or local server.

If somebody could tell me the steps I need to take, it'd be very helpful! Would be happy to provide more information if any more is needed.

Thanks, everyone!


texts: How do I judge whether a given sql statement has sql injection risk or not

texts: Analysis the sql comes from sql log only, trying to find out which sql statement has risk of sql injection. it is different from the perspective of application side check.


texts:
I’m having trouble with a new server. I installed the optimized configuration for Ubuntu server with EasyEngine of VirtuBox.

When migrating my website with template newspaper, the CPU consumption is very high.

Here is a mysql tuner report.

MySQL tuner result:

I have a database with more than 400 thousand posts. My server is:

Intel Core i7-6700K CPU
HDD 250 GB SSD
RAM 16 GB DDR4-2133 PC4-17000

with easyengine + rediscache + wordpress.

With little access the consumption of CPU is a load average: 7.56, 3.43, 4.81.

Thank you in advance for your attention.


texts: Setting Up Named SQL Server Instance Network Connection

texts:
I have successfully installed a second and named instance of MS SQL Server (14) on a Windows Server 16 system. The first instance is the default instance, and it was set up with the correct IP address and port 1433.
I have also looked at this link and followed its instructions where applicable. Under the 2017 SQL Server Configuration Manager, I found my named instance, and saw that its IP address was something outside our firewall, so I modified that to be the server's address. The port was a dynamic port. I went into the SQL server database, and allow incoming connections was checked. The rest of the protocol settings were identical to the default instance, shared memory and tcp/ip enabled and named pipes disabled.
I am using an older SQL Manager to connect, but I know it works to connect to the default instance.
The Windows firewall has the dynamic port added to the 1433 port which is in the all SQL inbound rule. I have restarted the named services, and I still cannot connect. What else should I try?
Edit:
I am adding the error message text; and the browser is running, and was restarted.

TITLE: Connect to Server

Cannot connect to patriot\building_permits.

ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:
OK


texts:
I need to import a saved .dmp made with exp in Oracle9i in another server which is a copy of the server where the save was made, the save is from a specific scheme (PERCON) that is the one I want to import in the other server, I am using exp/imp Well it is an Oracle 9i the export command is:

exp system/ssss CONSISTENT=y OWNER=PERCON
FILE=/backup_exp/export_PERCON.dmp log=/backup_exp/export_PERCON.log BUFFER=30720

I would like to know how to import into the copy server since I understand that the method exp launch several errors, is it better to delete the PERCON schema on the copy server before the import? Thank you very much.


texts: MariaDB service won't start (mysqld.sock not found)

texts:
My last error.log Logfile is from the 08. July and the system won't create newer Logfiles.

170708 13:55:34 [Note] InnoDB: Using mutexes to ref count buffer pool pages
170708 13:55:34 [Note] InnoDB: The InnoDB memory heap is disabled
170708 13:55:34 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
170708 13:55:34 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
170708 13:55:34 [Note] InnoDB: Compressed tables use zlib 1.2.8
170708 13:55:34 [Note] InnoDB: Using Linux native AIO
170708 13:55:34 [Note] InnoDB: Not using CPU crc32 instructions
170708 13:55:34 [Note] InnoDB: Initializing buffer pool, size = 128.0M
170708 13:55:34 [Note] InnoDB: Completed initialization of buffer pool
170708 13:55:34 [Note] InnoDB: Highest supported file format is Barracuda.
170708 13:55:34 [Note] InnoDB: The log sequence numbers 4530292 and 4530292 in ibdata files do not mat
ch the log sequence number 5818903 in the ib_logfiles!
170708 13:55:34 [Note] InnoDB: Database was not shutdown normally!
170708 13:55:34 [Note] InnoDB: Starting crash recovery.
170708 13:55:34 [Note] InnoDB: Reading tablespace information from the .ibd files...
170708 13:55:34 [Note] InnoDB: Restoring possible half-written data pages
170708 13:55:34 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Error: trying to access page number 4294896896 in space 0,
InnoDB: space name ./ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
2017-07-08 13:55:34 76fe3000  InnoDB: Assertion failure in thread 1996369920 in file fil0fil.cc line 5
612
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
170708 13:55:34 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.0.30-MariaDB-0+deb8u2
key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=153
thread_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 351246 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x0 thread_stack 0x30000
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Right now i tried to move the content from /var/lib/mysql to a fresly installed server with only MariaDB installed.

(Stop MariaDB Server -> backup /var/lib/mysql directory on new server > tar /var/lib/mysql directory from not working server > copy tar-file to new server >  extract > start MariaDB Server => Failed)

I have no clue what to do next - all i need is to backup a single database.

System:

Raspbian (latest)
MariaDB Ver 15.1 Distrib 10.0.30-MariaDB for debian-linux-gnueabihf

Looking forward for some help

Thanks in advance


texts: Impact of shrinking transaction log file after transaction log backup in SQL Server

texts:
In SQL Server, is there any performance issue if we shrink log file after taking the transaction log backup.
If yes, then please explain the reason.


texts: How to include Innodb_trx_id in MySQL slow log

texts:
Evaluating MySQL's slow query log with pt-query-digest from the Percona Toolkit, I need to group queries by transaction. As it seems, the easiest way to do this is to group by the Innodb_trx_id attribute.

However, my logs don't include this attribute. I tried with vanilla MySQL, MariaDB and Percona Server -- so I figure, you probably need to enable logging of this attribute. Any pointers on how to do this?

Thanks,
Victor


texts: In which form Microsoft SQL password should be given when creating a user?

texts:
I am creating a Microsoft SQL server user with the guidance of this MSDN article and giving the system the following commands:

  CREATE LOGIN MyName WITH PASSWORD = 'MyPass';
  USE My$DatabaseName;
  CREATE USER MyName FOR LOGIN MyName;
  GO

I use SQL Studio to look again the password by clicking the name on Logins list. There I see too many stars on the password that it could represent my given password.

My question is that if I gave the password according to that I wrote (in plain text), did I give it correctly? I have problems to login to the database and I don't know now is the password there ok or not.

(The link on MSDN does not say anything about encrypting, but I cannot say if the example passwords follow some encrypting rule or not.)


texts: Does cmdexec have additional process isolation beyond cmd.exe?

texts:
I have a cmdexec step in a sql agent job that includes a redirection into a file at the end. It works as expected from a CMD shell running in the context of SQL Agent service account and produces a file in the desired location. If I run it as an agent job, however, the step 'succeeds' but never produces the output file. In both cases, the service account obviously has filesystem permissions and system rights sufficient to perform this action.

Is the cmdexec environment more restrictive in someway than just running cmd.exe?


texts: Replacing NULL values with one string from provided list

texts:
I am trying to replace NULL values in table by using SELECT with one (randomized?) string from the set that I provide to the compiler.

Example:

|id | date |
+---+------+
| 1 | 2017 |
| 2 | NULL |
| 3 | NULL |

I want the NULL values to be '2015', '2012', etc, taken randomly from the set I specify.

Unfortunately COALESCE returns only first not null value rather than one from the specified. Is there some function that works like:

IF column_value = 'NULL (or something)' THEN RAND(string1, string2, string3, string4).

Thanks in advance.


texts: How do I join these two tables, showing results from the second table?


texts:
Postgres 11
I want to make a table calendars, that is at the same time partitioned by range (one month of data) and a list of keys.
The reason is that:

I want to prune months that are not longer updated.
I need all the data to be partitioned by list of ids for further joins with and other partitioned table listings, that is partitined by the same list of keys.

The documentation says:

Declarative partitioning only supports range, list and hash partitioning, whereas table inheritance allows data to be divided in a manner of the user's choosing.

Thus, if I get it right, this means that my taks can not be done with Declarative partitioning, but probably can be done using Inheritance.
So, I try to reproduce the given example with my modification
CREATE TABLE measurement (
                city_id         int not null,
                logdate         date not null,
                peaktemp        int,
                unitsales       int
            ) PARTITION BY RANGE (logdate); # this already does not accept mixed types of partitioning

than I try to create a partition with my mixed rules of partitining:
CREATE TABLE measurement_y2006m02 (
                CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01'
                        AND paektamp >0 AND peaktemp <=10)
                      ) INHERITS (measurement);

this gives:
cannot inherit from partitioned table "measurement"

Thank you!


texts: Is there a way to automatically update a view based on daily created tables?

texts:
Let's say I have a table that is created daily:

20181227
20181226
20181225
...

There is view that is like:

CREATE OR REPLACE VIEW someview
AS
SELECT * FROM 20181227
UNION
SELECT * FROM 20181226
UNION
SELECT * FROM 20181225
...

Is there a way to replace that with another code that automatically grabs data from all those tables without having to make a union for each one? Right now there is a bash script that runs daily and regenerates the view with a new union daily, but that's inelegant.


texts:
I am following How To Create a SQL Server Database Programmatically but find limited resources explaining the syntax is greater detail other than MSDN: Filegroup Options.

Take the following code on MSDN for example:

SqlConnection myConn =
new SqlConnection ("Server=localhost;Integrated security=SSPI;database=master");

str = "CREATE DATABASE MyDatabase ON PRIMARY " +
    "(NAME = MyDatabase_Data, " +
    "FILENAME = 'C:\\MyDatabaseData.mdf', " +
    "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
    "LOG ON (NAME = MyDatabase_Log, " +
    "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
    "SIZE = 1MB, " +
    "MAXSIZE = 5MB, " +
    "FILEGROWTH = 10%)"

Questions:

Back in SQL Server 2012, I would have in code Server=localhost\SQLExpress where Configuration Manager would show the instance as SQL Server (SQLExpress). Now with SQL Server 2016, I do not understand why it is simply Server=localhost because in Configuration Manager there is SQL Server (MSSQLServer) and I thought the syntax should be Server=localhost\MSSQLServer instead. Is it related to the Client Protocol? (but the Protocol did not differentiate the two version of SQL Server.
What is the difference between Database's name (MyDatabase) and NAME (MyDatabase_Data)? Why the need to assign a different name to a database which already has a name. What is the purpose?
How does the file grow in FILEGROWTH? Does it grow per SQL query? (For example I am now allowed to inserting large enough of textual data exceeding 1MB (10% of MAXSIZE) in one query) Or, does it mean the rate of growth (1MB per second)?


texts:
We are testing a particular HSM to use replace the Oracle wallet and user as a key-store for column encryption.  We are using redundant HSMs so that things will continue to run even with a complete loss of one HSM.  This works fine when connectivity is lost to one HSM at a time.

The problem we found is that when there is a complete loss of connectivity to both devices, a restoration of connectivity is not sufficient to continue to allow decryption of encrypted columns.  The only way we found to restore connectivity in this scenario is to restart the instance.

The question is, which of these do our findings represent?

A problem with the way we have configured the HSM.
A limitation of this particular HSM.
A limitation of the way the Oracle database works with any HSM.


texts: What does the position of the ON clause actually mean?

texts:
The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, not found in tutorials and I have not found any web resource that even mentions that this is possible.

Here is a script to play around with:

SELECT *
INTO #widgets1
FROM (VALUES (1), (2), (3)) x(WidgetID)

SELECT *
INTO #widgets2
FROM (VALUES (1, 'SomeValue1'), (2, 'SomeValue2'), (3, 'SomeValue3')) x(WidgetID, SomeValue)

SELECT *
INTO #widgetProperties
FROM (VALUES
    (1, 'a'), (1, 'b'),
    (2, 'a'), (2, 'b'))
x(WidgetID, PropertyName)

--q1
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN #widgets2 w2 ON w2.WidgetID = w1.WidgetID
LEFT JOIN #widgetProperties wp ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'
ORDER BY w1.WidgetID

--q2
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN #widgets2 w2 --no ON clause here
JOIN #widgetProperties wp
 ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'
 ON w2.WidgetID = w1.WidgetID
ORDER BY w1.WidgetID

--q3
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN (
    #widgets2 w2 --no SELECT or FROM here
    JOIN #widgetProperties wp
    ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b')
ON w2.WidgetID = w1.WidgetID
ORDER BY w1.WidgetID

q1 looks normal. q2 and q3 have these unusual positionings of the ON clause.

This script does not necessarily make much sense. It was hard for me to contrive a meaningful scenario.

So what do these unusual syntax patterns mean? How is this defined? I noticed that not all positions and orderings for the two ON clauses are allowed. What are the rules governing this?

Also is it ever a good idea to write queries like this?


texts: Latency appear to be an issue with linked server

texts:
A client have a data warehouse server with a linked server set-up. The data warehouse server is SQL Server 2014, while the linked server is a SQL Server, but version unknown.

Those two servers was previously physically at the same location, but a while ago the linked server was moved across the Atlantic. The performance was drastically hurt.

While I do understand that the latency is affected, I did not expect the throughput to be hurt as much. I'm talking about 20 minutes before and 15 hours after the relocation.

Monitoring wait stats shows pretty much async_network_io only.

I've tried to research how a query is processed across linked servers, but have not been able to find something useful. It appears to me that it's not as simple as the data warehouse sending a query to the linked server and the linked server starts to feed data back to the data warehouse. I do get the impression that it is continuous messages sent back and forth and therefor the latency plays a huge part.

The queries are simple selects from single tables.

Setting this up in SSIS improves performance to an acceptable level, but will take a lot of time to set up.

Anyone knows what's going on here?

UPDATE - Further details requested from David S

A typical table that is selected from the linked server and inserted to the local server is ~5 000 000 rows @ 4 GB

The query runs on a view:

CREATE VIEW [dbo].[Order_line]
AS
SELECT [ordno]
  ,[divcode]
  ,dbo.UTCOFFSET([regdate]) as regdate
  ...
  ,[o08rqtyb]
  ,[marowver]
FROM [LinkedServer].[Db1].[dbo].[O08T1]

The query to insert and update rows looks like this (Contained in a SP)

UPDATE
    T1
SET
    T1.[ordno] = T2.[ordno],
    T1.[divcode] = T2.[divcode],
    ...
    T1.[marowver] = T2.[marowver],
    T1.[updateTime] = GETDATE()
FROM
    [t_Order_line] T1
JOIN [Order_line] T2 ON
    T1.shorto08 = T2.shorto08 and
    T1.marowver < T2.marowver

INSERT INTO [ASTRO_Auburn].[dbo].[t_Order_line]
       ([ordno]
       ,[divcode]
       ...
       ,[marowver]
       ,[insertTime])
SELECT T1.[ordno]
    ,T1.[divcode]
    ...
    ,T1.[marowver]
    ,GETDATE()
FROM [Order_line] T1 WITH (NOLOCK)
LEFT JOIN [t_Order_line] T2 ON
    T1.shorto08 = t2.shorto08
WHERE t2.[ordno] IS NULL

I know that there are questionable decisions taken when this was set-up, but it is the legacy from another time.

Fetching the query plan that is executed on the remote server only shows a Table scan. Possibly that scan is executed twice? But that's not the major issue here.


texts: CHECK constraint to enforce ###.###.#### pattern match

texts:
I would like to have a check constraint that enforces this regex pattern for a multi-part code:

^\d{3}\.\d{3}\.\d{4}$

...three numbers, a period, three numbers, a period, four numbers.

Do I need to create a CLR function to enforce the pattern matching, and can the CLR function be referenced in DDL?

Is there a way to enforce the pattern in another way, using LIKE?


texts:
When using localdb and executing this, when browsing the database in sql server object explorer (in visual studio), the index is not created. I have to manually create it to make it appears. Why ?

CREATE TABLE [Instructor] (
    [ID] int NOT NULL IDENTITY,
    [LastName] nvarchar(50) NOT NULL,
    [FirstMidName] nvarchar(50) NOT NULL,
    [HireDate] datetime2 NOT NULL,
    CONSTRAINT [PK_Instructor] PRIMARY KEY ([ID])
);


texts: Unable to update or insert values table with DateTime column defaulted to GETDATE

texts:
I have atlered table by adding Datetime column. This column is not null and also default value from GETDATE().

ALTER TABLE myTable ADD myDateColun DATETIME NOT NULL DEFAULT (GETDATE())

I'm trying to update, I'm unable to update or insert values table

update  myTable set myDateColun = GETDATE() where id = someNumber

Its throwing me SQL error

  Column name or number of supplied values does not match table
  definition.

Note: This table has trigger defined on insert and delete scenario for data, which updates synonym table linked to this table in other database.

Any ideas what can be reason for SQL errors in above scenario?


texts: How can I handle multiple users for an online CRM?

texts:
First timer here so please bear with me. I'm not a database expert so I'm here to learn.

My client asked me to build an online CRM that is an online payroll and rostering system where they can manage projects and employees. I'm almost done with developing the platform but I need to solve a problem. The database has 115 tables. Now to my understanding, it should be one database per CRM user to avoid conflicts. Am I right?

I'd like to know how I should handle this on the production side. On the development side it's pretty simple one database to test but on the production server they can't all possibly use one database? If there are 1000 companies using the CRM, does it mean 1000 different databases? I need help understanding how to handle this situation.


texts: View foreign key constraints so I can delete a table

texts:
I have copied a database I was working on so I could change some key elements of its design for my new version.

I want to delete totally some tables from the database however I cannot due to some foreign key constraints.

1) How can I view the foreign key constraints that exist on a table?

2) How can I delete the foreign keys and the table?

I am viewing this doc for 2008R2 but I am not understanding it sys.foreign_keys


texts: MySQL is not logging queries into log file

texts:
I am using MySQL 5.5 with Amazon Linux 2013. Following is my configuration for log file.

slow_query_log = 1
long_query_time=0
log-error = /var/log/mysqld.log
slow_query_log_file = /var/log/mysqld_slow_query.log
general-log = 'ON'
log_queries_not_using_indexes = 1
general_log_file = /var/log/mysqld_normal.log
log = /var/log/mysqld_normal.log

But, It is not logging the queries in log file. particularly I have to find the queries which is not using Index at all.

Please help me on this. Thank you.


texts: How safe are PostgreSQL CONCURRENTLY option

texts:
As I know CONCURRENTLY let DATABASE create an index without any lock though It could incur more CPU usage.

I am planning to create an index in production DATABASE with the option.

Before applying that I want to check for sure.

Is it safe enough to use in production DATABASE regardless of how many rows there and how many INSERT, UPDATE is executed while creating the index?


texts: SQL Server Agent - Do not show job step details and column headers in output file

texts:
I have a SQL Server Agent job which runs a select statement on a schedule and appends results to a CSV file

(Right click the job --> properties --> advanced and then adding a file path to the output file location)

This is great except the output it generates displays

Job 'MyJobName' : Step1, 'Step Name' : Began executing YYYY-MM-DD HH-MM-SS

as well as the column headers throughout the file (it adds this with each append)

Is there a way to not output the Job name / step details and column headers?


texts: SQL Server 2012 SSIS : retrieve line number in Flat File Connection Manager

texts:
I'm developing a ETL, that uses a FFCM to parse data from a csv and loads it in a SQL Server table.

Some float data aren't parsing properly, so I need to see the result in the table and get back to the flat file to see how it is there.

I'm reading the file in Notepad++, it would be much easier to find data if I had its line number.

Is it possible for FFCM/Flat File Source to add the line number to the list of columns, so I can save it in the table?


texts:
According PostgreSQL documentation I can use pg_waldump for display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster.

I have clean installation of PostgreSQL-11 server on Ubuntu 18.04, but I have not this command:

  pg_waldump: command not found

Below list of present pg utilites:

pg_archivecleanup    pg_conftool          pg_dumpall           pg_recvlogical       pg_virtualenv
pg_basebackup        pg_createcluster     pg_isready           pg_renamecluster
pg_buildext          pg_ctlcluster        pg_lsclusters        pg_restore
pg_config            pg_dropcluster       pg_receivewal        pg_updatedicts
pg_config.libpq-dev  pg_dump              pg_receivexlog       pg_upgradecluster

What I need to do for use this command on my server


texts: Much slower replication on Innodb compared to myisam

texts:
Good morning. I wanted to test how InnoDB will work on my servers and for this I made a slave and connected it to my master with myisam.
The problem is that when the slave works on innodb, replication significantly slows down and at peak moments (about 6000 entries / min) begins to significantly delay.
It is definitely not a problem with the connection with the master because the download does not have any delays. The database can not perform queries from relay logs quickly enough. However, when the table to which 99.9% of the replicated entries get is of the type myisam, everything returns to normal and replication is not delayed.
Replicated entries are 95% inserts.

The database is very small because the application after processing data immediately deletes them so the number of records is a maximum of several thousand. The problem with replication occurs even when mysql is the only running process.

Is there any way to speed up replication in case I want to use innodb?

Specification:

MySQL version: 5.7.25
Server: c5.xlarge https://aws.amazon.com/ec2/instance-types/c5/
Database ssd: 900 IOPS
Ubuntu 14.04

MySQL config:

innodb_buffer_pool_size = 4G

innodb_log_file_size = 500M

innodb_file_per_table = 1

innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 2

key_buffer_size = 32M

tmp_table_size = 32M

max_heap_table_size = 32M

query_cache_type = 0

query_cache_size = 0

max_connections = 700

thread_cache_size = 10

open_files_limit = 65535

server-id =  2

symbolic-links = 0

log-bin = mysql-bin

log-slave-updates=ON

gtid-mode = on

enforce-gtid-consistency = 1

binlog_checksum = NONE

expire_logs_days = 1

slow_query_log = 1

log_slow_slave_statements = 1

Exemplary insert from relaylog:

abc.abc.BEGIN
abc.abc.INSERT INTO table (id,aciD,timestamp,statusCode,latitude,longitude,gpsAge,speedKPH,heading,altitude,transportID,inputMask,outputMask,ignitionState,address,dataSource,rawData,distanceKM,odometerKM,odometerOffset
KM,geozoneIndex,geozoneID,creationTime,streetAddress,city,stateProvince,postalCode,country,subdivision,speedLimitKPH,isTollRoad,gpsFixType,gpsFixStatus,horzAccuracy,vertAccuracy,HDOP,satelliteCount,batteryLevel,batteryVolts,batteryTemp,s
ignalStrength,sequence,priority,entityID,entityType,driverID,driverStatus,driverMessage,sensorLow,sensorHigh,costCenter,jobNumber,rfidTag,attachType,attachData,etaTimestamp,etaUniqueID,etaDistanceKM,etaLatitude,etaLongitude,stopID,stopSt
atus,stopIndex,messageTimestamp,messageID,messageStatus,fuelPressure,fuelUsage,fuelTemp,fuelLevel,fuelLevel2,fuelEconomy,fuelTotal,fuelTrip,fuelIdle,fuelPTO,fuelEngineOn,engineRpm,engineHours,engineOnHours,engineLoad,engineTorque,idleHou
rs,workHours,workDistanceKM,transOilTemp,oilCoolerInTemp,oilCoolerOutTemp,coolantPressure,coolantLevel,coolantTemp,engineTemp,intakeTemp,brakeGForce,acceleration,accelerometerXYZ,brakePressure,massAirFlowRate,oilPressure,oilLevel,oilTemp
,airPressure,airFilterPressure,turboPressure,ptoEngaged,ptoHours,ptoDistanceKM,throttlePos,brakePos,vBatteryVolts,j1708Fault,faultCode,malfunctionLamp,tirePressure,tireTemp,tankLevel,doorStateMask,lightsStateMask,thermoAverage0,thermoAve
rage1,frequencyHz,tripStartTime,tripStopTime,tripDistanceKM,tripIdleHours,tripPtoHours,tripMaxSpeedKPH,tripMaxRpm,tripStartLatitude,tripStartLongitude,tripElapsedSeconds,tripBrakeCount,tripClutchCount)
VALUES ('sth','sth232',155379534,344,26.33,-91.1790616,0,32.828,34.0,1.05,'',1,0,1,'','UDP','0x434352STHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH
HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH',0.0,5453.285,0.0,0,'',4554353,'','','','','','',0.0,0,0,2,0.0,0.0,0.8,12,0.0,4.172,0.0,-81.0,34,0,'',0,'',0,'',0,0,0,'','','','',0,0,0.0,0
.0,0.0,0,0,0,0,0,0,0.0,36.9,0.0,0.7080000000000001,0.0,0.0,0.0,0.0,0.0,0.0,0.0,44,2.54,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,85.0,0.0,0.0,0.0,0.0,'',0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0,0.0,0.0,0.876,0.0,12.01400000000
0001,0,'',0,'','',0.0,0,0,0.0,0.0,0.0,0,0,5143.809,0.0,0.0,0.0,0,0.0,0.0,0,0,0)abc.abc.COMMIT


texts: Is it possible to import a single mysql database directory?

texts: I am somewhat new to mysql. I created some databases recently and, to back them up, I just copied the folders with those database names in the mysql data directory (i.e containing only db.opt, .frm, and .ibd files). Unfortunately, my computer crashed. I now realize I did not back up the databases in the correct way (as I understand now, I should either have backed up the entire mysql data directory or used mysqldump). However, is there any way to recover the information from these data folders or is it lost?


texts: Keeping default max memory 2147483647 MB value in SQL Server, a bad practice?

texts:
Based on that msdn page :  server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic...

From what I learned through tutorials you should always define a max value, about min I haven't heard much. I am monitoring my pagefile using simple old perfmon and I realised that seldomly diskswapping is happenning and once it nearly caused a server crash.

Can that be related to have the default max memory setting in SQL Server?
The databases are around 150 GB and Memory is 48GB, there is no other application running on that machine.

Also, why should you have a min value? I know that a too low value can prevent the SQL-Server from starting and I know that SQL-Server keeps stuff in memory so doesn't try to release it's cache.

I am using SQL-Server 2012, thank you in advance!


texts:
Hi i'm planning to use Mysql replication for a distributed system, the topology will be:

Main DB (Master) --> Bridge DB (Master for Target DB, Slave of Main DB) --> Target DB (Slave of Bridge DB)

I will use ssh tunnels for connect the databases.

I know that replication could be asynchronously, by my concern is i want to know if Mysql replicate incrementally, the main db will be in a site where the internet connection is limited and probably will connect via mobile network only some days of the week, so i want to be sure that mysql does not send ALL data every time the databases synchronize.

Thanks in advance.


texts: Join a 'default' value only when there is no fully matching record

texts:
I need to be able to join to a 'default' row in a table (righttable) only when there is no matching record for both fields in lefttable.  Below is the raw contents of the tables

#lefttable
--------------------
value  program  race
100    gold     1
100    gold     4
100    gold     5

#righttable
-------------------------
commission  program  race
14          GOLD     1
23          GOLD     NULL

In this example, where the race is 1 I need to pick the first row (commission = 14) but if the race is anything other than 1 I need to pick the NULL row that matches the other field (commission = 23)

The problem I'm having is that my query picks the null record and the matching record when joining,  resulting in duplication...

SELECT #lefttable.race,
       totalvalue = SUM(value),
       commission = SUM(value * (commission * 0.01))
  FROM #lefttable
  LEFT JOIN #righttable  ON #lefttable.program = #righttable.program
                        AND #lefttable.race = ISNULL(#righttable.race, #lefttable.race)
 GROUP BY #lefttable.race;

This results in the below

--------------------
race  totalvalue  commission
1     200         37.00
3     100         23.00
4     100         23.00

The totalvalue is double what it should be (and commission incorrect) because the join has created two rows for race 1 (one for each row in righttable)

Ideally I want to be able to solve this problem entirely within the join clause rather than having to add messy cludges to the select clause, or additional joins, or views, or modify righttable to create a row for each race regardless.

I have tried different ways of constructing the join clause but I can't quite wrap my head around how to say "Give me the row with the null race only if there is no row that matches the race in lefttable" using a sql join.

Is this possible?


texts:
Please take note that the example below is just an example, my scenario is way more complex and the way i'm trying to model it really makes sense

Let's say i'm creating a table for audit events in one of my apps - so all of the "event_created", "user_created" etc. kind of thing. The table contains several columns, some of them are a foreign keys to other tables. Over time, this single table can grow to several milion of records.

From performance perspective, is it faster and more performant to use a single table for all of them or to use separate table for each kind of event and operate on separate tables? Or it doesn't make much differrence? It might sound silly to create a separate table for each kind of event but you need to trust me that in my real world scenario, it really makes sense.


texts: Unable to understand correlation between MaxWorkers created and CPU utilization

texts:
I am having a strange issue which i am unable to understand:
On my server with 64 Logical processors having maxworkers 1472, there are peak periods in day where workers created for an hour lets say goes beyond maxworkers available. Ranges somewhere 1800-1900 in that duration.
From what I can understand and saw is there a burst of blocking happening in that period. Lots of update commands blockings its own process while that UPDATE will block other inserts and selects over that range of couple of minutes.
However CPU does not tend to increase and stays steady at 30% on avg. Should the CPU not spike to 100% or atleast much higher than 30% when workers available are exhausted ? OR those waiting threads are not even on schedulers?
Please help me understand , thanks


texts:
I have a table named "log", which basically stores error log for my php application. My application has database running on rds, and recently i had to delete logs from this table, so i downloaded a backup of this table and imported into my local database in phpmyadmin.

It imported well, but when i went into the table its showed 0 records..

But there are records as its evident from this image..

I just don't know why it isn't displaying the records, even if they are there, i have tried changing the table name too but it didn't had any effect..is this has anything to do with large number of records..i tried to search it but couldn't find it..please help..

thanks.


texts:
I have a SQL query given below, I want to select multiple value using like operator.

Is my Query correct?

SELECT top 1 employee_id, employee_ident, utc_dt, rx_dt
FROM       employee
INNER JOIN employee_mdata_history
ON         employee.ident=employee_mdata_history.employee_ident
WHERE      employee_id like 'emp1%' , 'emp3%'
ORDER BY   rx_dt desc

If not, can anyone correct me?

My table has large amount of data starting with 'emp1' and 'emp3'.
Can I filter the result by top 3 "emp1" and top 2 "emp3" based on rx_dt?


texts: sqlite database table with 1000's of columns

texts:
I am using sqlite3 in an application to conduct an alalysis of distances between sets of points.  Distances between these points have been precomputed using dijkstra's algorithim and left me with a table like:

    point | p0    p1    p2  ...  p1500
    ------|--------------------------
    p0    | 0     100   15  ...  X
    p1    | 100   0     10  ...  Y
    p2    | 15    10    0   ...  Z
    p150  | X     Y     Z   ...  0

I will be doing lots of I/O on the table.  For 10s of thousands of records I need to look up a set of points.  Combine with other factors and determine which point is best for that record.  Then repeat.

My question is...  in terms of performance for a database (and sqlite in particular) would it better to leave the table as is or transform the table into a long format?

    aPoint bPoint | dist
    --------------|-----
    p0     p0     | 0
    p0     p1     | 100
    p0     p2     | 15
    p0     p1500  | X
    p1     p0     | 100
    p1     p1     | 0
    p1     p2     | 10
    p1     p1500  | Y
    p2     p0     | 15
    p2     p1     | 10
    p2     p2     | 0
    p2     p1500  | Z
    p1500  p0     | X
    p1500  p1     | Y
    p1500  p2     | Z
    p1500  p1500  | 0


texts:
I had a SQL Server 2008 Express edition as a Default instance and I am having  SQL Server 2012 Evolution and SQL Server 2014 Evolution are my named instances. When I was trying to configure Replication on my SQL Server 2014 as a Distributor as well as Publisher, I am getting  the following error:

  'NITHYAN\DBA2014' cannot be configured for publishing and
  distribution. Publishing and distribution are supported only in SQL
  Server version 7.0 or later.

If I am trying to configure on SQL Server 2012 as Distributor as well as Publisher it allow me to do Replication.

I don't understood what's wrong with SQL Server 2014.
Why do I get the above error?


texts: Having trouble with Merge Join with SSIS

texts:
I am currently using Merge Join to perform an inner join on two sorted sets of data in SSIS. The problem I am having is that no records are being returned from the inner join. To troubleshoot I have performed the following:

I exported both sorted data sets into my local SQL Server, and wrote an inner join manually. This confirmed that I should be receiving 720 rows as a result of the inner join between the two data sets.

I used a lookup, instead of a merge join, to perform the inner join and this actually worked. The reason I do not want to use a lookup is because of the performance I am getting with these sets of data. To give you an idea of the data I'm working with, table A has ~16k rows and table B has ~7.8 million rows. Using the lookup component yielded a run time of over an hour.

So I was wondering if you guys could help me get the Merge Join setup correctly. To help you help me, I've provided screenshots of how I currently have things configured:
Data Flow of Job

Table A Sort

Table B Sort

Merge Join


texts: Changing database name based on project environment

texts:
I have three environments for our BI project (DEV, QA, PROD) and two databases in each environment, source(EDB) and target(EDM)

DEV environment: Source = EDB_DEV   Target = EDM_DEV
QA environment: Source = EDB_QA   Target = EDM_QA
PROD environment: Source = EDB   Target = EDM

SSIS OLE DB Source component have connection to EDB_DEV but query also refer to EDM_DEV

SELECT A, B, C
FROM tblFirst F --(table in EDB_DEV)
INNER JOIN EDM_DEV.dbo.tblEDMTable T ON T.ID = F.ID

Now I have to  migrate to QA or PROD environment. So I am wondering, is there any way I can dynamically tell which database to go based on connection/environment.

For e.g EDM_DEV in query should be replaced with EDM_QA when running in QA environment.


texts:
Scenario:

Each time data is inserted/updated/deleted into/in/from a table, multiple unrelated items of business logic need to be executed.

Question

Given that the solution is to use database triggers, which option is better?:

1) A single trigger per operation (insert/update/delete):

I_Table - executed on an insert
U_Table - executed on an update
D_Table - executed on a delete

Each trigger would handle multiple unrelated items of business logic.

OR

2) Multiple triggers per operation that were segregated by concern:

I_Table_Logging - for logging on an insert
I_Table_RI - for enforcing referential integrity on an insert
I_Table_BL - for executing business logic on an insert
U_Table_Logging - for logging on an update
U_Table_RI - for enforcing referential integrity on an update
U_Table_BL - for executing business logic on an update
D_Table_Logging - for logging on an delete
D_Table_RI - for enforcing referential integrity on an delete
D_Table_BL - for executing business logic on an delete

I prefer option 2 because a single unit of code has a single concern.  I am not a DBA, and know enough about SQL Server to make me dangerous.

The architecture and schema of the database must not be changed.  There are several places in the schema that should be enforced with explicit relationships, but unfortunately cannot be changed yet.  That is the reason I need to manually enforce referential integrity instead of relying on an explicit relationship.

Are there any compelling reasons to handle all of the concerns in a single trigger?  I'm specifically concerned about performance, order of execution, and rollback.


texts: LEFT JOIN statement is optimized on Mysql 5.5 but not 5.7, is this a bug?

texts:
The following query:

EXPLAIN SELECT usr.*,act.*,cht.*
FROM users AS usr
LEFT JOIN active_u AS act USING (uid)
LEFT JOIN chat AS cht USING(uid)
ORDER BY usr.updated DESC LIMIT 0,10

shows this on MySQL 5.5.58 (Unix):

and this on MySQL 5.7.17 (Windows):

How comes that Mysql 5.5 optimizes the query perfectly, while 5.7 makes its best to slow down the query as much as possible? It simply scans all the rows , uses temporary tables, etc. Is there some mysql variable that specifies that queries must not be optimized, or something?

I can assure that the 5.5 Unix installation is using all default values.

I know I could refactor the query as shown here to optimize it for 5.7, but how comes the query was already optimized perfectly on Mysql 5.5?

Also: Why, and this happens often, the possible_keys field shows NULL but it actually ends up using a key, like the updated key in the 5.5 screenshot?


texts:
I've spent several hours to make a "simple" SELECT with GROUP BY in Postgres without success. The GROUP BY clause is giving me problems.
I have the table cities with columns user_id and city.
user_id can be repeated, so the table can have this information:
"Bill", "New York"
"Bill", "Chicago"
"Adam", "New York"
"Mike", "Los Angeles"
...

If I would like to have the count of cities it could be make this way:
SELECT cities.city, COUNT(*) FROM cities GROUP BY cities.city

But if I want to have this count and take only one city per user (it doesn't matter if "Bill" takes "New York" or "Chicago") how could I group by cities.user_id?


texts: PostgreSQL randomising combinations with LATERAL

texts:
In the following example I have a table foo from which I'd like to pick out at random a row per group.

CREATE TABLE foo (
  line INT
);
INSERT INTO foo (line)
SELECT generate_series(0, 999, 1);

Let's say that I'd like to group by line % 10. I could do this with:

SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 AS bin, random() x
    FROM foo
    ORDER BY x
) X

What I'd like to do is get random picks from each bin several times. I had thought I'd be able to do this with generate_series() and LATERAL

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, random() x
    FROM foo
    ORDER BY x
) X
ORDER BY bin) Q
ORDER BY bin, i;

However, when I do this in PostgreSQL 9.5 I find I get the same line for a given bin for each iteration i, e.g.,

i;line;bin
1;530;0
2;530;0
3;530;0
1;611;1
2;611;1
3;611;1
...

I'm confused, as I thought the subquery containing the random() would be run differently for each line from the generate_series().

EDIT: I realised that I can achieve the same objective by generating more combinations and choosing from these with

SELECT DISTINCT ON (bin, round) round, bin, line
FROM (
    SELECT line, line % 10 as bin, round
    FROM foo, generate_series(1,3) round
    ORDER BY bin, random()
) X;

So my question is simply why didn't the first way work?

EDIT: The problem appears to be that LATERAL only acts like a for-loop if the subqueries are correlated in some way (thanks to @ypercube's comment). Hence my original approach can be fixed by adding the following small change

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(
SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, m.i, random() x -- <NOTE m.i HERE
    FROM foo
    ORDER BY x
) X
ORDER BY bin
LIMIT 3
) Q
ORDER BY bin, i;


texts: Distributed Transactions between SQL Server 2000 & MySQL Stopped Working

texts:
We have been successfully copying data from a MySQL Slave database into a SQL Server 2000 database. The MySQL server is a linked server. I have tried using v3.51 and v5.1.8 of the ODBC connector provided my MySQL.

Simply trying to insert into a temporary table in SQL Server from a table in MySQL using OPENQUERY causes the following error:

  Server: Msg 7391, Level 16, State 1, Line 1
  The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
  [OLE/DB provider returned message: [MySQL][ODBC 3.51 Driver]Optional feature not supported]
  OLE DB error trace [OLE/DB Provider 'MSDASQL ' ITransactionJoin::JoinTransaction returned 0x8004d00a].

This error started happening when replication between the MySQL master and slave failed. This has since been fixed but the error above persists.

Is there something more that needs to be done on the MySQL side? Any suggestions for the SQL Server side? I have already tried restarting DTC as well as rebooting the server.

Thanks


texts: Percent of total: Is there a more efficient way than using a subselect?

texts:
I have an existing ASSET_SUMMARY_VW view:

+-----------------------+---------------+
|      ASSET_TYPE       |     COST      |
+-----------------------+---------------+
| TRANSPORTATION        | 1,000,000,000 |
| TRANSPORTATION        |   500,000,000 |
| TRANSPORTATION        |   500,000,000 |
| ACTIVE TRANSPORTATION |   100,000,000 |
| ACTIVE TRANSPORTATION |    50,000,000 |
| STORMWATER MANAGEMENT |   500,000,000 |
+-----------------------+---------------+

I want to GROUP BY and calculate the percentage of each ASSET_TYPE compared to the total cost:

+-----------------------+---------------+------------------+
|      ASSET_TYPE       |     COST      | PERCENT_OF_TOTAL |
+-----------------------+---------------+------------------+
| TRANSPORTATION        | 2,000,000,000 |             75.4 |
| ACTIVE TRANSPORTATION |   150,000,000 |              5.6 |
| STORMWATER MANAGEMENT |   500,000,000 |             18.8 |
+-----------------------+---------------+------------------+

I can divide the COST by a subselect that gets the total cost:

SELECT
    ASSET_TYPE
    ,SUM(COST) AS COST
    ,SUM(COST/(
               SELECT
                   SUM(COST)
               FROM
                   USER.ASSET_SUMMARY_VW)
               ) AS PERCENT_OF_TOTAL
FROM
    USER.ASSET_SUMMARY_VW
GROUP BY
    ASSET_TYPE

However, it's occurred to me that using a subselect is inefficient. Is there a faster way of doing this?


texts: Error updating multiple rows on a table with triggers

texts:
I have a Table Products with a Trigger to create a record on each update made to Products (something like a Log).

Everything works fine if I update one record at a time but if I try to update multiple records (Update Products SET Name = "BLABLABLA" WHERE Price > 10) I get the following error:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression. The statement has been terminated."

My Trigger is this one:

CREATE TRIGGER [dbo].[NS_Art_Export_ProdUpdate] ON [dbo].[Produtos] FOR UPDATE
    AS BEGIN

    DECLARE @Key1 NVARCHAR(20), @Key2 NVARCHAR(20)
    DECLARE @Changed BIT

    SET @Key1 = (SELECT Counter FROM INSERTED)
    SET @Key2 = (SELECT Prod_Gen FROM INSERTED)
    SET @Changed = 0

    IF UPDATE(Codigo) SET @Changed = 1
    IF UPDATE(NOME) SET @Changed = 1
    IF UPDATE(Familia) SET @Changed = 1
    IF UPDATE(CodIVA) SET @Changed = 1
    IF UPDATE(qtEmbal) SET @Changed = 1
    IF UPDATE(Volume) SET @Changed = 1
    IF UPDATE(PesoBrt) SET @Changed = 1
    IF UPDATE(PesoLiq) SET @Changed = 1
    IF UPDATE(Status) SET @Changed = 1

    IF @Changed = 1
        INSERT INTO Log_NSArtSoft
            (Tabela,      Tipo,  Key1,   Key2,   Data_Update,
             Data_Export, Flag_Update, Data_Export2)
        VALUES
            ('Produtos',  'U',   @Key1,  @Key2,  GETDATE(),
             0, 0, 0)

END
GO

Can someone help me with this?
Thanks


texts: Use variables within a function with INSERT INTO

texts:
I want to use a variable within the function so i can grab systemstate from several clients and put them into a local table.
It looks like this but now i am stuck with making a part variable

    CREATE OR REPLACE FUNCTION schema_test.sync_systemstate(table_name TEXT, dbname TEXT, port int, hostname TEXT, username TEXT, wachtwoord TEXT) returns integer as $$

    DECLARE
    sql varchar;

    BEGIN

    sql :='truncate schema_test.' || table_name || '' ;

    execute sql;

    INSERT INTO schema_test.[variable table name]
    SELECT timestamp, component_name, component_type, status, message ...;

    RETURN port;
    END;
    $$ LANGUAGE plpgsql;

The INSERT INTO schema_test.[variable] is not working, well at least i cant make it working. Any suggestions on how to achieve that behind the schem_test comes the variable table name??


texts: How does Oracle perform in hyperconvergence systems such as nutanix, simplivity? Oracle doesn't certify the underlying storage which nutanix or simplivity offers (file level storage) but in reality case studies say those systems perform good in general but with Oracle databases, technically how sound it is to go ahead with it?