texts: The job failed. The owner (pc\user) of job FULL DB BACKUP does not have server access

texts:
SQL Server 2008R2 is failing to execute log backup hourly.

  Date      5/2/2013 10:22:19 PM Log       Job History (LOG BACKUP)

  Step ID       0 Server       pc Job Name        LOG BACKUP Step
  Name      (Job outcome) Duration     00:00:03 Sql Severity      0
  Sql Message ID        0 Operator Emailed      Operator Net
  sent       Operator Paged         Retries Attempted     0

  Message The job failed.  The owner (pc\user) of job LOG BACKUP
  does not have server access.

ps: its a windows 7 x86 ultimate virtual machine.
the account have administrator privilege.
any idea why its failing?


texts: Sql Server Maintenance Plan - Simple Recovery Mode - Adding Shrink Log

texts:
I inherited lot of old servers and Databases from other department and now I will be the one who needs to take back ups / do Maintenance stuff with it.

I see a lot of databases in simple mode (I checked with the Owners and they say they are fine with loosing one day data in case of disasters)

I see some log files bigger than the databases. I am thinking of Including shrink Log (add a Sql Task ) in my maintenance plan after the back up.

Is it a Good Practice?


texts: Creating partitions on a production database

texts:
I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals.

I have two tables that involve a composite key on columns:

Bill_Num
Bill_Date

I want to create Partition on Bill_Date. The table consists records of four years. I want to know how a new partitioned table will accommodate future years? I also want to know whether I need to make changes to the table name in existing queries and replace the table name with the new partitioned table name?


texts: MySQL Query Cache - what type of queue is used?

texts:
I am wondering what type of queue is MySQL Query Cache using.
When there is new query and cache is full which query is deleted? Oldest one? One with fewer number of hits? Or maybe there is some different logic behind that?

I tried to find something in documentation, but without success.


texts: sql server - NoSync subscriptions will need to be dropped and recreated

texts:
I am having this error message from the sql server replication monitor:

  Error messages: The subscription(s) have been marked inactive and must
  be reinitialized. NoSync subscriptions will need to be dropped and
  recreated. (Source: MSSQLServer, Error number: 21074) Get help:
  http://help/21074

The publication is SupplierDB.

I have just generated an snapshot, as you can see on the picture below.

I have followed the answers and comments and not sure what else,
my subscription is now working, however, it shows up twice now, and I can't find a way to get rid of the one that is not working.

I got it double for some reason.


texts:
I need to get allowed itemTypes for a user based on 2 different tables in a conditional way. If the users userGroup has any entries in the userGroupItemTypes table it should only show items that are in common between userGroupItemTypes AND clientItemTypes but if no entries exist in userGroupItemTypes for that users userGroup, then it should just get all the ones from clientItemTypes

itemTypes

itID    itemType
1       Item 1
2       Item 2
3       Item 3

clients

cID     client
1       Client 1

userGroups

ugID    userGroup
1       Group 1
2       Group 2

users

uID     user     cID     ugID
1       Fred     1       1
2       Sam      1       2

clientItemTypes

cID     itID
1       1
1       2
1       3

userGroupItemTypes

ugID    itID
2       1
2       2

Resaults should be:

uID     itID     user     itemType
1       1        Fred     Item 1
1       2        Fred     Item 2
2       1        Sam      Item 1
2       2        Sam      Item 2
2       3        Sam      Item 3

Basically i think the query should function like follows:

SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
IF((SELECT COUNT(*) FROM userGroupItemTypes GROUP BY uID) > 0, INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID, DO NOTHING)

But I do not know how to actually achieve this. Any help would be greatly appreciated

EDIT:
Here is a dbfiddle


texts: MariaDB Columnstore unexplained behaviour using CASE

texts:
We were updating column value based on col 'string_hash_id' value, but we couldn't get through:
 Error Code: 1815. Internal error: CAL0002: Update Failed: TreeNode::getDecimalVal: non-support conversion from string

sql:

UPDATE table1 JOIN table2 USING(id)
SET table1.calc_col = table_1.col1 * table_1.col2 * table.col3 * ((100 - (
CASE col_id WHEN 'string_hash_id' THEN 2.00 ELSE 1.5 END)) * 0.01)

col1 is signed int, rest of cols are decimal

We thought that if we cast these cols it will work but nothing happens error was the same. But when we changed case values to string it works but it does not make any sense.

  THEN 2.00 ELSE 1.5 ==> THEN '2.00' ELSE '1.5'

UPDATE table1 JOIN table2 USING(id)
SET table1.calc_col = table_1.col1 * table_1.col2 * table.col3 * ((100 - (
CASE col_id WHEN 'string_hash_id' THEN '2.00' ELSE '1.5' END)) * 0.01)

And my question is, how is this possible and why it shows TreeNode::getDecimalVal: non-support conversion from string when there is no string.

And works without issues when there is string.


texts: What to do in times of data inconsistency in Master-Master Replication in MySQL?

texts:
I am building an architechture consisting MySQL Master-Master replication with HAProxy in its front. For HAProxy i am using round_robin algorithm to distribute loads between two masters. My question is when there is a heavy write situation as
 Seconds_Behind_Master will be greater than 0, what should I do for the data inconsistency will arise when HAProxy will go divert to an unupdated Master?


texts: Is this SQL schema normalized according to 3NF?

texts:
This schema is for a sports statistics/data app

Note:

Pink background = string/char/varchar
Yellow background = int/tinyint
Green background = dateTime
bold column = PRIMARY key
id_* = FOREIGN key

Edit: round two.


texts:
I could do with some help designing a database for a website I'm working on. I should mention that I am a beginner and I have never had to design a database in the past.

Ok, my implementation needs to hold data on auctions based on 5 world regions (EU, US, KR, CN, TW). Each auction is identified with an ID that is not unique to a region but is unique to a 'realm' in that region. The final requirements are:

Able to track historical market prices of individual items up for auction over the past 4-6 months.
Able to view the current items up for auction.
Able to track sellers of items and their history.
Able to create a profile on the site.
It is not required to be able to compare regions.

Some stats:

Each region can be made up of 240+ realms.
Each realm has around 20,000 auctions up for sale at any one time.

My current plan is to work with a database for each region so 5 in total. Each one of those databases would have the following tables:

Realms - Holds information about all realms in a region
Scan - Holds a snap shot of the current items up for sale in a region. Indexed by realm.
Historical - Holds 4-6 months of history for each region. This will include every auction that was seen.
Sellers - Holds data on all sellers seen in the last 4-6 months.

I am debating whether it would be better to have just two tables and merging the Scan, Historical and Sellers tables together, then if a user wanted to view just the current scan data or information for a seller it would be calculated from the combined table on the fly.

With me being new at this, I could do with someone with more experience to take a look and let me know if I'm overlooking anything. Keep in mind that I don't know anything about performance and whether the design I have would be acceptable or not.

Anyone interested can see the documentation for the API I am working with here http://blizzard.github.com/api-wow-docs/ I'll have scheduled tasks to import the data and insert it into the database.

Feel free to ask me anything if I wasn't clear enough.


texts: Can PostgreSQL create a case sensitive database?

texts:
I'm porting some code from some version of Sybase to PostgreSQL. This is a C application that uses the Sybase client library. My approach is to write a translation layer that translates calls to dbsqlexec() to PQexec() (for example). That part is mostly working.

It appears that the Sybase database is set up in a case sensitive manner (with respect to database object names). For example, there is both a WIDGET table and a widget table. It looks like the convention in this application is that the all-uppercase names indicate the actual data tables, while the lowercase names are used as temporary tables when running some processing.

According to 4.1 Lexical Structure, "Key words and unquoted identifiers are case insensitive." I know that I can double-quote identifiers to disable automatic folding to lowercase, but I don't want to have to do that manually through the zillions of lines of code that uses this database.

Is there a way to set up PostgreSQL to disable this automatic case folding for database object identifiers?

My alternative will be to write some code that examines each SQL statement and puts double quotes around every identifier (that is not a keyword).


texts: How to Audit Successful Logins for certain logins only?

texts:
I need to setup auditing (logging) of successful logins on SQL Server (date, time, who), not for all logins on a server, but only  for a small group of logins (developers)

In short, me only interested in logging/auditing developer logins, not application logins

Tried using SQL Server audit feature, using SUCCESSFUL_LOGIN_GROUP, but it does not allow me to select "principal name" and specify logins that are interesting to me

Is there a workaround ? I need only certain logins to be audited, otherwise it will be millions of records in the audit file if I audit every successful login of every application/user


texts: SQL Server Mirroring vs 3rd party replication products such as DoubleTake

texts:
I'm charged with looking after several SQL Server instances, most of which use SQL Server mirroring to replicate data for DR purposes.

I automatically script logins, roles & jobs to the mirror, in the event of a failover we literally need to run a script to bring the mirrors online, apply the logins, apply the rolls and jobs and away they go (give or take a few mismatched SIDs & the usual niggles).

Now, I have documented this failover process in case I am not on site in the event of a failure; however, management deems the process to be too complicated and keeps trying to push 3rd party replication products such as DoubleTake that could do instance level replication.

(Company did not want to use clustering for instance level as the shared storage would be a single point of failure – they were not willing to buy multiple replicating SANs.)

We have databases ranging from a few meg to 200GB+.

Is DoubleTake really practical for DR in this manner? Can I trust that the data will be bang up to date between the primary and secondary? (We operate high safety mode mirroring for this very reason.)

I've read lots of opinions on forums, but always either biased or unconfirmed; I'm after some first-hand experience with these products.


texts: Issue with GIST indexing an ltree column

texts:
I recently read about the ltree column & how it could, supposedly, be GIST indexed to provide relatively good support; this seemed like a good match for a problem I'm currently playing with, so I created a table like so:

CREATE TABLE foo (
  id varchar(12) NOT NULL, -- ID is a hash, not a serial
  parent varchar(12) NULL references foo(id), -- some nodes may be orphaned
  pos integer NOT NULL, -- orphaned nodes do not have a position
  primary key (id)
);

This is a slight simplification, but is essentially the structure of the existing table. (Additional columns omitted)

If I extend the table thus:

ALTER TABLE foo ADD COLUMN path ltree NULL;
CREATE foo_path_gist ON foo (path) WHERE path IS NOT NULL;

And begin serially inserting records in a balanced fashion, after only some hundreds, or maybe a couple thousand, updating the index takes so much memory (the machine I'm using to test has 2GB available) that the kernel OOM killer steps in and terminates Pg.

The machine has not yet been tuned - Pg is running with all default settings - but I'm not sure what, if anything, would cause that kind of memory consumption to begin with. I could understand it if the combined size of the path nodes was large, but at this point we're talking only a few megabytes of data, at the most.

Update:

Records are inserted by selecting a key at random, and then walking down the tree until an opening is found.
All insertion is done by a single process, one at a time
The problem (excessive memory consumption) does not appear if I remove the GIST index from the column.
If I try to create the index after inserting more than a few hundred rows, I get the error:

  stack depth limit exceeded


texts: How to upgrade versions of postgresql without losing statistics

texts: I'm upgrading from 9.5 to 9.6. Running vacuum analyze on the whole db to regenerate stats is a pain. Is there any way to upgrade and migrate over the statistics so I dont have to regenerate them?


texts: How do I store spatial coordinates in SQL Server 2005

texts:
I'm looking to store GPS co-ordinates in an SQL Server 2005 for location matching of users with GPS-enabled mobile phones. I've read about STDistance on MSDN, but it only appears to exist in SQL Server 2008 (& later) along with the geometry and geography field types. I'll need to support older versions of SQL Server as well (at least 2005).

What's the best way of storing GPS co-ordinates and calculating distances between 2 GPS locations in SQL Server 2005?


texts: Why is cost threshold for parallelism ignored?

texts:
SQL Server 2012 SP2 Enterprise Edition. Users complaining of slowness. Monitoring tool shows highest wait event is CXPACKET.

Instance settings

MAXDOP: 8
Cost Threshold for Parallelism: 175

Ran sp_BlitzCache (from the Brent Ozar toolset, those guys rock) for further diagnosis and results show queries with cost under 175 going parallel.

Anyone ever see Cost Threshold for Parallelism being ignored?


texts: Availability Groups - When Does Synchronous Mode Actually Restart

texts:
Given the scenario where a connection for a synchronous replica of an AG is broken, 10 seconds (by default) elapse and the replica goes into asynchronous mode.  This allows database clients to keep writing and everyone is happy.

Then the connection is reestablished, and according to the documentation, it goes back into synchronous mode.  My question is, does it do this immediately, which would block clients from writing to the database until the replica is back in synch, or does it actually wait until it's almost synchronized before it goes back to synchronous mode?  If it goes back to synchronous mode immediately and it takes a few minutes for it to get back in synch, database write transactions will be held up for a few minutes--more "downtime."

I'm wanting to know so that if we have an extended outage on a node, we know whether we need to configure the replica as asynch so that we don't end up with more delays when the node comes back online.


texts: Revoking execute access on builtin functions on SQL Server

texts:
I am trying to lock down a SQL Server database server and I want to revoke execute access from all users on specific builtin functions such as LEFT, RIGHT SUBSTRING, etc.

When I try to execute:

REVOKE EXECUTE ON LEFT from appuser;

I get an error:

  Cannot find the object 'LEFT', because it does not exist or you do not have permission.

In addition, are there any other functions that can perform the same operation on SQL server, i.e. be used to extract portions of a static string ?

Thanks


texts: Sql Server merge replication primary key violations

texts:
Using Sql Server 2008.  Pretend I have a publisher and 2 subscribers A and B.  The replicated table has a guid on it and also a primary key that is not the guid.  Frequently, updates happen at both subscribers near simultaneously generating 2 different guids but the same primary key.  Then when they go to syncronize I get primary key violation errors.

Thinking of ways to resolve this - having the guid be generated based on the primary key or having replication use the primary key instead of guid (that seems hard/silly) or as a last resort have replication just continue on and ignore those violations.

Which route should I take?


texts:
I'm searching all over the internet and stack Overflow but every example I find is with only one row, or it just does not work.

I have this table:

TBL_RELATORIOS_TAMANHOS

table_catalog
table_name
column_name
data_type
character_maximum_length
numeric_precision
numeric_scale

Inside it I have all columns from all tables. I need to find only those columns that has a different precision, size or etc.

With row_number I got this:

;WITH CTE AS
(
SELECT DISTINCT table_name 'Tabela',
       column_name 'Coluna',
       character_maximum_length as 'Tamanho',
       numeric_precision as 'Precisao_Numerica',
       numeric_scale 'Escala_Numerica'
FROM TBL_RELATORIO_TAMANHOS
)
SELECT *,
    ROW_NUMBER() over ( partition by Tabela,Coluna order by Tabela,Coluna)
FROM CTE
ORDER BY 1,2

and with this, I can see columns that has a 2 on it, so this one is duplicated ( same column, different size, precision or etc ).

| table_Name              | Column_name      | character_maximum_length | numeric_precision | numeric_scale | Row_number |
|-------------------------|------------------|--------------------------|-------------------|---------------|------------|
| ACOES                   | ID_BMF           | 20                       | NULL              | NULL          | 1          |
| ACOES                   | NO_ACAO          | 20                       | NULL              | NULL          | 1          |
| AJUSTE_CODIGO_MITIGADOR | CODIGO_MITIGADOR | 40                       | NULL              | NULL          | 1          |
| AJUSTE_CODIGO_MITIGADOR | CONTA_DLO        | 10                       | NULL              | NULL          | 1          |
| AJUSTE_CODIGO_MITIGADOR | CONTA_DLO        | 25                       | NULL              | NULL          | 2          |
| AJUSTE_CODIGO_MITIGADOR | VALOR_PADRAO     | 1                        | NULL              | NULL          | 1          |
| AJUSTE_POPR             | CD_ELEMENTO      | 10                       | NULL              | NULL          | 1          |

As you can see, CONTA_DLO appears 2 times. So, how the hell can I list only those columns with more then 1 row_number, but returning all of them ( all duplicates ). in this case. it should return CONTA_DLO with row_number 1 and 2.


texts: MongoDB - Does WiredTiger benefit from virtual cpus cores the same way it does with physical cores?

texts:
According to https://docs.mongodb.com/manual/administration/production-notes/#id5 The WiredTiger storage engine is multithreaded and can take advantage of additional CPU cores.

We will be running mongoDB in a VMware vSphere enviroment and I am tasked with allocating resources. Does that statement hold true when it comes to virtual CPU cores?

Thanks!


texts: MongoDB map-reduce impacts insert performance on source collection

texts:
I have a "hot" MongoDB(4.0.5) capped collection with a timestamp (and several other) indexes.  Many applications (Java, NodeJS & C++) insert (never update) records into this collection at a rate of ~50 inserts/second.  Normally these inserts are very quick and take only a few milliseconds.

When I run a map-reduce query on the most recent 5 minutes of data, calls to insert new records into the source collection are blocked or delayed.  New inserts during the map-reduce call can be delayed > 10 seconds which is roughly the time that it takes to run the map-reduce operation.  The output from the map-reduce operation is written to the same database.

What can I do to tune MongoDB or the map-reduce operation so that inserts can continue to flow into the capped collection without delay while the map-reduce operation runs?  I need to get the summary analysis in near real-time so I don't want to wait until the insert load is reduced before running the analysis.

I do not believe that this is a system level resource constraint.  I have monitored CPU, network, i/o and memory during the map-reduce call and CPU idle time remains > 50% and network and i/o latency remains low.  IO/wait times remain under 5%.


texts:
We are in the process of building a new production Windows Server 2012 R2 server to run SQL Server 2014 Enterprise Edition.

There is a bit of advice on the internet that suggests for Windows Server 2012 and SQL Server 2012 I should stick to NTFS (e.g. itknowledgeexchange) as database commands like DBCC CHECKDB don't work.

However, there is also advice that SQL Server 2014 now supports ReFS (e.g. Brent Ozar, msdn social)

Is there a recommendation from Microsoft or a general rule of thumb on when to use NTFS and if/when to use ReFS for data and log files in SQL 2014 and Server 2012 R2?


texts: Get one column table when doing localization

texts:
I have got only one column for a table when i create two localized tables. Code as bellow.

-- Month
CREATE TABLE `month` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
);

-- Month Localized
CREATE TABLE `month_loc` (
  `month_id'    INT NOT NULL,
  `name`        VARCHAR(200)    NOT NULL,
  `description` VARCHAR(500)    NOT NULL,
  `lang_id`     INT NOT NULL
);

month_loc.month_id is the foreign key.

Month table holds only the primary key. Other all fields should be localized. Is this table structure correct ?

Thanks.


texts: Mysql Getting Access denied for user 'root'@'localhost' (using password: YES) constantly in log

texts:
I have MonYog running and it is monitoring my DB Server. It gives me constant alerts that the number of refused connections has exceeded the threshold etc. Looking in the LySQL.Log I can see constant login failures for root@localhost.

This is on a hosted server out in the ether so I have been as strict as possible with access etc. Firstly, the 3306 port on the firewall is open only to a couple of selected IP addresses. All other users (for dev and testing etc) use SSH. Secondly, there is no 'root' user. I renamed the root account to something else. This new named account only has localhost access.

So from the above, I just don't see how any login attempts are getting to the DB from localhost. The 3306 port is closed to all but one IP address (mine) and I am not attempting any connect to the server all through the night as the logs are showing. And anyway, I never connect as the (new) root dba user unless I need to do some special admin task.

Is there any way of finding out where these login attempts are coming from (IP address, host name, application name etc...).

Or am I looking in the wrong place. I just cant work out where they are coming from but I am convinced it cant be good!


texts:
I searched Google but couldn't find an exact answer. Some have this value at 10000 or even 20000. Others say that even 1000 is too much for 1gb of RAM. I'm Confused.

My number of opened_tables is growing. Mysqltuner says I should raise the table_cache value. I tried to raise it to 2K, then 3K, then 4K, then I made it 512 to see what happens. Here is the report:

Currently running supported MySQL version 5.0.67-community-nt-log
Operating on 32-bit architecture with less than 2GB RAM
Archive Engine Installed
Berkeley DB Engine Not Installed
Federated Engine Installed
InnoDB Engine Installed
ISAM Engine Not Installed
NDBCLUSTER Engine Not Installed
Data in InnoDB tables: 12M (Tables: 3)
Data in MEMORY tables: 380K (Tables: 2)
Data in MyISAM tables: 83M (Tables: 84)
Total fragmented tables: 16
All database users have passwords assigned
Up for: 16h 12m 55s (161K q [2.000 qps], 6K conn, TX: 281M, RX: 22M)
Reads / Writes: 69% / 31%
Total buffers: 128.0M global + 2.1M per thread (100 max threads)
Maximum possible memory usage: 334.3M (16% of installed RAM)
Slow queries: 1% (11/161K)
Highest usage of available connections: 10% (10/100)
Key buffer size / total MyISAM indexes: 32.0M/4.1M
Key buffer hit rate: 97% (849K cached / 20K reads)
Query cache efficiency: 28% (26K cached / 93K selects)
Query cache prunes per day: 0
Sorts requiring temporary tables: 1% (1 temp sorts / 21K sorts)
Temporary tables created on disk: 1% (2 on disk / 15K total)
Thread cache hit rate: 99% (10 created / 6K connections)
Table cache hit rate: 5% (139 open / 2K opened)
Open file limit used: 20% (232/1K)
Table locks acquired immediately: 99% (116K immediate / 116K locks)
InnoDB data size / buffer pool: 12.5M/32.0M
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increase table_cache gradually to avoid file descriptor limits
table_cache (> 512)
Scan Complete

mysql> show global STATUS LIKE 'open%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 222   |
| Open_streams  | 0     |
| Open_tables   | 127   |
| Opened_tables | 2335  |
+---------------+-------+
4 rows in set (0.00 sec)

However, opened_tables is still growing. Should I raise it again? Or is it not worth attention?

In addition, some people say that the problem is usually in creating a lot of temp tables. As we can see from the stats, it's not my case. What's the ratio between table_cache and RAM?


texts: Create an Audit trail on SQL Server 2008 to trace all privilege users

texts:
I need to create a trace of all the commands issued by members of SysAdmin or SecurityAdmin server Roles. I figured, I could use Profiler to do that but it will not comprise all the added members. I also checked the new Audit feature and yes, I can trace membership change on these roles but I will not get all their activity log.

All I see now is to reactivate the trace with a DDL trigger on Server Roles membership change.

Do someone brighter than me have a better idea ?


texts: SQL Server 2016, history temporal table not compressed by default in Standard Edition

texts:
I have a BI database running on SQL Server 2016 Standard SP2 CU14 (13.0.5830.85) in which there are multiple temporal tables defined with default history tables. According to Microsoft, such history tables are PAGE compressed by default. In time, size of those history tables became significant so I took a closer look at them and to my surprise - they are not compressed at all (at least that is what SSMS claims when looking at table properties).
The BI database was created using SSDT so my initial thought was that some database publish option was set that prevented compression so I executed a simple T-SQL script in SSMS to create a brand new database with temporal table:
-- Create a database
USE master;
GO

DROP DATABASE IF EXISTS TemporalDB;
GO
CREATE DATABASE TemporalDB;
GO

-- Create a temporal table
USE TemporalDB;
GO

CREATE TABLE dbo.Customer(
   Id INT IDENTITY(1,1) CONSTRAINT PK_ID PRIMARY KEY,
   CustomerName VARCHAR(50) NOT NULL ,
   StartDate DATETIME2 GENERATED ALWAYS AS ROW START  NOT NULL,
   EndDate DATETIME2 GENERATED ALWAYS AS ROW END  NOT NULL,
   PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory))
GO

Then I checked storage in SSMS and compression was disabled:

I did the same on another SQL instance with exactly the same version and edition - the same result. Then I did it on SQL 2016 Enterprise SP2 CU13 (13.0.5820.21) and it worked. It also worked on another instance of SQL 2016 Enterprise SP2 CU1.
Finally, I checked that I can manually compress the history table so the compression feature works just fine.
So does it really work by default only in Enterprise or am I doing something wrong?
Piotr


texts: Including rows from base table with left joins when these joins are 'empty'

texts:
I've got a query which counts the number of reservations in a table over several 15minutes intervals retrieved from an other table:

SELECT
   t.*, count(r.id) as nof_reservations
FROM (
   SELECT time_start, time_end FROM `interval`
   WHERE time_start < '18:00:00'
   AND time_end > '10:00:00'
) t -- holds a row per 15m interval

-- reservations of the assets
LEFT JOIN reservations r
ON   start_utc < concat('2017-04-02 ', t.time_end)
AND  end_utc > concat('2017-04-02 ', t.time_start)

-- we need to filter the assets on their type, which is
-- stored in the assets table
LEFT JOIN assets b
ON r.asset_id = b.id

WHERE r.deleted_at is null
AND b.asset_type_id = 6 -- asset type we need
GROUP BY t.time_start, t.time_end;

This produces the desired table. But when there are no reservations (nof_reservations=0) the whole row disappears:

| time_start | time_end | nof_reservations |
+------------+----------+------------------+
| 11:00:00   | 11:15:00 | 2                |
| 11:15:00   | 11:30:00 | 2                |
| 11:30:00   | 11:45:00 | 2                |
...
+------------+----------+------------------+

As I'm retrieving every interval <1800 >1000 I would have thought to get lines like

| time_start | time_end | nof_reservations |
+------------+----------+------------------+
| 10:00:00   | 10:15:00 | 0                |
| 10:15:00   | 10:30:00 | 0                |
| 10:30:00   | 10:45:00 | 0                |
| 10:45:00   | 11:00:00 | 0                |
| 11:00:00   | 11:15:00 | 2                |
| 11:15:00   | 11:30:00 | 2                |
| 11:30:00   | 11:45:00 | 2                |
...
+------------+----------+------------------+

What am I missing in the before mentioned query?

Schemas:

CREATE TABLE `reservations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `asset_id` int(11) NOT NULL,
  `start_utc` datetime NOT NULL,
  `end_utc` datetime NOT NULL
  PRIMARY KEY (`id`),
  KEY `idx_van_utc` (`start_utc`),
  KEY `id_tot_utc` (`end_utc`),
  KEY `idx_boot_id` (`asset_id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `assets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `asset_type_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `interval` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `time_start` time NOT NULL,
 `time_end` time NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


texts: How to create trigger with NOT FOR REPLICATION

texts:
I tried adding "NOT FOR REPLICATION " in many parts in the below code but ended up in the below code. I searched a lot for an example where I can see how its done with a trigger.

Can any one show me how I can modify the below code to add option NOT FOR REPLICATION in the below code.

DELIMITER //
CREATE TRIGGER update_table
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
IF (NEW.updated_replication < OLD.updated_replication) THEN
CALL raise_error;
END IF;
SET NEW.updated_replication = NOW();
END //

Or how can I alter this trigger to make it not for replication(Alter Query). I have spent hours trying this but no results.


texts: Execute Dynamic SQL Generated From Select

texts:
A business process in SQL can generate a variable to each operation 1-N  number of calls to a stored procedure whose purpose is to insert data related to the main process.

How does one execute multiple rows of generated sql from a select or is there a way of calling a stored procedure from a select?

The process before this step is an involved operation of business logic and data driven operations and has done subsequent inserts to related FK joined tables.

At that point where it needs to call a stored procedure it has data ready to be processed in a table variable. The step (logic inside the proc) calling the stored proc is an involved process that makes sense to be in a stored proc and cannot be copied to the existing proc.

Faux example of generated code generated by Select.

SELECT 'EXEC [sim].[ROI_Save] ' + CONVERT(varchar(6), VSL.PathId)           + ', '
                                + Convert(varchar(6), @ROIResultId)         + ', '
                                + CONVERT(varchar(6), RT.routeTypeResultId) + ', '
                                + CONVERT(varchar(6), RT.ROI_RouteTypeId)   + '; '
FROM  @routeResultIds RT
JOIN sim.ROI_Result_Vehicle VSL ON RT.ROI_Result_VehicleId = VSL.ROI_Result_VehicleId

Results

Does one concatenate the results and call Exec?


texts:
I have a corrupt table and I can't delete it to restore it. How do I delete this table from the database?

When I try to query on the table this is what I get...

mysql> SELECT * FROM tbl_company LIMIT 10;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: us_businessdb

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.soc            k' (111)
ERROR:
Can't connect to the server

Here is the error log;

2017-08-10T16:11:28.492290Z 4 [ERROR] InnoDB: Trying to access page number 3 in space 35, space name us_businessdb/tbl_company, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2017-08-10T16:11:28.492335Z 4 [ERROR] InnoDB: Server exits.
2017-08-10T16:11:28.724161Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-08-10T16:11:28.724259Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

So with force recovery set to innobd_force_recovery=6 this is what I get...

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_us_businessdb |
+-------------------------+
| jnct_tbl_user           |
| tbl_company             |
| tbl_contact             |
| tbl_data                |
| tbl_details             |
| tbl_sic                 |
| tbl_user                |
| tbl_zipcode             |
+-------------------------+
8 rows in set (0.00 sec)

mysql> DROP TABLE tbl_company;
ERROR 1051 (42S02): Unknown table 'us_businessdb.tbl_company'
mysql> DROP VIEW  tbl_company;
ERROR 1347 (HY000): 'us_businessdb.tbl_company' is not VIEW
mysql> CREATE TABLE tbl_company ( id int(10));
ERROR 1050 (42S01): Table 'tbl_company' already exists

For the record every time I have tried to delete this table I have:

SET_FOREIGN_KEY_CHECK=0 before I attempted to delete the table.
still received this same error every time I have tried to drop table (so no partial deletes that I am aware of.
In force recovery I can still query other tables

I just want to drop this table and reload it with the original data that I dumped originally.

What do I do to fix this? The only thing I know to do is delete the file manually but then I get errors when MySQL loads and I'm sure there has to be a better way to do this.

And also for the record, I have recreated this database several times and this same table which is the main table keeps corrupting (which is another issue in itself that I will reserve for another question). Since it's a clean MySQL Server install on Linux, previously I just reinstalled MySQL server to get rid of the error on MySQL startup for missing files where I deleted the db file manually, but this is a pain and I'm sure there is a better way.

EDIT:
This corrupt table originated from a table is full error when I was updating a single column. I had just updated about 12 million records so I'm sure it was a setting issue somewhere since my HD has plenty of space.


texts: Slow SQL Server authentication, but fast SAN, fast querying?

texts:
I am asking you after days of research but I think I don't have required skills to understand what is happening.

I have a slow authentication on a SQL Server 2008R2, which leads to a timeout for the linked application. Sometimes up to 20s for a simple SQL auth (not windows or kerberos or AD)
6GB of RAM, W 2008 R2, virtualized on VMWare.

There is a separate drive for TempDB and for the Main DB, both on a fast SAN behind fiber channel.

Queries (once authentication is ok) are quite fast (few micro seconds up to 10 s, average around 15ms).

I have the following data :

MSDB on C:
86% of 32GB
io_stall_read_ms : 2340428
num_of_reads : 45062
avg_read_stall_ms : 51.9
io_stall_write_ms  : 192217
num_of_writes : 34392
avg_write_stall_ms : 5.6
io_stalls : 2532645
total_io  : 79454
avg_io_stall_ms : 31.9

Main DB : on E:
94 % of 250GB

io_stall_read_ms : 556848206
num_of_reads : 46555392
avg_read_stall_ms : 12.0
io_stall_write_ms : 2213187501
num_of_writes : 62730741
avg_write_stall_ms : 35.3
io_stalls : 2770035707
total_io : 109286133
avg_io_stall_ms : 25.3

Master on C:,
io_stall_read_ms : 11966
num_of_reads : 2229
avg_read_stall_ms : 5.4
io_stall_write_ms : 240
num_of_writes : 104
avg_write_stall_ms : 2.3
io_stalls : 12206
total_io : 2333
avg_io_stall_ms : 5.2

Well, I don't know which column to look at, but C looks like a slow drive isn't it ?

WAITS are the following :
Oledb   28 %
Pageiolatch Ex  18 %
Writelog    13 %
Pageiolatch Sh  10 %
Cxpacket    8 %
Sos Scheduler Yield 5 %
Sleep Bpool Flush   4 %
Backupbuffer    1 %
Io Completion   1 %
Preemptive Os Authenticationops 1 %
Preemptive Os Waitforsingleobject   0 %
Backupio    0 %
Async Io Completion 0 %

My understanding is that Pageiolatch and Writelog indicates a slowness around IO completion , is that right ?

IO over C drive is quite calm : 1% of data Read / Write over 24hours, no peaks.
1 or 2 Read / Write per second, of a few KB.

Only Main Db (E:) is heavily used : 100 Reads / sec, 130 writes / sec.

Memory usage : 100 % of 6GB, swap file of 6GB full . (from what I've read, it is perfectly normal).

Well, What do you think slowness over authentication can come from then ?

Thanks a lot for your help,

S.


texts: Help using SQL Server Database Experimentation Assistant for performance testing

texts:
I am following the guide for capturing a trace in SQL Server Database Experimentation Assistant here: https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-capture-trace?view=sql-server-2017

I am using DEA version 2.6. The article above states that I should be able to choose the capture trace file size for rolling over files. However, in version 2.6 I do not have the option to set trace file size.

As a result, I end up with dozens of 500 MB files at the end of the capture. Then I am expected to sit there and replay each trace file, one by one. Is there any easier way of doing this, or of forcing the trace file to grow larger so I end up with less rollover files?

My new capture screen:

MS doc capture screen:


texts:
We have an sql database of 165GB (includes filestreaming) and we do an onsite full backup every evening using veritas. We would like to also have an online backup in place just so that we don't need to take backup tapes home. We would like to be able to restore back up to 28 days. It seems that all of the online backup companies will only do sql full backup or differential backups not incremental.

My problem is, if differential backups will look at the last full backup date and only send over changes since then, our on site backup will mess this up.E.g If i do a full online backup on Sunday.  Then we do an onsite backup at 6pm every day, at 9pm on Thursday when we do the online backup, it will only find changes from 6pm on Thu -9pm Thu. I want it to find changes from the last full online backup i.e. sunday up to 9pm on Thursday.

Does anyone have any solutions or recommendations, this must be a common issue.


texts:
I have the following table.

create table test (
   id smallint unsigned AUTO_INCREMENT,
   age tinyint not null,
   primary key(id),
   check (age<20)
);

The problem is that the CHECK constraint does not work on the age column. For example, when I insert 222 for the age field MySQL accepts it.


texts:
I have a data model in ERwin (r7) that has about 1000 tables in it. I'm right now in the process of splitting it up into various different subject areas to make it a bit more manageable.

Here's the issue: I create a subject area with approx. 50 tables. The problem is, they appear all over the subject area in seemingly random places. Even if I zoom out as much as possible, I can't see the entire subject area, and I need to scroll around to reach the tables. Additionally, there's an obscenely large amount of unnecessary blank space between the tables.

Is there some sort of shortcut to condense all the tables in the subject area into one area (especially when there are tables that are also connected through relations)? Currently I'm moving every piece one or two at a time, but I'm hoping there's a quicker way to organize everything.

Thanks in advance for your help!


texts: Does Data Migration Advisor migrate system databases?

texts: I would like to know if Data Migration Assistant copies system databases (master, model, msdb) into the new server.


texts:
we are currently running at the edge of resources with our mssql server based solution.

We have now many traditional options regarding the next move to tackle the load:

buy faster CPUs and IO
split some customers to seperate server
move db to cluster

All are either expensive in terms of licensing and hardware or time. So, I want to add another option by moving the whole system to a scalable solution that nosql engine cassandra promises.

Yet, I am not sure and not experienced with noSQL databases, so I need to understand the structure of "unstructured" data.

In our application, we basically store data entered by users in various ways as "key-value" lists. There is a parent table, that contains the head element (like an Order) and there is a child table with the key-value pairs comprising the contents of the order (like Order_Lines).

Business-wise, Order and OrderLines are a unit. But due to the RDBMS, they are stored in tables and must be joined all the time.

During operations, we sometimes choose to load only the top part, but most of the time, we load the head row + some KVPs to display some useful information.

For example, in an overview list, we show the head identifier + some values to in columns for each row.

UPDATE: We store forms of any kind. So, basically we store "documents". Nevertheless, we have to prepare and search through these forms by any value, sort etc. Data access control adds another layer of compexity on the database.

As you may guess, the amount and availability of certain KVPs varies from object to object. There is no valid possibility to create single tables for each kind of object as we would have to create thousands of tables for the different data combinations.

Would this kind of "Dictionary" like datasets be better stored in a noSQL database? And will we have performance benefits from this?
Would cassandra model these head+KVPs as one dataset? Looking at the cassandra webpage and some tutorials, I have the impression, that there is not so much of a difference between our RDBMS and cassandra in terms of data organisation - leaving us with the same huge amount of joins if you wanted to select 5 KVPs for a list for each row.

Enlightenment is welcome, also pointers to papers explaining the issues are ok.


texts: Completely uninstall Oracle server on Linux

texts:
I have searched lot in Google for how to uninstall Oracle on Linux, but I can't figure it out.

I think that I need to reinstall the server because I get a lot of errors when I try to startup the server.

Does any one know how to completely remove all data belong to an Oracle server?


texts: Getting recovered db files to work on rebuilt server

texts:
I've had to rebuild my server and have tried to copy the database files from the old drive to /var/lib/mysql/. I have restarted apache and run 'show databases', this list all the databases. I have entered one of these databases 'joomla' and run 'show tables' this shows all the tables I would expect.

However trying to run a query such as 'SELECT * from bsf_session' thows up and error:

ERROR 1146 (42S02): Table 'joomla.bsf_sessions' doesn't exist

checking the table I get:

mysql> check table bsf_seminar;
+--------------------+-------+----------+---------------------------------------------------------+
| Table              | Op    | Msg_type | Msg_text                                                |
+--------------------+-------+----------+---------------------------------------------------------+
| joomla.bsf_seminar | check | Error    | Can't find file: './joomla/bsf_seminar.frm' (errno: 13) |
| joomla.bsf_seminar | check | error    | Corrupt                                                 |
+--------------------+-------+----------+---------------------------------------------------------+
2 rows in set (0.00 sec)

When I copied the files over I change the permissions to match those of the mysql table, however I couldn't change ownership, the files currently look like this (ls -g):

-rw-rw---- 1 root   8842 2012-02-23 09:54 bsf_session.frm
-rw-rw---- 1 root 271012 2012-02-23 09:54 bsf_session.MYD
-rw-rw---- 1 root  25600 2012-02-23 09:54 bsf_session.MYI

I have tried to run

chown mysql bsf_session.frm

but this doesn't make a difference. I don't know if this is related to the main problem or not.

I'd really appreciate any help you could give...

I asked this on stackoverflow and was recommended to ask it here too...


texts: PostgreSQL procedural languages - differences between PL/pgSQL and SQL

texts:
Can anybody please summarize the differences between:

http://www.postgresql.org/docs/9.1/static/xfunc-sql.html

and

http://www.postgresql.org/docs/9.1/static/plpgsql.html

?

Main points:

conceptional differences
given a problem family, convenience of use
political issues


texts: Need help configuring Memcached to store values in MySQL Cluster

texts:
I am working on a Memcached implementation with MySQL Cluster 7.2.
I have installed and configured memcached with following parameters:

PORT="11211"
USER="mysql"
MAXCONN="1024"
CACHESIZE="1024"
OPTIONS=""

but Memcached is working irrespective to the MySQL engine
(i.e. when inserting to Memcached it is not storing into database)

Can any one help me out giving parameters so that it will access the ndb engine and store the values in both memcache and also in database?

I am not able to figure out the relation between the two.


texts: best way to avoid redundant aggregate functions and/or group by columns

texts:
Suppose I have two tables:

Foo:

id
baz

Bar:

id
foo_id
boom

So a Foo has many Bars.  I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set of Foos but I also want some properties from Foo.  The two most straightforward ways of doing this are ugly:

Method #1: Unnecessary Aggregate Functions

select
  foo.id,
  min(foo.baz) as baz,
  min(bar.boom) as min_boom
from
  foo
join
  bar on foo.id = bar.foo_id
group by
  foo.id;

Method #2: Unnecessary Group By Column

select
  foo.id,
  foo.baz,
  min(bar.boom) as min_boom
from
  foo
join
  bar on foo.id = bar.foo_id
group by
  foo.id,
  foo.baz;

This is not so bad when there's only one additional column from Foo besides "id", but if there are many columns that need to be included then the grouping becomes much less efficient.  A query like this gets around both these issues, but seems unwieldy:

select
  foo.id,
  foo.baz,
  x.min_boom
from
  foo
join
  (select
    foo_id,
    min(boom) as min_boom
  from
    bar
  group by
    foo_id) x on x.foo_id = foo.id;

Is there a better way?  Platform is Postgres if that matters.


texts: Can SQL Server prevent the pagefile from being moved?

texts:
On a production server of ours the page file has grown to 30gb+.  The Sys Admin has moved the Page File to a different drive, but for some reason the old one won't be removed.  He thinks that SQL Server may be the culprit.

Could SQL Server prevent the page file from being moved?


texts: Modeling Subclass (or Subtype) Constraints in an Entity-Relationship Diagram

texts:
Scenario description

Can someone help me out on how to model the situation that follows:

There is a superclass (or supertype) called PRODUCTS, which has the subclasses (or subtypes) named FOOD and SHOES.
There exists another superclass denominated CUSTOMERS, with the subclasses denominated SHOE STORE and GROCERY STORE.
I need to represent some rules that indicate that:

FOOD can only be delivered to GROCERY STORES.
SHOES can exclusively be distributed to SHOE STORES.

Tentative diagrams

I have tried the two diagrams shown bellow:

Diagrams considerations

Top diagram

I really do not like the duplication of (a) the same entity type, i.e., ORDERS, and (b) the relationships around it.

Bottom diagram

I am pretty sure this not the proper notation. Should I instead create the relationship on the superclass level and make a note that the restrictions will be implemented at the programming level?


texts:
I added pg_cron extension to my database (postgresql 9.6) to schedule a job.
I created a job to be executed every day at 3:30 am:

SELECT cron.schedule('30 3 * * *', $$create table  tbl_new as select  * from temp$$);

I can see that this job stored in the database:

mydb=# select * from cron.job;
 jobid |  schedule  |                        command                         | nodename  | nodeport |    database    | username
-------+------------+--------------------------------------------------------+-----------+----------+----------------+----------
     7 | 5 17 * * * | create table  tbl_new as select  * from temp | localhost |     5432 | mydb           | postgres
(1 row)

The problem is that the job is never executed and I there is no error in the error log.

Please advice how to fix this problem.


texts: How to safely resume a SQL Server Mirroring scheme that had a expired certificate?

texts:
We have a production database with a SQL Server 2012 Mirroring scheme with three machines: Principal, Mirror, Witness.

The mirroring was configured with a certificate that had only one year of expiry date. Now, the certificate is expired and the mirroring has failed. The Principal and Mirror machines are both with the database status Disconnected and the Mirror is also with "In Recovery" status.

To resume the mirror, I want to follow these tutorials: link1 and link2.

They basically suggest that I have to create new certificates and alter the Endpoint. My issue is how to safely resume the mirroring since the machines are disconnected for over a week. If I use ALTER DATABASE [mirrored_database_name] SET PARTNER RESUME, the Mirror machine will need A LOT of time to sync ALL information that was change in a week.

Will the Principal database be unavailable at this syncing or be with a bad performance? Or should I create a new mirroring session from the scratch? (i.e.: creating a new Full backup and Transaction Log backup for the Mirror machine)


texts: Can not find 'NT AUTHORITY\NETWORK SERVICE'. on Sql Server 2012

texts:
Hi How do i fix this Error?
when i run my MVC app i get this error
though my web.config is find. the error is on my DB call Sample:

Cannot open database "Sample" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Also cannot find ‘NT AUTHORITY\NETWORK SERVICE’. on my sql server management studio 2012 object explorer under Security > Logins to right click and set the Properties.

How can i add it, is this a win vista issue ?


texts:
What is the role of eager writer? How this process works internally in SQL server?
What is the major difference between eager writer and lazy writer/checkpoint? My understanding here is, that both eager and lazy writer are doing the same job - hardening dirty pages to disk. Is my understanding correct or am I missing any thing over here?
I've read at some blog that, "there is no chance for roll forward operation in bulk logged recovery model as the trasactions are minimally logged". If we take the case where a log backup consists of minimally logged transactions, how could this log restore can become successful without any roll forward operation?

I really confused with above questions. Please help me in learning more about eager writer.

Thank you.


texts: Permission denied errors during Nominatim import

texts:
I'm trying to import the OSM database into postgres using the default importer that Nominatim uses. After a couple of hours, i keep running into a snag. The error message is as follows:

index_placex: UPDATE failed: ERROR:  could not open file "base/16386/866592": Permission denied
CONTEXT:  SQL statement "DELETE from search_name_138 WHERE place_id = in_place_id"
PL/pgSQL function deletesearchname(integer,bigint) line 50 at SQL statement
PL/pgSQL function placex_update() line 75 at assignment
index_placex: UPDATE failed: ERROR:  could not open file "base/16386/866592": Permission denied
CONTEXT:  SQL statement "DELETE from search_name_138 WHERE place_id = in_place_id"
PL/pgSQL function deletesearchname(integer,bigint) line 50 at SQL statement
PL/pgSQL function placex_update() line 75 at assignment
index_placex: UPDATE failed: ERROR:  could not open file "base/16386/866592": Permission denied
CONTEXT:  SQL statement "DELETE from search_name_138 WHERE place_id = in_place_id"
PL/pgSQL function deletesearchname(integer,bigint) line 50 at SQL statement
PL/pgSQL function placex_update() line 75 at assignment
index_placex: UPDATE failed: ERROR:  could not open file "base/16386/866592": Permission denied
CONTEXT:  SQL statement "DELETE from search_name_138 WHERE place_id = in_place_id"
PL/pgSQL function deletesearchname(integer,bigint) line 50 at SQL statement
PL/pgSQL function placex_update() line 75 at assignment
index_placex: UPDATE failed: ERROR:  could not open file "base/16386/866592": Permission denied
CONTEXT:  SQL statement "DELETE from search_name_138 WHERE place_id = in_place_id"
PL/pgSQL function deletesearchname(integer,bigint) line 50 at SQL statement
PL/pgSQL function placex_update() line 75 at assignment
ERROR: Error executing external command: /srv/Nominatim/nominatim/nominatim -i -d nominatim -P 5432 -t 32 -r 26
Error executing external command: /srv/Nominatim/nominatim/nominatim -i -d nominatim -P 5432 -t 32 -r 26

Permissions on the data folder:

total 44
drwx------. 1 postgres postgres    50 Jan 11 07:56 base
drwx------. 1 postgres postgres   522 Jan 12 10:32 global
drwx------. 1 postgres postgres   184 Jan 12 07:26 pg_clog
-rw-------. 1 postgres postgres  4232 Jan 11 03:51 pg_hba.conf
-rw-------. 1 postgres postgres  1636 Jan 11 03:51 pg_ident.conf
drwx------. 1 postgres postgres   216 Jan 16 00:00 pg_log
drwx------. 1 postgres postgres    28 Jan 11 03:51 pg_multixact
drwx------. 1 postgres postgres     8 Jan 11 03:53 pg_notify
drwx------. 1 postgres postgres     0 Jan 11 03:51 pg_serial
drwx------. 1 postgres postgres     0 Jan 11 03:51 pg_snapshots
drwx------. 1 postgres postgres    22 Jan 16 04:19 pg_stat_tmp
drwx------. 1 postgres postgres     8 Jan 12 07:48 pg_subtrans
drwx------. 1 postgres postgres     0 Jan 11 03:51 pg_tblspc
drwx------. 1 postgres postgres     0 Jan 11 03:51 pg_twophase
-rw-------. 1 postgres postgres     4 Jan 11 03:51 PG_VERSION
drwx------. 1 postgres postgres  9724 Jan 12 13:08 pg_xlog
-rw-------. 1 postgres postgres 19828 Jan 11 03:53 postgresql.conf
-rw-------. 1 postgres postgres    57 Jan 11 03:53 postmaster.opts
-rw-------. 1 postgres postgres    92 Jan 11 03:53 postmaster.pid

Any idea on what the issue might be?

This was a fresh install, and it worked earlier on a installation of CentOS 7 server. The only thing that has really changed is that KDE is now installed so I can use X applications.


texts:
I have an Oracle 10g XE installation (exclusively used for development) which has accumulated so far 14 GB of files at ...\oraclexe\app\oracle\flash_recovery_area\XE\ARCHIVELOG. I do need that space and I'm tempted to just remove everything using Windows explorer but surely Oracle won't be amused.

Is it possible to configure Oracle XE to purge that stuff automatically as soon as it's no longer needed?


texts:
i got some problems on my approach to join a couple of tables by a date range. This are my tables:

RETURNS:

RETURN_DATE     RETURN_AMOUNT
02.05.14        2
02.05.14        3
02.05.14        1
02.05.14        7
02.05.14        5
02.05.14        2
02.05.14        1

and

ORDERS:

ORDER_DATE      ORDER_AMOUNT
02.05.14        50

As a first step, i created a date range and then tried to join these two tables to it. This is my SELECT statement:

SELECT
  DAT,
  RETURN_AMOUNT,
  ORDER_AMOUNT

  FROM
    (
      SELECT
      TO_DATE('02.05.2014','dd.mm.yyyy')+ROWNUM-1 AS DAT

      FROM
      all_objects

      WHERE
      ROWNUM <= (TO_DATE('02.05.2014','dd.mm.yyyy')-TO_DATE('02.05.2014','dd.mm.yyyy')+1)
    ) LEFT JOIN RETURNS ON RETURN_DATE = DAT
    LEFT JOIN ORDERS ON ORDER_DATE = DAT

    ORDER BY DAT;

This was the Result:

RESULT:

DAT         RETURN_AMOUNT   ORDER_AMOUNT
02.05.14    1               50
02.05.14    2               50
02.05.14    5               50
02.05.14    2               50
02.05.14    1               50
02.05.14    3               50
02.05.14    7               50

Thats a problem because when i GROUP BY DAT and then sum up the amounts, i got a wrong ORDER_AMOUNT for this day.

How can i achive, that ORDER_AMOUNT is only resulted once?

Thanks,
Stefan.


texts: Include UDFs in replication publication on SQL Server 2012

texts:
When trying to replicate UDFs via snapshot replication on a working database I got an error. So I created a simpler database with only a single UDF to test that I could replicate UDFs, because I've been reading that there might be bugs that depend on how you write the SQL I created a simpler test.

I got the same error with this function, so should I assume that I can't replicate UDFs? I mean, I'm given an option to do exactly this. But even the simplest example fails. Am I doing anything wrong? The error is from the snapshot agent on the publisher's side.

This is the function:

CREATE FUNCTION test1
(
)
RETURNS INT
AS
BEGIN
    RETURN (
        SELECT 1
    )
END
GO

The stacktrace is:

Source: Microsoft.SqlServer.Smo
Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[], Boolean)
Message: Script failed for UserDefinedFunction 'dbo.test1'.
Stack:    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
   at Microsoft.SqlServer.Management.Smo.Scripter.EnumScriptWithList(SqlSmoObject[] objects)
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.CreateMirrorCopy(String mirrorCopyName)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.FetchBaseDependenciesWithMirrorCopyTransaction(SqlConnection connection)
   at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.FetchBaseDependenciesWithMirrorCopy()
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.FunctionDependencyNode.get_BaseDependencies()
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.GetEnumerator()
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.DepthFirstSearchVisit(IDependencyNode dependencyNode)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.DepthFirstSearchVisit(IDependencyNode dependencyNode)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.PerformArticleDependenciesAnalysis()
   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoReplicationDependenciesAnalysis(Scripter scripter, SqlSmoObject[] smoObjectArray, IDictionary urnKeyArticleLookupTable)
   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
   at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
   at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0

I'm trying to publish on SQL Server 2012 to a subscriber that is SQL Server 2016. The Distributor is also SQL Server 2016.

I was able to successfully run a snapshot of this UDF on a publication hosted on SQL Server 2016. So I think that it's actually SQL Server 2012 that is not allowing this, assuming that the snapshot agent runs on the publisher.

I've tried with and without the AS keyword. The value of @type in sp_addarticle is 128.


texts:
I have SQL Server with 2 databases.

I want to compare them and see which tables are on the first database and not on the second database.

Only the names of tables matter, I don't care about the values in those tables.

How can I do it?


texts: Is there any reason to run VACUUM followed immediately by VACUUM FULL?

texts:
Because VACUUM FULL completely rewrites a table into a more compact form, it seems obvious that there is never any reason to run VACUUM FULL and then run VACUUM, because the second VACUUM wouldn't have any cleanup to do.

But could the reverse -- running VACUUM and then VACUUM FULL -- ever make sense?

One hypothesis might be that VACUUM makes the subsequent VACUUM FULL run faster. If this hypothesis is true, and if you are primarily concerned with limiting the time period of the exclusive lock that VACUUM FULL needs, then it could make sense to run the two commands sequentially.

Note that I'm guessing that this hypothesis is false, given that the VACUUM FULL to my understanding rewrites the entire table. But I'm interested in the general question.

I'm running 9.3.3 in case there are version-specific wrinkles.


texts: Can I somehow Improve the relationships between tables in the following sample?

texts:
I am currently developing a database for a school management system... There are tables for GRADE, SECTION and SHIFT...

GRADE
====
id PRIMARY KEY

SECTION
====
id PRIMARY KEY

SHIFT
====
id PRIMARY KEY

And yet another table which comprises such data called GROUP through foreign keys which additionally has a primary key...

GROUP
====
id PRIMARY KEY
grade_id FOREIGN KEY REFERENCES GRADE(id)
section_id FOREIGN KEY REFERENCES SECTION(id)
shift_id FOREIGN KEY REFERENCES SHIFT(id)

Now where I have an desing stigma and would like advice from a designer with more experience... A table called SUBJECT which comprises a foreign key referencing GRADE and a primary key (A certain grade always has the same subjects)...

SUBJECT
====
id PRIMARY KEY
grade_id FOREIGN KEY REFERENCES GRADE(id)

Now I have yet another table called CLASS referencing SUBJECT, PROFESSOR and GROUP through a foreign key too in addition to its primary key...

CLASS
====
id PRIMARY KEY
subject_id FOREIGN KEY REFERENCES SUBJECT(id)
professor_id FOREIGN KEY REFERENCES PROFESSOR(id)
group_id FOREIGN KEY REFERENCES GROUP(id)

I wonder if it is ok that if drilling down one level on CLASS table references makes possible to access presumably the same GRADE (Through SUBJECT and GROUP) in two different ways. I don't include the code for the tables because I do think it would make this question somewhat long and of course there are more tables. My database is already implemented and works  with the proper application but I would like to see if design could be improved. Thanks in advance. *Note : I know I am using reserved words for table names in my case elaboration, but only for illustrative purposes, also did not include all the tables... Too I have an STUDENT table which as a reference to a GROUP, that is why I included a primary key on such table. I hope now my question is better explained, I am new to this kind of site.


texts: WITH statement does not persist outside of query

texts:
I run a select to check what records I should register in another table.

Each record returned in the query I should give an insert in my table campanha_cliente_chamada.

WITH psql as ( /* stuff */ )
SELECT * FROM psql;

WITH disca ( /* reads from psql */ )
INSERT INTO callcenter.campanha_cliente_chamada ( columns )
SELECT col1, col2, col3
FROM   disca;

This is the error I'm getting.

Error : relation psql does not exist LINE 11: ( SELECT
id_camapanha_cliente FROM psql),(SELECT c...
                          ^


texts: SQL Server db user mapped to windows login not in SQL Server logins

texts:
I am on a SQL Server 2008 where I login with DOMAIN\USER_A

On my database I can see this login is mapped to the user dbo of that database.
What I don't understand is that DOMAIN\USER_A is nowhere to be found in the SQL Servers Security Logins.

CURRENT_USER reports dbo
SYSTEM_USER reports DOMAIN\USER_A as do SUSER_NAME

I can't find DOMAIN\USER_A in the sys.syslogins? What is going on? Can a database user have its own login circumventing the SQL Server?


texts:
I have a many-to-many relationship table (bridge table) (TicketId, CiiD) and I want to transform it into the following model : (IdBridge, Ciid),
actually my problem is how to remove the duplicates groups !
here is an exemple code for source table (TicketCis):
TicketCis
CREATE TABLE TicketCIs
(
      TicketId varchar(5)
      ,CIId varchar(5)
);

INSERT INTO TicketCis (TicketId, CiiD)
VALUES
('A','AA'),
('A','AB'),
('B','AA'),
('C','AC'),
('C','AD'),
('D','AA'),
('E','AC'),
('E','AD'),
('F','AF');

so I proceed to grouping by TicketId with adding ID_GROUP with this query:
SELECT
DENSE_RANK() OVER(ORDER BY TicketId) AS ID_GROUP,
     CIId
FROM TicketCIs

The output I'm getting:
╔══════════╦═══════╗
║ ID_GROUP ║ CIId  ║
╠══════════╬═══════╣
║    1     ║  AA   ║
║    1     ║  AB   ║
║    2     ║  AA   ║
║    3     ║  AC   ║
║    3     ║  AD   ║
║    4     ║  AA   ║
║    5     ║  AC   ║
║    5     ║  AD   ║
║    7     ║  AF   ║
╚══════════╩═══════╝

The output I'd like to get:
╔══════════╦═══════╗
║ ID_GROUP ║ CIId  ║
╠══════════╬═══════╣
║    1     ║  AA   ║
║    1     ║  AB   ║
║    2     ║  AA   ║
║    3     ║  AC   ║
║    3     ║  AD   ║
║    6     ║  AF   ║
╚══════════╩═══════╝

In the output (what I'm looking for) as you can see, the duplicate group_ids (4,5) are removed.
I hope it's clearer now :)
Thank you.


texts: Query to get the "Item -- (1,n) --> Attributes" relation table?

texts:
given is the following database scheme and I am wondering how to build the appropriate query on a Postgres system.

It is defined that Items can have multiple (1 -> n) Attributes which relation is specified within the Relation table.
This relation table also stores a value related to this connection.
Also  items and attributes can be added or removed any time, but the relation table will always only contain valid item and attribute ids.

Item
----
id (primary key), name

Attribute
---------
id (primary key), name

Item_Attribute_Relation
-----------------------
Item.id, Attribute.id, value

Now my task is to return a (sparse) table where items are shown as rows and where each attribute is given as a column.
As an example following table shows the Attributes (A, B, C, and D) and the Items (a, b, c, and d).
The entries relate to the value in the Item_Attribute_Relation.
It is possible that an attribute (column) is NULL for every item and that an item has no attributes at all.

   | A | B | C | D |
--------------------
 a | x | x | y |   |
 b | x | z |   |   |
 c | w |   |   |   |
 d |   |   |   |   |

Unfortunately my SQL knowledge is rusty and Postgres does not support PIVOT to my knowledge.
Also I am not able (probably due to missing knowledge) to dynamically generate the columns when using the Postgres crosstab() function.
My gut tells me that this is a common problem. Therefore help by providing the correct wording or by posting links to this problem explanations is also welcome.

Thanks!


texts: postgres 9.1: role public does not exist

texts:
I understand public is not a typical role in postgres:

dav-gis=# \duS public
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------

But why can I revoke all privileges on a schema from public, but not assign another role to public? E.g.,

db=# REVOKE ALL PRIVILEGES ON SCHEMA "eia" FROM "public";
REVOKE
db=# GRANT "eia-ro" TO "public";
ERROR:  role "public" does not exist

I'd like to only give public users read access through the read-only role, but perhaps this is just out of sync with how public access works in Postgres.


texts:
I'm missing something while trying to make my stored procedure use EXECUTE AS. The stored procedure is reading data from source_db, aggregates it and stores result in target_db.

The sp itself is in target_db. I have a dedicated login and map it to users in both source_db and target_db for sp's owner (so there is a user app_agent in source_db and in target_db for login app_agent).

If I log in as app_agent, and execute

EXEC target_db.app_agent_schema.import_data

everything works fine. But if I change

ALTER PROCEDURE app_agent_schema.import_data WITH EXECUTE AS OWNER` (or `AS SELF`)

and try executing it, it throws

  The server principal "app_agent" is not able to access the database
  "source_db" under the current security context.

I'm using SQL Server 2008.

Could someone point out my error?

Thanks

Update
After doing some research, I found that ALTER DATABASE target_db SET TRUSTWORTHY ON solves the problem, but that doesn't seem as the right solution to me...


texts: Can I recover an overwritten incident trace file?

texts: I was looking at incident trace file in oracle database but some how that file got overwritten. As of now I have ecrep_ora_26467_i49966.trm but trhe corresponding .trc file is lost. is it possible to recreate the lost trace file


texts:
DETAIL:  Key (proname, proargtypes, pronamespace)=(rand, , 2200) already exists.: CREATE OR REPLACE FUNCTION "rand"() RETURNS float AS

How on earth is that even possible? How can CREATE OR REPLACE fail with already exists? This is in a docker container, completely pristine environment and this is the only that code runs. Yes, it might run repeatedly but that's why the OR REPLACEis there. I do not get it. Here's the full command:

  if (!$connection->query("SELECT COUNT(*) FROM pg_proc WHERE proname = 'rand'")->fetchField()) {
    $connection->query('CREATE OR REPLACE FUNCTION "rand"() RETURNS float AS
      \'SELECT random();\'
      LANGUAGE \'sql\'');
  }


texts: In-Place SSRS 2014 to 2016 Upgrade Failed. Cannot start new SSRS 2016 instance

texts:
We have a SQL server 2014 and SSRS 2014 instance set up on two different 2012 R2 servers. This has been our setup for years, and everything has worked fine.

We did an in place upgrade of the SQL server from 2014 to 2016 with no issues, but when I tried an in place upgrade of SSRS 2014 to 2016, it completed the setup with errors.

The error we initially recieved on setup is “Index was out of Range. Must be non-negative and less than the size of the collection. Parameter name: index”.
Now we are unable to start the SSRS instance. These errors appear to be an authentication issue.

When I try starting the instance, I receive the following errors:

  “Starting report server “ReportServer$NVSLDS2T080SSRS on NVSLDS-2T080.
  The Task Failed.

  “System.InvalidOperationException: Cannot start service
  ReportServer$NVSLDS2T080SSRS on computer 'NVSLDS-2T080'. --->
  System.ComponentModel.Win32Exception: Access is denied    --- End of
  inner exception stack trace ---    at
  System.ServiceProcess.ServiceController.Start(String[] args)    at
  System.ServiceProcess.ServiceController.Start()    at
  ReportServicesConfigUI.Panels.ConfigurationPanelWithErrors.StartOrStopServiceTask(Boolean
  start, String serviceName)”

The Event  Viewer is also saying:

Event ID 7000 “The SQL Server Reporting Services (NVSLDS2T080SSRS)
service failed to start due to the following error: Access is
denied.”
Event ID 64, “Certificate for local system with Thumbprint
89 91 b4 e0 3a 96 cf ea a2 75 8f aa b6 41 e4 e5 c2 d3 69 aa is about
to expire or already expired.”
Event ID 16 “The access history in
hive \??\C:\Windows\System32\SMI\Store\Machine\SCHEMA.DAT was cleared
updating 0 keys and creating 0 modified pages.”

I haven't found many clear articles on the issue.

The closest I have gotten is an article about the KB 2677070 update, which I don't believe to be the issue.

https://support.microsoft.com/en-us/help/2745448/you-cannot-start-sql-server-reporting-services-after-you-apply-the-upd

An issue with ASP.net:

https://support.microsoft.com/en-us/help/332215/fix-system-argumentoutofrangeexception-index-was-out-of-range-error-oc.

Please advise.


texts: How to identify a process that has been cancelled?

texts:
How can you identify a process that has been cancelled?

for example:

I was running this dynamic sql - via a linked server:

declare @sql nvarchar(max)
declare @i int
declare @z int
DECLARE @vCrlf CHAR(2);

SELECT  @vCrlf = CHAR(13)+CHAR(10)
       ,@Z = COUNT(*)
       ,@I = 1
   FROM #T1

    SELECT @sql = '
SET IDENTITY_INSERT bocss2.DBO.tblBCataloguePriceSetItem  ON;
INSERT INTO bocss2.dbo.tblBCataloguePriceSetItem(
            [lngCataloguePriceSetItemID]
           ,[lngCataloguePriceSetID]
           ,[strItemNo]
           ,[strTier1]
           ,[strTier2]
           ,[strTier3]
           ,[strTier4]
           ,[strTier5]
           ,[strTier6]
           ,[sintNameStructureID]
           ,[strCurrencyCode]
           ,[decPrice]
           ,[decWasPrice]
           ,[strBCCurrencyCode]
           ,[decBCPrice]
           ,[decBCWasPrice])
SELECT
            [lngCataloguePriceSetItemID]
           ,[lngCataloguePriceSetID]
           ,[strItemNo]
           ,[strTier1]
           ,[strTier2]
           ,[strTier3]
           ,[strTier4]
           ,[strTier5]
           ,[strTier6]
           ,[sintNameStructureID]
           ,[strCurrencyCode]
           ,[decPrice]
           ,[decWasPrice]
           ,[strBCCurrencyCode]
           ,[decBCPrice]
           ,[decBCWasPrice]
FROM TABLEBACKUPS.DBO._mm_20140324_tblBCataloguePriceSetItem
ORDER BY [lngCataloguePriceSetItemID]'

    BEGIN TRY
        --PRINT (@SQL)
        EXECUTE [SQLBOCSSLON1.DEV.BODEN.LOCAL].[bocss2].[dbo].[sp_executesql] @SQL
    END TRY
    BEGIN CATCH
      PRINT ERROR_MESSAGE()
    END CATCH

via a LINKED server,
[SQLBOCSSLON1.DEV.BODEN.LOCAL]

when I realised the OLEDB WAIT_TYPE going to the roof, so I cancelled this query,
copied and paste the script in the REAL server and run it from there.

This is the OLEDB WAIT TYPE:

This is the picture of cancelling the query via linked server:

These are the 2 processes that are currently running on the server.
In yellow is the one that I have cancelled.
Wait_type is SOS_SCHEDULER_YIELD, but I cannot see any indication (at least not in my query) - that shows me that this is a process that has been cancelled.

How can I achieve that?

The exact query that I am using is here:

how to find the T-SQL of a sleeping session that is blocking some other process?

and here:

how to get the information about sleeping processes that are causing blocking?


texts: I have a dump of a postgis layer (layer.dump), which I am trying to add to my heroku database (mydatabase). The postgis layer is stored on S3 (https://s3.amazonaws.com/layer.dump). I would like to add the layer to to the heroku database and previously used heroku pgbackups:restore DATABASE 'https://s3.amazonaws.com/layer.dump'. However, the new heroku pg:backups restore 'https://s3.amazonaws.com/layer.dump' DATABASE deletes all data from the target database before restoring the backup (https://devcenter.heroku.com/articles/heroku-postgres-backups). Is there still a way to only restore a single table and leave the remaining tables in the database untouched?


texts: Update the other columns which are for to have minimum of other 2 columns , difference between 2 columns , and others

texts:
I am working on data in oracle 11g as below :

subscriber_id |  count_s  |  count_t |   min_count   |  diff    | negative_diff   |  positive_diff

137606401        3          3              3        0          0                  0
102842273        5          4              4        1          0                  1
116638321        14        14             14        0          0                  0
98353910         53        53             53        0          0                  0
102869643        18        20             18       -2          2                  0
107144617        21        21             21        0          0                  0

Can you please help me with a logic to update min_count , diff , negative_diff , positive_diff considering we have values for subcriber_id , count_s and count_t ?

here , min_count should be minimum of (count_s , count_t) for that specific subscriber_id.

diff should be difference between count_s and count_t , which can be easily attained with [count_s - count_t]

negative_diff should be updated for subscribers which has -ve diff values otherwise value for this should be 0 [-ve sign should not be considered while updating]

similarly , positive_diff should be updated for subscirbers which has +ve diff values otherwise value shouild be 0

please suggest the with sql code snippet to be used to update min_count , negative_diff and positive_diff.

Note: Working in SQL


texts:
For the following table I want to loop update something like 1000 rows at a time. I have accidentally from ruby on rails application changed the updated_at timestamp so now old transactions shows up among the new ones.

create table bet_settle_txs (
  id int not null,
  constraint id primary key (id)
);
insert into bet_settle_txs (id)
  select id
  from bet_settle_transactions
  where external_data ? 'jackpot_contributions' and updated_at != created_at and created_at < date 'yesterday';

Now to use these ids to reset the timestamp I have already tried to update all the transactions with a single commit but unfortunately that locks the entire table. I can't have that because then I would have to take the application offline so I was thinking of looping through all the records and updating them 1 and 1 if need be but preferably something like a 1000 begin+commit.

with txs as (
    select id
    from bet_settle_txs
    order by id
    limit 1000
    offset 0
) update bet_settle_transactions
set updated_at = created_at
from bet_settle_txs b
where b.id = bet_settle_transactions.id;

Anyone have any idea how to perform this in a loop without locking down the entire table. These transactions is never changed so any type of read could be allowed. Inserts should also be allowed really since this would be the only update running I could have as little locking as possible. It seems the commit of a million updated rows is to be avoided however. At least any way I tried this fails. When I changed the rows from ruby on rails I updated them 1 by 1 changing a column value from a string variant of a ruby hash to a single value. This was needed but took a couple of days to complete so I would like to handle it at the database layer directly to avoid using connections now that the need is simple.


texts: Enable slow_query_log tries to write to slow_log.CSV

texts:
I'm running MySQL 5.5

I wanted to truncate the slow log file, so I did:

SET GLOBAL slow_query_log = 0;

then truncated the file in filesystem.

But, when I enable it back it fails:

SET GLOBAL slow_query_log = 1;
ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)

Although the slow_query_log_file set explicitly to another path.

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
+------------------------------------+---------------------------------+
| Variable_name                      | Value                           |
+------------------------------------+---------------------------------+
| slow_query_log                     | OFF                             |
| slow_query_log_file                | /home/mysql_data/jobs1-slow.log |
| slow_query_log_timestamp_always    | OFF                             |
| slow_query_log_timestamp_precision | second                          |
| slow_query_log_use_global_control  |                                 |
+------------------------------------+---------------------------------+

What is wrong? I want MySQL log to the specified file. Plain text, not CSV.

SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

Before I stopped it logging was fine.


texts: Oracke RMAN: Recover DB when backup folder has changed

texts:
Scenario:

RMAN backp where made in this folder: /xxx/yyyy
Database was accidentally erased.
For weird reason, backup pieces are no longer in /xxx/yyyy, they are now in /aaa/bbb
I do an startup nomount, restore controlfile from new location /aaa/bbb
I put the DB in mount mode.
When I try to restore the BD it fails because backup pieces and not found (restored controlfile says backup pieces should be in /xxx/yyyy )
I try so set CHANNEL DEVICE TYPE DISK FORMAT to the new location (/aaaa/bbb) hoping that RMAN is smart enough to find the backup pieces, but that cannot be done with a backup controlfile.

How can I restore in this scenario where I do have all my backups in another folder than originally backed up to?


texts: Large NOT IN Statement vs Creating/Populating Temp Table

texts: I have a large daily data feed which can contain up to around 5,000 ID's. I need to delete all pre-existing records in the DB that are not referenced in the new feed. Should I create a temporary table for these 5,000 ID's, and run a LEFT JOIN IF NULL DELETE statement? Or, is it all the same if I just pass the ID's over and run a DELETE IF NOT IN statement. My assumption is that MySQL would do the work of creating the temp table for me when using IN statements.


texts: In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

texts:
As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature.

I would just like to know what the specific differences will be between Always Encrypted & the currently available Transparent Data Encryption in SQL Server 2016 so that we can make the correct decision for future projects.


texts: Are UDF used by a stored procedure optimised/merged in the SP itself?

texts:
The issue has sent me crazy for a few days, I may have found my answer, but I need a confirmation and to check for an eventual easy solution.

When a stored procedure uses some user defined functions, are the UDF plans merged in the main plan and everything get optimised or are they called directly every time?

And suppose it doesn't happen, is there a way to force SQL Server to do it for a specific SP?


texts: Single query sending and retreiving duplicate data on MS SQL Server?

texts:
Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes (roughly).

On further investigation (after several days of eliminating the "obvious") it would appear that, sometimes queries are being sent by the client (Sql Server Management studio) multiple times and being received multiple times.

Ie "Bytes sent from client" will double or even treble, and "bytes received from server" will do the same. Obviously similar pattern of increase in"Client processing time" and "total execution time".

This is even happening with a SELECT * FROM table where the table on has 3 rows of data!

And when one query goes slow they all go slow. Doesn't matter how simple the query / results set, or which "client" is accessing (same in an ADO.NET based console app / MVC web app).

Any suggestions would be greatly appreciated...

EDIT

The "multiple queries" thing cannot be recreated. No idea why it happened, seems to have been an anomaly which temporarily lead us down the wrong alleyway
@Brent Ozar - thank you for your suggestion - we have run these types of tests already with, unfortunately, no useful results!
@jco360 - thanks for your suggestions. Indexing is a non-starter as performance is slow across the whole database (one slow = all slow then one quick = all quick). I am the developer and I'm seeing the same issue when running simple queries from SSMS. Finally, the hardware issue may be the answer, although 64GB Ram and many processors should suffice for a server which is barely being used, there may be some hard drive corruption or similar?

It's also not network related as tests have been run from the box itself.


texts:
I am looking how to strong security on my Postgres database implementing fail2ban

But checking on logs i can't see the ip adress of failed connection

2020-03-30 11:19:50.929 CEST [21167] postgres@test_ecofil FATAL:  password authentication failed for user "postgres"
2020-03-30 11:19:50.929 CEST [21167] postgres@test_ecofil DETAIL:  Password does not match for user "postgres".

checking for something  like this

Mar 30 11:29:19 xxxx sshd[13605]: Invalid user xxxxx from xx.xxx.xx.x

There is a way to do that? thanks


texts:
I have backed up a database (full backup) and was attempting to restore as a new database (different name). However when attempting to restore it I get the following error:

  The transaction log for database 'L-Test' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Not really sure why that's happening. The size of the database is only 37.50MB and the logfile is set to auto grow as well.


texts: Joining two tables with different fields through a link-table

texts:
Im a complete novice to Access (2013).

Im doing some History research and thought it would be easier to use Access. I have created two table so far.

People, Details info about them
Events (historical).

Both have different fields.

Can I create a third table that collates all the info from the first two tables?

Therefore, I can 'organise' the dates to display in order. (may need to look at DOB and Event Date to merge under name field name to do this?)

I've uploaded the file for you to see if it helps. I tried to search for answers before posting but nothing along the lines im looking at was thrown up. Also have no coding experience, so this is completely new to me.

Thank you in advance

Link to Database File


texts: Postgres FOREACH array as composite type

texts:
I'm trying to loop over an array with two types as a composite type, but it seems that I can not use the composite type as the variable array to loop over it. What I'm trying to accomplish:

create type my_item as (
    field_1        timestamp,
    field_2        numeric
);

CREATE OR REPLACE FUNCTION unnest_2d_1d_my(ANYARRAY)
  RETURNS TABLE (ts timestamp, value numeric) AS
$func$
DECLARE
  a my_item[];
  timestamp timestamp[];
  value1 numeric[];
BEGIN
   FOREACH a SLICE 1 IN ARRAY $1 LOOP
      timestamp = array_append(timestamp, a.field_1);
      value1 = array_append(value1,a.field_2);

   END LOOP;
   RETURN QUERY select unnest(timestamp), unnest(value1);
END
$func$
LANGUAGE plpgsql IMMUTABLE;

Composite array types -> (timestamp, numeric):

CREATE TABLE test AS(SELECT * FROM unnest_2d_1d_my(array[['2018-01-01',2],['2018-01-02',5]]));

I'm expecting as an output:

ts           | value
'2018-01-01' | 2
'2018-01-02' | 5

but currently I only obtain the following error:

invalid input syntax for integer: "2018-01-01"

I wonder how can I work with a multi-type array to achieve the mentioned output.


texts: Huge MySQL slave replication lag (days) at almost idle servers. Slave is mostly 'Waiting for dependent transaction to commit'

texts:
I have a strange situation with MySQL replication lag happening on relatively idle servers.

Slave shows no symptoms of IO saturation (according to top, iotop or iostat), no symptoms of CPU saturation. If I do SHOW SLAVE STATUS at regular intervals, it seems, that slave spends 99% of it's time at two events:

Waiting for slave workers to process their queues
Waiting for dependent transaction to commit (happening most often).

The problem is that the database is about 1Tb in size and it's not easy experimenting with it. Both master and slave are Xeon E5 with SSD RAID10 storage. Investigation showed no lagging of slave IO thread. Everything looks like slave SQL thread is constantly waiting for some event to complete before replaying next transaction from log.

Replication type used is row-based. We tried to switch to slave_parallel_type         = LOGICAL_CLOCK and set slave_parallel_workers = 24 but with no success. We see no server resource over-utilization. All metrics show that the server is mostly idle. log_slow_slave_statements shows nothing. innodb_flush_log_at_trx_commit = 2 to prevent fsyncs() on small transactions (not that it was necessary because we are nowhere near iops limit but we wanted to try something).

Putting additional load to server does not affect replication lag.

Database being replicated contains about 95% of InnoDB tables and some TokuDB tables.

This situation started about several months ago at a time point none can remember. Despite of the fact, that it is a visible point in Grafana for us, we can't remember or find something in logs that could bring us closer to the problem cause.

MySQL server version is 5.7 by Percona.


texts: Redefine Transaction log size restricion fails

texts:
I am trying to set the transaction log's max size restriction to UNRESTRICTED using SSMS using the following dialogue:

The actual setting is set to 2.097.152 MB - a nonsense value that exceeds the capacity of the drive.

I have tried to set it to some other value e.g. 85 GB (something between actual TL size and max drive capacity) as well as to unrestricted.

Neither of those settings is getting persisted. The original restriction setting returns again and again.
Why is it impossible to change?

(Note: All DBs are running in Compat Lvl 80)

I ran the command:

ALTER DATABASE myDB
MODIFY FILE ( NAME = N'myDB_Log', MAXSIZE = UNLIMITED)

against several servers. Some of them afterwards show MAXSIZE=-1. Others show max_size=268435456 in sys.database_files. The ones having -1 also show the correct radio button selected in the dialogue.


texts: MySQL - Multiple index with join and where query

texts:
I have the following table:

CREATE TABLE `table1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `sha256` BINARY(32) NOT NULL,
    `state` TINYINT(4) NOT NULL DEFAULT '0',
    `is_ignored` BIT(1) NOT NULL DEFAULT b'0',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `sha256` (`sha256`)
);

And I have the following query:

SELECT 1
FROM table2
JOIN table1 ON table1.sha256 = table2.sha256
WHERE table1.is_ignored = FALSE AND table1.state = 1

table2 has 20m rows indexed by sha256.

table1 has 5m rows.

I am considering how to index table1 (not many inserts).

Will creating an index of sha256_state_is_ignored index increase performance for this query comparing to 3 separate indexes on each the columns?(sha256 index is very dominant here)
Will it change if I make the combined index as state_sha256_is_ignored instead of sha256_state_is_ignored.(thats because sha256 already has an index from his unique index)

EDIT:

This is my final table:

CREATE TABLE IF NOT EXISTS maint.files_state_global (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `sha256` BINARY(32) NOT NULL,
    `source` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0-default(cloud), 1-dynamic compsInOrg and vt',
    `state` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0-unknown, 1 - white, 2- black',
    `is_ignored` BIT(1) NOT NULL DEFAULT b'0',
    `date_in` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_seen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `sha256` (`sha256`),
    INDEX `source` (`source`),
    INDEX `is_ignored` (`is_ignored`),
    INDEX `last_seen` (`last_seen`),
    INDEX `state_is_ignored_sha256` (`state`, `is_ignored`, `sha256`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

I must have a single index on source/last_seen/sha256.
sha256 is unique.
state and is_ignored must have index and will always come together(90% of the cases with sha256 as well).

So other then changing the sha256 to PK and removing id, are my indexes optimized?


texts: Using Select Statement to get column name and table name from the same table

texts:
I have a table containing 2 Columns :
Column1 (table_name) and column2 (column_name)
like this :

     (Column 1)    (Column 2)

    |Column_Name | Table_Name |
    -------------|------------|
    |Address     |  TBL_Person|
    |Email       |  TBL_User  |

It has about 300 records

I want to have a select statement which returns these 2 columns and a column which gets the maximum length of the column of the table

like this :

    |Column_Name | Table_Name | Max_Length |
    -------------|------------|------------|
    |Address     |  TBL_Person| 1020       |
    |Email       |  TBL_User  | 50         |

I can make a string calculating max_length, but I don't know how to use it in the select statement.

I tried using a function but in a function I had to execute SQL which is not allowed in a function because you can not use a sp in a function.

COL_LENGTH returns the default length of the column, not the maximum length.
All the columns I have are varchar(max) or nvarchar(max).


texts:
This morning when I logged into SQL Server Management Studio (v 10.0.16) I clicked on the databases node and saw all by db's (they are hosted remotely) as I have done for the last 3 years. This evening, when I click on the databases node I see NOTHING - except the system databases.

According to my hosting company, this is an issue with my local permissions. I have searched everywhere and found nothing to help me. I'm now desperate and and help would be massively appreciated.


texts:
I have a database where logging happens and is processed in a data-warehouse styled environment. This means there are staging cycles where the data comes in and then is processed. For a reporting system, some log entries for certain parameters need to read, although it can happen that there are no entries in the staging cycle. Therefore there is a table which contains a value for every parameter - the last one which has been read. After the processing finished, the table is updated via

UPDATE jobcontrol.actual_values a
JOIN jobcontrol.processing p
SET a.value = p.value
WHERE p.t_ns = (select max(p.t_ns) from jobcontrol.processing p WHERE a.para_id = p.para_id)
AND a.para_id = p.para_id;

This seems improvable to me, although my try to simplify it via a real join didn't quite work.

Do you have any suggestions on how this could work more easily (and faster)?

-> Explain Statement (This was run on a test table with 36 rows for 8 different parameters)

+----+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                  | rows | Extra       |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
|  1 | PRIMARY            | p     | ALL    | PRIMARY       | NULL    | NULL    | NULL                 |   36 | NULL        |
|  1 | PRIMARY            | a     | eq_ref | PRIMARY       | PRIMARY | 4       | jobcontrol.p.para_id |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | p     | ref    | PRIMARY       | PRIMARY | 4       | jobcontrol.a.para_id |    2 | Using index |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+

-> SHOW CREATE TABLE

CREATE TABLE `processing` (
  `para_id` int(10) unsigned NOT NULL DEFAULT '0',
  `t_ns` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id_inst` smallint(6) NOT NULL DEFAULT '1',
  `value` varchar(255) NOT NULL DEFAULT '',
  `isanchor` tinyint(4) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`para_id`,`t_ns`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

MySQL Version is 5.6


texts: Few Insert queries rollbacked due to system crash

texts:
Well, this problem occured to me first time. Yesterday, one of our mysql system server crashed and when it restarted last few inserts were completely rollbacked.

I am sure that they were proper inserts as we fetch results after inserts to print a PDF and PDF was generated properly. and system was crashed after 2-3 minutes of that INSERT operation.

System is PHP - MySQL based and we are using MyISAM as storage engine for our tables with autocommit ON

This is unique as earlier, after a system crash we normally had crashed tables but nothing like rollback operation. all the table are intact and operational after system crash.

Can anyone explain to me how is that possible if autocommit is ON. Thanks in advance


texts: TSQL - How to strip out debit card numbers from a string of text?

texts:
We have a table that stores a large string of text and within the text is a debit card number, the string of text is copy and pasted from an email the customer has sent in.

How can I search through the 7000+ records and identify/replace the card numbers from 0000-0000-0000-0000 to XXXX-XXXX-XXXX-0000?


texts: Unknown _TransactionIndex table in Azure SQL Database

texts:
Just noticed today that there is a random table in my Azure SQL Database that is named "_TransactionIndex_{guid}"

It has 1 column, Id, that contains 8 GUIDs.

Couldn't find any information about this table - what is it?  Does it mean that I have an issue with transactions within my application?