texts: MySQL: memory requirements for django app with 1 big table (460M rows)

texts:
I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows).

CREATE TABLE `genescorrelation` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `correlation` double NOT NULL,
 `gene1_id` int(10) unsigned NOT NULL,
 `gene2_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `c_gene1_id_6b1d81605661118_fk_genes_gene_entrez`   (`gene1_id`),
KEY `gene2_gene1_idx1` (`gene2_id`,`gene1_id`))
ENGINE=InnoDB AUTO_INCREMENT=92345005 DEFAULT CHARSET=latin1

The query that user will run (up to 10 genes):

 SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation
 WHERE gene2_id IN (176829, 176519, 176230)
 GROUP BY gene1_id ORDER BY NULL

Currently I have imported around 89M rows and the query is slow. It would take around 30-40 seconds for 3 or 4 genes.

I think my VM is too weak because it was a default set up (for smaller project/database). Currently it has just 2GB RAM and 36GB space.

I need to ask to set up prod machine.

What are the memory/space suggestion for this kind of set up?
Is there anything that could be done in MySQL config?
Is there anything else I can do to speed up a query?

Any hints appreciated, thanks.

FYI, I asked my initial question on so: https://stackoverflow.com/questions/31969964/mysql-slow-avg-query-for-411m-rows. After that I was trying to add the suggested index (gene2, gene1) but didn't have enough space, so dropped the table and reimported 90M rows.


texts: Fast backup & restore of MySQL database to revert recent changes

texts:
I have a decently big MySQL DB (InnoDB) with size about 400 MB. Thus dumping the whole DB with mysqldump and restoring it back takes forever (more than 15 minutes, which is unacceptable).

For debugging purposes I need to run my code against a certain "version" of the DB and then revert all changes back (if any) and try again (possibly multiple times). In order to reproduce a bug, I need to always start from the same version of DB.

Is there any way to manually instruct the DB to create a point-in-time snapshot and then restore it back on demand? This should ideally take just a couple of seconds (I imagine some copy-on-write method would be used).

I'm looking for a solution that is independent from the end application. Imagine a web application that you need to run your robot tests on (which includes multiple request/response cycles). Moreover, if we treat the application as possibly buggy we can't trust it to reliably manage transaction rollbacks either.

I just tried to backup /var/lib/mysql with rsync. Cannot even start MySQL after restore, probably there's some file left somewhere that breaks the integrity. Anyway, I was looking for a more higher level solution, resorting to FS-level backups only as a last hope.


texts: How can I import an old pgdump into an updated schema?

texts:
I'm DB newb doing a software upgrade, and my predecessor's original migration plan may not have been as well thought out as I thought...

The Plan was to use our backup, created via

pg_dump -U derp -h db-host -Fc derp > backup

Flash a clean VM with an upgraded software, with its fancy new schema and use

pg_restore -v -U derp -n public -c -1 -h db-host -d derp backup

to load it with all its fancy old data.

Unfortunately I'm getting errors because of the updated schema, with new columns that create new relations.  "cannot drop constrain X on table Y because other objects depend on it"

I tried using -a (data only) but that fails fast because there are many lookup tables with now duplicated keys.

Can I somehow import the existing copy?

If not, can I alter the new database schema in a way that will allow my old dump to work?

If not.... can I make a different, more useful copy of the old database?

If I'm asking really dumb questions, what should I be asking?


texts: Get the list of tables used in a stored-procedure for a database

texts: our company is planning to administer the report server database and the requirement is to get the list of the tables used in stored-procedures in the productions server database.Is there any query/function to retrieve this information ?


texts: SQL Server 2017 MLS - cant add python package

texts:
I'm new-ish to MLS and have been trying to add new python packages like  attrs  to the python but it hasn't been working.
Our set up is SQL Server 2017 with CU14 on Win 2016.
Python version is 3.5.2
The usual command I run is:
pip install attrs

and I run this from the C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\Scripts
but nothing happens.....
it says "collecting attrs" then nothing.
When I look in the So I updated a dev VM to SQL Server 2017 CU22, and it installed Python 3.7 and tried same thing - it worked.
But this doesn't help me as we already have existing SQL Server 2017 servers with this package in it and the dude who originally set this up is gone, so any help appreciated.
All the installs are default, nothing special.


texts: How to Determine Optimal Disk Cluster Size for Firebird 2.5

texts:
I'm using Firebird 2.5.6 Classic Server on a Windows 7 64-bit machine. We're moving the database to a separate disk (32GB NTFS Volume) and are now provisioning that disk. My question is, what should be taken into account when deciding disk cluster size? The goal being to get optimal performance when the database is reading from disk. I'm hoping you all can provide information on the relevant factors so that I can make my own decision, and so this question will be helpful to others in different situations.

Should the cluster size (allocation unit size) match the Database Page Size? I'm not very knowledgeable in how Firebird uses the disk though, maybe it makes more sense to use a bigger cluster size? I read this question on the same topic but about SQL Server. I'm unsure if Firebird SQL behaves similarly to SQL Server when reading from disk, or if there are other factors to take into account.


texts: Cloning a Db2 database within a single instance

texts:
I need to create an exact copy of a Db2 database (V11.1+) on the same server within the same instance. (This is a development server that does not run production databases.)

This is how I do it now.

Dump tables using a hand-written script
Create a new database
Import service functions specific to my application to the database
Create a schema in the new database
Import data from a dump into the new database
Set database configuration parameters for the new database

I read that it is possible to copy a database scheme with db2move.
Is there a simple approach that could be used to perform a full copy?


texts: Constraints between tables on a variable

texts:
im aiming to add a constraint on the number of bicycles allowed to be stored in each terminal - the number differing between terminals.

These are my original tables

create table terminal(
terminal_id serial primary key,
terminal_town varchar(30) not null,
terminal_street varchar(30) not null,
terminal_max_capacity smallint not null,
sponsor_payment_id serial references sponsor_payment(sponsor_payment_id)
);

    create table bicycle(
bicycle_id serial primary key,
bicycle_make varchar(30) not null,
bicycle_model varchar(30) not null,
bicycle_colour varchar(30) not null,
bicycle_type varchar(14) not null,
terminal_id serial references terminal(terminal_id) on delete cascade
);

When adding this:

constraint terminal_max_capacity_check check (references bicycle(bicycle_id between 1 and terminal_max_capacity)

It didnt work.

any ideas.
Thanks


texts:
I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type work (Adding record, modifying, viewing etc) via an ASP.Net website.

Each insert and update is done in it's own Transaction, handled by the data access layer. The database isolation, I believe, is set to Read_Commited.

All is working fine.

However, a new module has been added, which polls a separate database for information. If there is new info, a process starts a new transaction, and the uses the same data accessor code to read from our database, as well as reading from another separate database for the new info. It then does loads of checks to see what it must do with the new data... And the starts doing loads of updates or inserts into our database. This is all within a large transaction. All inserts and updates from both the UI application, and the polling service, go through the same CRUD procedures. Because an incoming message to be processed can hold a lot of entitys that need updating, the time for a transaction to complete can be between a split second, and a minute.

What we're finding though, is that when a larger message is processed, the UI locks up, and can lock for a user for 3 minutes.

So, we thought that maybe adding 'NOLOCK' hints to the selects may assist. It didn't. Well, it may have helped a bit, but the lockups are still happening.

I thought that the cause may be that the message arrives, and a transaction is started, which is locking other transactions from working (Even SELECT statements, which I don't understand). Profiling the database shows that even simple selects are taking ages to complete on the UI (Simple, such as SELECT fields FROM SingleTable WHERE PrimaryKey = Value

Our indexes seem OK... We do have Triggers on all tables which simply copy updates and inserts into an AUDIT database table. Don't think they're the problem.

I think it';s because of the transaction around the message processing, which is locking the UI out.

Can anyone maybe share an experience or tell me where I can look to see why we're getting UI lockouts? The UI should have priority. Message processing is a background thing... the users need to be priority... but it seems the messages are locking the database... and we're unsure if the UI ever locks the message processing.

Hope someone can assist. I can provide as much info as possible to help.


texts: Postgres default privileges do not seem to work

texts:
I am trying to set up RBAC in Postgres where I have a database and a schema. I have a few roles provisioner, service, mydb_ro, and mydb_rw. I ran the following to change default privileges
ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
    GRANT SELECT                           ON TABLES TO mydb_ro;  -- only read

ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
    GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO mydb_rw;  -- + write, TRUNCATE optional

ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
    GRANT REFERENCES, TRIGGER              ON TABLES TO mydb_rw;  -- + TRIGGER

ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
    GRANT USAGE, SELECT, UPDATE            ON SEQUENCES TO mydb_rw;  -- SELECT, UPDATE are optional

ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
    GRANT EXECUTE                          ON FUNCTIONS TO mydb_rw;

this is what I see in default privileges
postgres@mydb=> \ddp
                           Default access privileges
┌─────────────┬──────────────────┬──────────┬─────────────────────────────────┐
│    Owner    │      Schema      │   Type   │        Access privileges        │
├─────────────┼──────────────────┼──────────┼─────────────────────────────────┤
│ provisioner │ mydb_schema      │ function │ mydb_rw=X/provisioner           │
│ provisioner │ mydb_schema      │ sequence │ mydb_rw=rwU/provisioner         │
│ provisioner │ mydb_schema      │ table    │ mydb_ro=r/provisioner          ↵│
│             │                  │          │ mydb_rw=awdDxt/provisioner      │
└─────────────┴──────────────────┴──────────┴─────────────────────────────────┘

But when I am logged in as a user that has the provisioner role and I create a table, a user that has service role cannot see it. What am I doing wrong?
I now ran
GRANT mydb_ro TO mydb_rw;
GRANT mydb_rw TO service;

But that did not help with the permission issue.


texts: does solution of Normalilization causes duplicacy in NoSQL?

texts:
i am currently using mysql as a database but now i have decided to switch rdbms to nosql probably on mongoDB. i have an doubt that nosql removed the concept of normalization from database so in such a case lots of duplicate records were there should be present on database. does nosql solved this or duplicacy in record is part of nosql?

same customer can registered multiple complaints so while using nosql if normalization not in use we need to store whole customer information each time with key. so my databese contains duplicate data.


texts: mysql master.info shows password in plain text

texts: I'm running 5.7 mysql. Is there anyway I can hide the password? I read that it is visible on 5.6 as well


texts: Binding errors with dependent stored procedures

texts:
We are getting strange errors on one of our stored procedures. This stored procedure calls other stored procedures & we are getting errors relating to columns that cannot be found in the sub procedures.  For example:

Exec StoredProc1, this stored proc calls StoredProc2, StoredProc3 & StoredProc4

When I execute StoredProc1 the first time I get errors relating to columns on StoredProc2.
I then execute StoredProc2 directly and it works, then I call StoredProc1 again and it complains about column Errors on StoredProc3, and so on.

I am guessing this has to do with how SQL Server has cached its execution plans for StoredProc1, & executing each stored proc in turn forces the Stored Plans / Dependencies to be updated.

How can I correct this across the board?

If I execute each dependent procedure one at a time then execute the parent it works. If I then run these commands:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

and execute the parent procedure I get the errors again.

The stored procedures all use a temporary table with the same name but if I understand it correctly because they are in different procedures they would create separate tables.


texts: ORA-12516: when accessing another database via link

texts:
I need to bring some data (blobs) from another database. I created global links to enable bidirectional communication. The links work. When I try to bring some data from tables that have BLOB columns I get intermittently the ORA-12516:

  TNS:listener could not find available handler with matching protocol stack

Here's what I run to bring BLOBs to my schema:

CREATE TABLE TEMP_MY_TABLE AS
SELECT * FROM SOME_REMOTE_TABLE_WITH_BLOB_COLUMN@dblink_source;

Oracle 11 r2. Blobs have different sizes.

Number of PROCESSES is set to 1000 and only half is utilized during the run. Sessions = 1528.

Both source and target are identical in hardware and software including patches.

The links work fine for hundreds of other queries. I checked and they are resolved properly.


texts:
I have a query that looks for and counts orders that have been written in a medical record that have a specific display group SID. I have 8 medical centers and I want a count for all of them even if it's zero. My query return counts fine when there are instances, but it will not list a facility with a zero count. I tried the LEFT JOIN as mentioned in other threads.

SELECT DISTINCT *
INTO   #T
FROM   (SELECT    p. [PatientSID]
                  ,p.[TestPatientFlag]
                  ,co.[CPRSOrderSID]
                  ,co.[DisplayGroupSID]
                  ,co.[Sta3n]
                  ,[EnteredDateTime]
        FROM      [CPRSOrder].[CPRSOrder] co
        LEFT JOIN [SPatient].[SPatient] p
        ON        co.[PatientSID] = p.[PatientSID]
        WHERE     [TestPatientFlag] IS NULL
        AND       [EnteredDateTime] >'2018-01-01'
        ) AS X

SELECT [Sta3n]
       ,COUNT([DisplayGroupSID]) AS '# Of RTC Requests'
FROM   #T
WHERE [DisplayGroupSID] IN (1200003559
                            ,1200003561
                            ,1200003558
                            ,1200003557
                            ,1200003560
                            ,1200003562
                            ,1200003556
                            ,1200003519)
GROUP BY [Sta3n]

Here's the output (It's missing 4 sites who have no instance of the type of order I am looking for:

Sta3n   # Of RTC Requests
-----   -----------------
679     755
544     974
521     2
534     54


texts: Adding a Far DR secondary server Log shipping on already configured SQL server

texts:
We have a setup where there are 3 SQL Server instances in which 1 primary,1 Near Secondary,1 Far Secondary.

So the there is already log shipping configured between Primary and Near DR Secondary.

Now i have to configure/Add log shipping between the Primary and the Far DR Secondary.

Need to add 1 database in the log shipping size is 1.1 TB

The issue is copying a full backup might take more than 12 hours from primary to really far DR and there is 4 schedules of daily full backup on Primary each of 6 hours interval.

Kindly help with a plan so that i can configure the log shipping on the Far DR without any delay and one more thing we cannot stop the existing log shipping config.


texts: Generate Dates between Date Ranges in mysql

texts:
Is there a way to generate dates between date ranges. After looking on SO I found out there is a way to use CTE, another option is to use Union All from 0 to 9.
Is there an inbuilt function which I can use to generate dates between date range?

We are using MySQL 8.0.


texts: Query to get number of items from different table reference in another table

texts:
I have 5 tables which are

- Project(PK Project_ID),
- Bottles (PK Bottle_ID, FK Project_ID),
- Plastics (PK Plastic_ID, FK Project_ID),
- Glasses (PK Glass_ID,FK Project_ID) and
- Cups (PK Cup_ID, FK Project_ID)

How can I get one SQL query to return the number of Bottles ,Plastics , Glasses  and Cup per Project,  where One Project has many Bottles ,Plastics , Glasses  and Cup ?


texts: Is the overhead of frequent query cache invalidation ever worth it?

texts:
I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that are being executed on many of the tables.

What I'm trying to determine is whether or not there is any benefit at all to allowing the query cache to be used for SELECT statements that are being run against these tables. Since they get invalidated so quickly, it seems to me the best thing would be to just use SQL_NO_CACHE on SELECT statements with these tables.

Is the overhead of frequent invalidation ever worth it?

Edit: At the request of the user @RolandoMySQLDBA below, here's the info on MyISAM and INNODB.

InnoDB

Data Size: 177.414 GB
Index Size: 114.792 GB
Table Size: 292.205 GB

MyISAM

Data Size: 379.762 GB
Index Size: 80.681 GB
Table Size: 460.443 GB

Additional info:

Version: 5.0.85
query_cache_limit: 1048576
query_cache_min_res_unit: 4096
query_cache_size: 104857600
query_cache_type: ON
query_cache_wlock_invalidate: OFF
innodb_buffer_pool_size: 8841592832
24GB of RAM


texts: Does STATISTICS IO output include Version Store reads?

texts: SQL Server has an option SET STATISTICS IO ON that shows the number of logical and physical page reads for a query. Do these stats include reads of the version store for SNAPSHOT and RCSI queries?


texts: Which string variables in mysql support utf-8?

texts:
I have a table that stores string from a user and is displayed in a web form.
But as I see when the user inputs e.g. in a language of cyrilic alphabet (I assume is UTF-8) garbage are displayed back.
I did a show create table and I saw that the table is defined as LATIN-1 and the column that stores the string is defined as TEXT.
I am not clear on what type of data does TEXT stores. Is it only for ASCII? Should I be using a different data type? Which would be the most appropriate?


texts: Deterministic function for getting today's date

texts:
I am trying to create an indexed view using the following code (so that I can publish it to replication it as a table):

CREATE VIEW lc.vw_dates
WITH SCHEMABINDING
AS

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), number) AS SettingDate
FROM lc.numbers
WHERE number<8

GO

CREATE UNIQUE CLUSTERED INDEX
idx_LCDates ON lc.vw_dates(SettingDate)

lc.numbers is simply a table with 1 column (number) which is incremented by row 1-100.

However, I keep getting the error:

  Column 'SettingDate' in view 'lc.vw_dates' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

I realize that GETDATE() is non-deterministic. But, is there a way to make this work without drawing a calendar table?


texts: Restore database with zero size log file

texts:
SQL Server backups contain meta-data about the size of the original log (LDF) file.

So if you backup a database that has a 5GB LDF file, when you restore it, you will get an (empty) 5GB LDF file. This often isn't needed (because the log file of the restored database contains practically nothing)

Often this causes problems with database restores on busy dev servers with not much space left if people *cough* haven't been too careful about shrinking log files before making the backup.

Is there any way to restore a SQL Server backup but tell it "Dont bother making that empty 5GB LDF file, thanks" ?


texts: Oracle 11g Enterprise Manager Login Fail Internal Error has Occured

texts:
I have installed Oracle 11g on my Windows 10 machine. I followed all preinstallation requirements.

When trying to log in to the Enterprise manager via this link:

https://localhost:1158/em

I get the error:

  Internal Error has occurred. Check the log file for details.

I have found log files and it looks like this is the relevant entry:

2017-09-17 07:36:53,631 [MetricCollector:HOMETAB_THREAD600:60] ERROR rt.DbMetricCollectorTarget _getAllData.344 - java.lang.NullPointerException
java.lang.NullPointerException
    at oracle.sysman.emSDK.emd.comm.EMDClient.getURL(EMDClient.java:1570)
    at oracle.sysman.emSDK.emd.comm.EMDClient.getConnection(EMDClient.java:1266)
    at oracle.sysman.emSDK.emd.comm.EMDClient.getConnection(EMDClient.java:1242)
    at oracle.sysman.emSDK.emd.comm.EMDClient.getResponseForRequest(EMDClient.java:1689)
    at oracle.sysman.emSDK.emd.comm.EMDClient.getMetrics(EMDClient.java:981)
    at oracle.sysman.emo.perf.metric.rt.DbHomeTab._getAllData(DbHomeTab.java:340)
    at oracle.sysman.emo.perf.metric.rt.DbHomeTab.getData(DbHomeTab.java:154)
    at oracle.sysman.emo.perf.metric.eng.MetricCached.collectCachedData(MetricCached.java:416)
    at oracle.sysman.emo.perf.metric.eng.MetricCollectorThread._collectCachedData(MetricCollectorThread.java:605)
    at oracle.sysman.emo.perf.metric.eng.MetricCollectorThread.run(MetricCollectorThread.java:325)
    at java.lang.Thread.run(Thread.java:595)

I have tried to set the unique name in environment variables, I thought maybe Java was the problem so I updated my JDK to 8. I am still getting the same error, and my SQL Plus will not open, as soon as I put in the correct credentials it closes. Also I have configured a Listener and tested, test was successful.

Please help.


texts: After importing an exported table into a new database, the data occupies a significantly larger amount of disk space

texts:
I came across this issue while troubleshooting a problem that I described in another post, but I thought this deserves a post of its own.

I'm trying to reclaim some unused space in a table. This is the current disk usage report for the table:

This is the current disk usage report for the database:

As an experiment, I did the following:

Used the bcp utility to export the table contents:

EXECUTE sys.xp_cmdshell 'bcp MyDb.dbo.MyTable out c:\bcp\MyTable.bcp -n -S MyServer -T'

Created a new database.
Created the table with its schema in the new database:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
    [Column1]  [int] NOT NULL,
    [Column2]  [int] NOT NULL,
    [Column3]  [int] NOT NULL,
    [Column4]  [bit] NOT NULL,
    [Column5]  [tinyint] NOT NULL,
    [Column6]  [datetime] NULL,
    [Column7]  [int] NOT NULL,
    [Column8]  [varchar](100) NULL,
    [Column9]  [varchar](256) NULL,
    [Column10] [int] NULL,
    [Column11] [varbinary](max) NULL,
    [Column12] [varchar](max) NULL,
    [Column13] [varchar](100) NULL,
    [Column14] [varchar](6) NULL,
    [Column15] [int] NOT NULL,
    [Column16] [bit] NOT NULL,
    [Column17] [datetime] NULL,
    [Column18] [varchar](50) NULL,
    [Column19] [varchar](50) NULL,
    [Column20] [varchar](60) NULL,
    [Column21] [varchar](20) NULL,
    [Column22] [varchar](120) NULL,
    [Column23] [varchar](4) NULL,
    [Column24] [varchar](75) NULL,
    [Column25] [char](1) NULL,
    [Column26] [varchar](50) NULL,
    [Column27] [varchar](128) NULL,
    [Column28] [varchar](50) NULL,
    [Column29] [int] NULL,
    [Column30] [varchar](max) NULL,
 CONSTRAINT [PK] PRIMARY KEY CLUSTERED
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column4]  DEFAULT (0) FOR [Column4]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column5]  DEFAULT (0) FOR [Column5]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column15]  DEFAULT (0) FOR [Column15]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column16]  DEFAULT (0) FOR [Column16]
GO

Inserted the exported data into the new table:

BULK INSERT dbo.MyTable FROM 'c:\bcp\MyTable.bcp'
WITH (
DATAFILETYPE = 'native',
ORDER(Column1,Column2,Column3),
TABLOCK,
KEEPIDENTITY
)

Now here are the disk usage reports for the new database:

We can see that we've eliminated most of the unused space, but the space for the data has gone way up, from 4.5 GB to 8.2 GB, resulting in a negligible change in the overall space.

So, why is this happening? Is this kind of outcome expected?


texts: Handle expired SQL login's passwords in SQL Server

texts:
Is it possible to change the password for a login with an expired password using SQL Server Management Objects (SMO) programming?
Many times during working hours I got this message from a production server.
I have a 'sa' level privilege in SQL Server.

Working Environment

Microsoft SQL Server 2012 Express
Windows Server 2012 R2
VMware,Inc. VMware virtual platform


texts: MariaDB threads goes around 10K suddenly

texts:
We have a big Database, It is about 750G. And also for replication this is our Master DB and we have 4 slaves, 3 of them are sync without delay, and one of them has 2 hours delay.

OS : Ubuntu 16.04
DB : MariaDB 10.2.9
app : php 7

Sometimes mysql thread increase suddenly (about 5k) without any reason, by chance I found that if I stop one of first three servers slave trough stop slave it can decrease the threads to normal numbers (around 500), and start slave by star slave after 1 minute or more.

Every time Mysql threads goes up more than 4000 in a second I should use :
stop slave; start slave; on every server ! and this is not good solution!

*** And even worse cases, stop slave; start slave; won't work and Mysql threads is going up more than 10K and I should stop and start Master database Mariadb  ( I mean systemctl stop mysql && systemctl stop mysql) and it is clear that at that time my application is not reachable by users!

My questions are :
-1 : what is the reason of this accidents ?
-2 : what is temporary solution ?
-3 : will something like maxscale help me in this scenario or not
     -3.1 : If yes how does maxscale will help me ?
-4 : what is the best solution for my problem, how can I trace the issue ?


texts: SQLite: Using count in triggers to update another column

texts:
UPDATE: Fixed. Not 100% sure why sqlfiddle.com didn't like my syntax, but using https://dbfiddle.uk/?rdbms=sqlite_3.27 worked. I did change the last WHERE clause to be based on the id column, so that it only updates the current record, and increments instead of storing max() on records for username.
I'm trying and failing to do something similar as described here (but in SQLite):
Using count in triggers to update another table.
I assume a trigger is the best way to achieve what I want, but am open to alternate suggestions. My SQL experience is... limited, to say the least.
My goal is to have one column with an identifier, say username and another column that is a count of the times username is in the database at the time of the record insertion.
id | username | u_cnt | etc
1  |  MarkC   |   0   | ...
2  |  PeterB  |   0   | ...
3  |  MarkC   |   1   | ...

Here's a sample of what I've been trying:
create trigger count_user
after insert on rec_info
begin
  update rec_info set u_cnt = ( select count(*) from rec_info where username = new.username );
end

I've been bashing my head against the docs at https://sqlite.org/lang_createtrigger.html#syntax_restrictions_on_update_delete_and_insert_statements_within_triggers
but I can't seem to figure out what's wrong with the count_user statement. Depending on where I'm testing it I'll get one of the following
near ")": syntax error or could not prepare statement (1 incomplete input), neither of which are that useful to me.
I've also tried making u_cnt a generated/computed column ( https://sqlite.org/gencol.html ), but trying to declare the column with:
u_cnt int as (count(username)) stored,

Which gives me misuse of aggregate function count() and trying to add HAVING or GROUP BY led me down a rabbit hole that gave me and headache and didn't seem to lead to progress.


texts: Can't execute dynamic DDL, argument is NULL

texts:
I'm running PostgreSQL 9.4.4.
I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly.

I'm trying to dynamically create partitions for a table. I've got the individual parts of the code working however I can't get the function to execute the dynamic DDL statements. If I replace the execute with an insert into a log table, the statements that are generated are valid and I can run them myself but if the function attempts to execute the following error is produced:

select part_test();
ERROR:  query string argument of EXECUTE is null
CONTEXT:  PL/pgSQL function phil_test() line 18 at EXECUTE statement

A reproducible function is as follows:

create table harry(logged_time timestamp, id integer);

create or replace function part_test () returns void as $$
declare
rec record;
begin
for REC in SELECT 'create table if not exists '||'harry'||'_'
                ||to_char(date_trunc('day', my_date), 'YYYY_MON_DD')
                ||' (check (logged_time>=DATE'''
                ||to_char(date_trunc('day', my_date), 'YYYY_MON_DD')
                ||''' and logged_time < DATE'''
                ||lead(to_char(date_trunc('day', my_date), 'YYYY_MON_DD'),1)
                  over (order by date_trunc('day', my_date))
                ||''')) inherits ('||'harry'||');' STMT
                FROM generate_series
                        ( '2015-SEP-01'::timestamp
                        , '2015-SEP-10'::timestamp
                        , '1 day'::interval) my_date
LOOP
    execute REC.STMT;
END LOOP;
end;
$$ LANGUAGE plpgsql;


texts: How to concat one column of multiple rows (group by clause used in query)

texts:
I had some issue while populating some data as per my requirements and that is as follows..

Table data:

---------------------------------------------
EMPID  PROJ_TYPE  HOURS  ST_DATE    ED_DATE
---------------------------------------------
1      DEV        10     2013-1-1   2013-5-1
1      DEV        34     2013-5-2   2013-8-1
1      DEV        23     2013-8-2   2013-10-1
1      TEST       12     2014-1-1   2014-3-1
1      TEST       25     2014-3-2   2014-6-1

Here I want to fetch the employee data with hours, So as simple I wrote

SELECT EMPID,
       PROJ_TYPE,
       SUM(CASE WHEN PROJ_TYPE='DEV' THEN HOURS ELSE 0 END) DEV,
       SUM(CASE WHEN PROJ_TYPE='TEST' THEN HOURS ELSE 0 END) TEST
FROM TABLE1
GROUP BY EMPID, PROJ_TYPE

Hence it is returning two rows as expected but here the twist I want only one row per employee. If employee have multiple PROJ_TYPE then it should combine both project values and return only one row.

Finally my result set should look like:

----------------------------
EMPID  PROJ     DEV  TEST
----------------------------
1     DEV/TEST  67   37

Hope I am clear please comment if any doubt on my question.


texts:
When I have a session running in psql it does not show newly created tables (from another connection but for the current owner role).

If I re-login they show up. Do I need to refresh a psql cache somehow or is this related to transaction/sessions or cached permissions?


texts: How can I push data from my local MAMP database to my remote database?

texts:
I'm developing a site locally using MAMP Pro, and I've created a mysql database locally that have the same structure as the remote server. (username and password are different though)

I'm inserting new data into the local database - since that's much faster than interacting with the remote server - and at the end of the day I would like to just update my remote database with the stuff I've added locally.

Now I'm using the "export to file" in phpMyAdmin on each table that I've added new stuff into, and adding the DROP IF EXISTS-statement. It's not that hard, but I kind of have to take the site down because the foregin keys fails when I'm just updating one at a time.

Any suggestion on how to just update the content of tables on the remote server, based on what's in the local server?

I have MySQL Workbench, but all I could find was a way to sync the models. As in updating the table structures, adding new tables and stuff like that...
I might have overlooked some functionality there?


texts: sys.sp_reset_connection. Why theres a lot of them ?

texts:
Theres a lot of questions here on DBA EXCHANGE. But none of them can help me.

I have 2 problems:

1) Using sp_whoisactive, I found lots of sys.sp_reset_connection;1( I know they are sp_reset_connections because I used dbcc inputbuffer(SPID):

This is the "main server", so the other servers ( 3,4,7 ) are constantly requesting information of some databases here ( And what I see is, every request is for the MASTER database ).

2) Why they're taking so much time?

Looking at some answers here, I found that the system is waiting for the CPU. but all CPU is null. How can this be the problem?

  Under the covers SQL Server uses the sp_reset_connection logic to
  ‘reset’ the connection state for the SQL Server, which is faster than
  establishing a completely new connection.  Older drivers send the
  procedure call as a separate, TDS, round-trip to the SQL Server.
  Newer client drivers add a flag bit along with the next command,
  avoiding the extra network round-trip.

Good...But I still don't know what to do.

This is not giving me problems. I just want to know how to troubleshoot this.


texts:
In SQL Server 2012 Standard edition, I know that the max number of user connections is 32,767. What should I do as a DBA if I am heading towards this number?

Currently there are 30,000 user connections, and this number is expected to increase.


texts:
I'm completely stumped.  I'm a relatively new SQL Server DBA so I'm at a loss with the following situation; hoping maybe someone had an idea.

I have a new production SQL Server (Windows Server 2003/SQL Server 2005 SP3)  that I am going to be responsible for.  Wanted to get some basic stats so I got some scripts off of SQLServerPedia to help with that. (Great stuff btw).

Did a check on index fragmentation and ran the following query (took it about 30 min to run)...

SELECT
      db.name AS databaseName
    , SCHEMA_NAME(obj.schema_id) AS schemaName
    , OBJECT_NAME(ps.OBJECT_ID) AS tableName
    , ps.OBJECT_ID AS objectID
    , ps.index_id AS indexID
    , ps.partition_number AS partitionNumber
    , ps.avg_fragmentation_in_percent AS fragmentation
    , ps.page_count
FROM sys.databases db
  INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps
      ON db.database_id = ps.database_id
  INNER JOIN sys.objects obj ON ps.object_id = obj.object_id
WHERE ps.index_id > 0
   AND ps.page_count > 100
   AND ps.avg_fragmentation_in_percent > 30
ORDER BY databaseName, schemaName, tableName
OPTION (MaxDop 1);

This came back with several rows of data with several indexes for several databases.  (I didn't save the result set of the query -- wish I had now).  Long story short I re-ran the query about ten minutes later and it ran in 2 seconds and returned no rows.  Nothing.  I have run 0 queries to manipulate indexes in that timeframe.  No jobs had run.  There is nothing in the SQL Server Log for that time period to indicate anything fishy.  And I double checked and confirmed the query was run both times on the same server.  How could this be?  How could approx. 20 indexes that were over 30% fragmentation with more than 100 page count suddenly be taken care of (or disappear from this result set) without me doing anything (or from what I can tell -- no one else or no other application) doing anything either?


texts:
currently fsync option is disabled in postgresql.conf.

If I am enable that,

From:

#fsync = on

To:

fsync = on

(I am enabling fsync to avoid data corruption if any failure since my data (tablespace created in mounted remote partition) is available in remote location)

should I need to consider consider below properties also?

#synchronous_commit = on
#wal_sync_method = fsync

Should I need to enabled above configuration also?


texts: MySql column value to indicate 2 possible options

texts:
I don't know if the title represent my doubt very well, but basically I have some columns that can represent 2 possible values.

For example, with the following table that retains data about orders:

id | payment_type | shipping_type
14 | 1            | 1
15 | 1            | 2
16 | 2            | 1

So, for this those 1 and 2* would be:

payment_type: 1= 'Money', 2= 'Online'
shipping_type: 1= 'Delivery', 2= 'Take on store'

* The number values are used to store in the database only, on the screen it's displayed the text for the admins.

This data need to be displayed on the screen for the admins to analyse the order report. Some times the "labels" (money/online) may change, not so frequently but that's a possibility, but it will always be one or the other option.

This data should be displayed on the screen as simple as the following:

Order: #01234
Payment type: Money
Delivery type: Take on store

This data also needs to be able to be edit. For example, let's say I sold an Item for my client to delivery on his address, but for some reason he was passing by and decided to stop on the store to take it. So I need to change the shipping_type from 1 (delivery) to 2 (take on store). Same applies for the payment_type.

So, my doubt is about the way it's organized and how I can manage this data in order to be able display and edit as needed. Currently I'm naming the fields manually with a CASE statement, ex.: CASE WHEN payment_type = 1 THEN 'Money' ELSE 'Online' END AS 'payment_type'. But I'm feeling this is not the best way to go, since it's very manually and if I update the field, I need to also update it manually in multiple queries. If the terminology changes, ex.: Money becomes Check, than I would need to go trought all the options and change it. The name or "labels", don't change often, but it's something that may occur, as it already did in the past.

As an alternative, I was thinking about a table like the one shown below:

payment_table
id | value | name
-  | 1     | Money
-  | 2     | Online

But would have a lot of these tables. Actually, it's not a big deal, but I'd like to know if this is the correct approach to this situation. I'm new to databases and backend in general.


texts: Postgres - Insert performance with indexes

texts:
We have a table with about 20 columns in a Postgres 9.2.10 DB. For better performance on some SELECT queries, we plan to add an index on one column with data type timestamp. As the index also can slow down the performance on inserts, we did the following performance test:

We inserted 5 million records into the table. That's the max. number of records we expect in production. Then we measured the time to insert 10000 records with and without index on the timestamp column. This is the maximum number of inserts we expect per day, In peaks not more than 5 inserts per second.

Here the results:

With index - 84 441 ms
Without index - 78 000 ms

At least for this test, the index slowed down the performance only slightly. For our requirements, I do not see an issue to add the index.

But this was only one test in a lab environment, are there any other pitfalls when running this on a production database? Can we get into a situation, where an INSERT suddenly takes more than 5 seconds under specific circumstances?


texts: What's the solution for corrupted MDF files?

texts:
We are working on an MDF file that has been corrupted, because a virus deleted 2 MB from the header of the file.

We also have another backup of this MDF File from 3 months ago. Both MDF files have the same size.

I think I can read some of the tables in binary data from the corrupted DB, but I don't know about the structure of MDF files

What's the solution for this?

Thanks.


texts: Recover data from ransomware-encrypted databases

texts:
I'm doing some work for an organization that was hit by the RYUK ransomware (a newer variant of HERMES), which has encrypted some large databases (around a half TB each). I don't know if backups are difficult to restore or simply don't exist; either way, I have been asked to do what I can with the encrypted files.

This ransomware doesn't appear to encrypt the entire .mdf file; it seems to only encrypt certain chunks.

Here's a snippet of the tail end of one of the databases as seen in a hex editor:

Do you know of any tools or methods by which I can extract any salvageable data? Potentially even resorting to options like reverse-engineering the storage format and writing a one-off utility. (I can get the table schema.)


texts: Is is possible to handle a UserAbort or ClientTimeOut within a Stored Procedure?

texts:
An example:

create proc [dbo].[usp_SlowProc]
as
begin try
  set xact_abort on;  -- Ensures rollback on some errors that bypass the try/catch mechanism
  raiserror('Waiting...', 0, 1) with nowait
  waitfor delay '00:00:10'
  raiserror('Completed successfully.', 0, 1) with nowait
end try
begin catch
    raiserror('Caught It.', 0, 1) with nowait
    -- Rollback, log the error.
end catch

Then run it within SSMS/Toad:

exec dbo.usp_SlowProc;

and then cancel it (using SSMS/Toad) before it completes.  The catch block does not run.

Assuming from this link that a user abort is the same as as what .NET does on a client timeout (the result is the same).

SQL Server Transaction Timeout


texts: How to model multiple types of contacts?

texts:
I'm not sure if this can be called a polymorphic association - what is needed is to for each Message to have a Sender Contact and a Recipient Contact.
Both Sender Contact and Recipient Contact could be one of 2+ types. Currently SMS and Email are supported but more may come.

I have this urge to eliminate a possible duplication that would appear in case of having two different tables - sender_contacts and recipient_contacts. Also type column is a kinda of a DB-modeling smell.
But I'm not a DB modelling expert and rather looking for an advise from an experts how to properly model those relationships.

Any input is appreciated.

Thanks.


texts:
I have a few dozen tables, that have data in them, both for the core set of data, and then the company specific data.

I can never delete the core set of data, that is global for all companies.

But i want to be able to help identify orphan data.

Data that is inactive, but hasn't been deleted.

I do not need a query, I can figure that out myself.

Just some ideas on best practices, to help identify then get rid of orphan data, which hopefully will trim the database size down.

Thanks


texts: Concatenation, with separators, where any combo of input fields might be null

texts:
I have three columns, CITY, STATE, COUNTRY. One, two, or all columns may be NULL.

With the following logic, a single string is made using the SQL below. It is messy, and I am wondering if there is a simpler way to achieve the output?  I've considered CONCAT() but I don't think I can use that and still get the separators in there.

if both CITY and STATE have a value, use that, separated by ', '. Exclude COUNTRY.
if only one of CITY or STATE exists, AND Country exists, use the one of CITY/STATE which exists, plus the separator, and COUNTRY.
if neither CITY or STATE exist, use country only with no separator
if only one of the three exists, use it, as is, with no separator
If none exist, output should be NULL (not '')

I will add that if a CITY and/or STATE value exists, then COUNTRY will never be NULL.

Here is the code so far. It is messy but I believe it meets the output requirements listed above.

I am working on SQL Server 2008.

CASE WHEN
    CASE WHEN ISNULL(M.CITY, '') = '' THEN  '' ELSE ISNULL(M.CITY, '') + ', ' END + CASE WHEN ISNULL(M.PROVINCE, '') = '' THEN  '' ELSE ISNULL(M.PROVINCE, '') END

     = '' THEN CASE WHEN COUNTRY IS NOT NULL THEN COUNTRY ELSE NULL END ELSE

     CASE WHEN ISNULL(M.CITY, '') = '' THEN  '' ELSE ISNULL(M.CITY, '') + ', ' END + CASE WHEN ISNULL(M.PROVINCE, '') = '' THEN  '' ELSE ISNULL(M.PROVINCE, '') END
END
+
CASE
    WHEN (M.CITY IS NULL AND M.PROVINCE IS NOT NULL) OR (M.CITY IS NOT NULL AND M.PROVINCE IS NULL) THEN
        CASE WHEN PROVINCE IS NOT NULL AND CITY IS NULL THEN ', ' ELSE '' END + COUNTRY
    ELSE ''
END
LOCATION


texts: mysql match against with same score on different input

texts:
I am trying to find the nearest match against a given name with mysql. The scoring is for the first two results the same, but looking at it I would rather get the second one as it fit's the name almost.

SELECT ID, name, MATCH (`name`)
AGAINST ('Superocean Héritage 42mm' IN BOOLEAN MODE) as `score`
FROM cat_names cs
WHERE MATCH (`name`) AGAINST ('Superocean Héritage 42mm' IN BOOLEAN MODE)
ORDER BY `score` DESC

The result:

Superocean Héritage     6.3744072914123535

Superocean Héritage 42  6.3744072914123535

Superocean Héritage 46  6.3744072914123535

Is there a way to tweak the query in order to retrieve the second result correctly?

Additionally the fulltext search returns a low quality score for exact matches:

Big Bang Aero Bang
9.445959091186523
Big Bang 44 mm
6.32852840423584
Big Bang Caviar
6.32852840423584
Big Bang Jeans
6.32852840423584
Big Bang Meca-10
6.32852840423584
Big Bang Sang Bleu
6.32852840423584
Big Bang Unico
6.32852840423584
Spirit of Big Bang
6.32852840423584
Big Bang 41 mm
6.32852840423584
Big Bang Broderie
6.32852840423584
Big Bang Ferrari
6.32852840423584
Big Bang King
6.32852840423584
Big Bang Pop Art
6.32852840423584
Big Bang Tutti Frutti
6.32852840423584
Big Bang
6.32852840423584

The exact match has the lowest score.


texts:
I have a schema like:

[ad_id] .  [name] . [valueofname]
 1 .        name .    "brian"
 1 .        age  .    "23"
 2 .        job  .    "IT"
 2 .        name .    "Jack"

The row name contains multiple values: age, name, birthday, job, and I'd like to convert it into this:

[ad_id] .      [name]  .       [age] .              [birthday] .    [job]
[valueofad_id][valueofname] [valueofnameofage] [valueofnameofbirth] [valueofnameofjob]

Here is the query that I made; it's working but it's very slow because it is trying to make for each column its own table and JOIN it.

Do you have any suggestion or insights please? I am dealing with huge data volumes and trying to optimize fetching.

select users.ad_id,name.valueofname as name,
        age.valueofname as age,
        birthday.valueofname as birthday,
        job.valueofname as job
from users
left join users_details as name on (users.ad_id=name.ad_id)
left join users_details as age on (users.ad_id=age.ad_id)
left join users_details as job on (users.ad_id=job.ad_id)
left join users_details as birthday on (users.ad_id=birthday.ad_id)
group by ad_id,name.valueofname,age.valueofname,birthday.valueofname;


texts: pt-heartbeat showing "Host is blocked because of many connection errors"...but connections are well under limit

texts:
I'm running MySQL in a master-slave configuration. pt-heartbeat is showing "Host is blocked because of many connection errors"...but connections are well under limit (limit is 600 connections, MySQL workbench shows I'm hovering around 250, and I'm having no other problems connecting).

Here is my CLI, running from the master:

PTDEBUG=1 pt-heartbeat --host="mysql-slave" --user=root --ask-pass --database="percona" --monitor --master-server-id="14"

Any ideas? Should I run the suggested 'mysqladmin flush-hosts' as PTDEBUG suggests? I just don't feel that's the problem if I'm below the limit and not seeing this anywhere else.

Thx


texts: SQL Server - SQL Plan Management for baseline

texts:
I am a .Net developer. I have a database upgrade coming up (from SQL Server 2005 to SQL Server 2012). The database team will be handling the migration. But I have seen forums posts saying that after migration, the queries are taking too long to execute even though amount of data and number of users are almost same. And one main reason is change in execution plan between these servers. Reference: Performance problem after migration from SQL Server 2005 to 2012

So, I am considering possibility of storing the actual execution plans from my current production server itself. In Oracle, I could find a feature for baselining:-

SQL Plan Management (Part 1 of 4) Creating SQL plan baselines
Using SQL Plan Management

In SQL Server, what is the usual practice for achieving this?

Note: I referred Creating a baseline for SQL Server, yet it doesn't say anything about baselining execution plan


texts: DB2 LUW Timestamp differences in versions

texts:
Im new to DB2, my back ground is in MS SQL server 2000 thru 2012. I have a developer using

  Local DB DB2 LUW v9.7.500.4299 FIXPACK_NUM = 5

She is selecting some data from a table where the field is a Timestamp data type. She can select it and get a return using the following WHERE CRTD_DT_TM = '2014-11-19 18:00:00'

But if she does the same thing in the Prod version:

  DB2 LUW v.9.7.0.6 FIXPACK_NUM=6

she doesn't get anything in return. She actually has to add the milliseconds to the query:

Where CRTD_DT_TM = '2015-01-13 04:32:13.080000'

Is this an issue between versions? If so Is there a FIX pack that has addressed this?

Thanks in advanced!


texts: Table does not exist even if there is one

texts:
I have MySQL master-slave replication setup in Amazon EC2. For the sake of resilience, I have set up LVM on RAID 10 array, on both master as well as slave. I have been using  ec2-consistent-snapshot  for some time now to take the snapshot from the slave instance.

So, I tried to create the disks from those snapshots and attach them to a new MySQL installation. Since the new installation would not recognise the LVM and RAID setup, I reactivated the LVM and RAID in the new instance using the following commands.

Scan the disks for RAID array members:

mdadm --examine --scan <comprising disks>

Reactivate the RAID array:

mdadm -A -s

Reactivate the lvm:

vgchange -ay

Since snapshotting took the backup of the ibdata and iblogfiles, I removed them for the fresh installation. MySQL would start normal.

Help me out of this


texts: how do i recover an inconsistent backup after another full backup without restoring the new full backup?

texts: I have restored a 2 week old full inconsistent backup and recovering it daily using level 1 incremental backup. And then another full backup was taken. Can I still recover the old restored file using the incremental log taken after the new full backup?


texts: Is there anyway to know when a sql server instance was installed?

texts:
I have 100+ sql server instances (from sql 2005 to sql 2016) installed in my environment, is there a good way to find when an instance was initially installed?

I tried to check the [master] database creation date, and found (for sql 2012 onward at least) it is always Apr 8, 2003.

The only possible date is [msdb] creation date, which I see it changing for different sql server instances. (I am not sure about this is because I am not sure whether a service patch will change the [msdb] creation date?)

Is there any other reliable way that can indicate the sql server instance creation date? I am open to use of t-sql or powershell to figure it out.

Thanks in advance.


texts: Oracle SQL- special characters delimited strings

texts:
I have a following string in my table in the following format:
APPLE- GREEN- 40%; BANANA- YELLOW- 15%; APPLE- RED - 25%; GRAPES- GREEN - 20%
I want a output as:
Output
APPLE - 65%
Which is the sum of %quantity purchased of total.


texts: Why SQL Server replication related jobs do not show up under the jobs folder in SSMS?

texts:
I found some SQL Server replication-related jobs that do not show up under the jobs folder in SSMS. Can anybody explain why this is, please?

Thanks.


texts: Oracle equivalent to MS-Access character range/set

texts:
In MS-Access, I can use character ranges/sets in the where clause:

SELECT
    table_name
FROM
    all_tab_columns
WHERE
        table_name NOT LIKE 'A[0-9]*'
    AND table_name NOT LIKE 'D[0-9]*'
    AND table_name NOT LIKE 'S[0-9]*'

The character range/set is the range of numbers between 0-9. All tables that start with A, D or S, followed by a number, are eliminated from the query.

How can I do this in Oracle?


texts: Selecting distinct records having multiple properties

texts:
For following table data:

ID     | Property
=================
1      | Sweet
2      | Sweet
2      | Sour
3      | Hot

How can I get the ID's having Property Sweet AND Sour (in this example, 2)

Thanks.


texts: MySQL: What do we mean by 0 value in in_use column of show open tables statement?

texts:
If I run show open tables command, system return 73 records. But, in_use column value for every record is 0. What does this mean?

Are these tables available in cache but not used by any thread?


texts: Validation Errors in SSIS Package when columns are mapped correctly

texts:
I have a SSIS package that I am working on. As soon as I open the SSIS package I am given the following error:

  Validation error. Populate Organisations Table: Organisation Table destination: 'Organisation Table destination' has 2 input columns that are not mapped to upstream columns. Delete the unmapped input columns or map columns to them with the Resolve References editor. For example, one of the unmapped input columns is 'Organisation_OrganisationId_PimsCode'

But when I look at the mappings, the mapping exists, and it is looking fine!

Why is this happening? When I Rebuild solution, it builds fine with no errors, but then a minute or so later when I open a Data Flow Task (has red cross next to it) the error show up in the errors window.


texts: save/export PGAdmin server list configuration settings

texts: I have a ton of configured connections listed in the Server Groups, is there a way I can save this? not just save the passwords but the Server Group Settings


texts: Converting a varchar date into a date (dd/mm/yy)

texts:
I have a CSV file that has dates written like this: 250909,240909 and they get stored in a SQL Server database as varchars.

How do I get it to convert them into dates so that it gets displayed like this: 25/09/09, using the dd/mm/yyyy format?

At the moment I can get it to convert, but it converts it like this: 2025/09/09, meaning it is using the yyyy/mm/dd format which is wrong, seeing that the first column is the day and second the month and third the year.


texts:
I have the following query which casues some problems: it complains that ORA-00904: "TLEA"."TLSN_TRD_ID": invalid identifier even though TLSN_TRD_ID is clearly in the TLEA table. It is as if the aliasing doesn't work correctly. Both the RECON and TLEA table work separately but when I try to do a left join as below I get the error. Any suggestions what could be the problem?

SELECT * FROM

(
SELECT * FROM DF_RISK_SIT2_owner.RECON_RESULTS
WHERE (RS_ONLY = 1 or VERSION_MISMATCH = 1) AND  REC_SYSTEM = 'SUMMIT'
)RECON

LEFT JOIN

(
SELECT * FROM DF_RISK_CUAT_owner.TLEA
  INNER JOIN
        (SELECT  TLSN_TRD_ID,TLSN_LEG_ID, MAX(TLEA_COB_DT) AS MDate
            FROM    DF_RISK_CUAT_owner.TLEA
            GROUP BY TLSN_TRD_ID,TLSN_LEG_ID
        )MaxDate
            ON MaxDate.TLSN_TRD_ID = DF_RISK_CUAT_owner.TLEA.TLSN_TRD_ID
            AND MaxDate.MDate = DF_RISK_CUAT_owner.TLEA.TLEA_COB_DT
            AND MaxDate.TLSN_TRD_ID='C8112832LX'
)TLEA

ON RECON.RS_TRD_ID =       TLEA.TLSN_TRD_ID

Update:
It appears that the problem is related to duplicate column names:
When i replace the line below the LEFT JOIN with
SELECT MaxDate.TLSN_TRD_ID AS "A_A_TLSN_TRD_ID" and then join on TLSA.A_A_TLSN_TRD_ID it works. Strangely it doesn't work when I do on the join TLEA.MaxDate.TLSN_TRD_ID. Any idea why?


texts: Character Conversion Issue Importing From CSV File

texts:
After doing a load of CSV files, there are various words that are "written" to the database incorrectly.

Some examples:

Diã¡ria should be Diária
Crã©dito should be Crédito
Ligaã§ãµes should be Ligações
Usuã¡rio should be Usuário
Nãºmeros should be Números

Is there any way to translate the symbols to the correct characters?

I've done several tests with different collations and functions that could search the internet, but without success.


texts: Query PostgreSQL 9.0 table on foreign key value?

texts:
Noob-type question:

Say I have two tables, candies and colors. The colors table holds just code/value pairs like 01 yellow, 02 blue, 03 green, etc. and is referenced in the candies table by code.

How do I query the candies table for all blue candies without an explicit join? For two years now I've been writing these queries with joins, like:

SELECT * FROM candies a JOIN colors o ON(a.color_code = o.color_code)
WHERE o.color_value = 'blue';

I'm certain every time that I'm bringing a gun to a knife fight, but my googling has been fruitless.

Thanks.


texts:
I am unable to execute batch files to copy backups from SQL Server 2008 R2 hosted on Windows 2008 R2 to a file share.

Error:

  Access denied

SQL Server Agent service has full control on the soure and destination folders.

This only happens in Windows Server 2008 R2 on my production environment, suspecting additional security controls on Windows Server 2008 causing this issue.


texts: DB2 LOAD: Poor performance while loading table with large BLOBs

texts:
I need help regarding LOAD-command with BLOBs.

I notice very poor performance while loading a table which contains large BLOBs.
While tracing the OS, I only have about 15MB/sec I/O-speed.
But in other cases I have normal I/O-rates > 200MB/sec. (e.g. creating DB-backups, creating tablespaces, copy files etc.).

I already tried to use different extent-sizes, page-sizes and some LOAD-parameters like "DISK_PARALLELISM", "SORT BUFFER", "DATA BUFFER", "SAVECOUNT" etc. to improve LOAD-performance but I was not able to speed up the LOAD.
The target table is empty and has no indices defined.
Source and target are located on separate RAID-volumes with dedicated physical drives.

Any hints to improve LOAD performance or to further investigate the problem are very welcome.

Thank you in advance.

Ronny

My setup:
- SLES 11 SP2
- DB2 9.7 FP8
- Raid:
    Source: 2 dedicated Disks, Raid-0 (Striping) with 64 KB stripe-size
    Target: 2 dedicated Disks, Raid-0 (Striping) with 64 KB stripe-size
- File-System: EXT3
- Tablespace-Definition:
    CREATE LARGE TABLESPACE LOB IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY DATABASE USING (FILE 'xxx' 20000000) PREFETCHSIZE AUTOMATIC BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY OFF;

- LOAD-Definition:
    LOAD FROM xxx OF IXF LOBS FROM xxx MODIFIED BY lobsinfile METHOD N (ID, CONTENT, SEGMENTGROUPID, SEGMENTNUMBER) SAVECOUNT 1 ROWCOUNT 250 MESSAGES /xxx/CONTENT.log REPLACE INTO CONTENT (ID, CONTENT, SEGMENTGROUPID, SEGMENTNUMBER) NONRECOVERABLE;


texts: Error When Trying to Run MDW Data Collector

texts:
I have SQL Server Management Data Warehouse (MDW) set up across multiple instances, all in the same way with the same accounts and permissions.

On one server I get an error when trying to run the data collectors. The error states that SSIS packages for the data collectors are missing. True enough, when I check SELECT * FROM msdb.dbo.sysssispackages, 8 packages relating to collecting and uploading are missing.

In addition, system logs showed the following error:

  The application-specific permission settings do not grant Local
  Activation permission for the COM Server application with CLSID
  {FDC3723D-1588-4BA3-92D4-42C430735D7D}  and APPID
  {83B33982-693D-4824-B42E-7196AE61BB05}

Does anyone know a fix for this? It is really odd, since all my other instances are running the data collectors without issues. I am running SQL Server 2012 and my host MDW database is on a remote server. But again, all other MDW instances collect and upload without error.

I saw SQL Server 2012 Integration Services and DCOM Permissions by John Veazey, which has a common fix for people but it didn't work for me. The sys log error went away, but the error when running data collection persists.


texts:
I am trying to design a database storing data about several Zoos. I just want to ask if my design is good. I know pretty well SQL and queries, but I'm not an expert at design. Requirement:
You will manage zoos, animals, food, visitors and visits. Each zoo has an id, an administrator, a name and several animals. An animal has an id, a name and date of birth; it can eat various foods, the latter consisting of an id and a name. The system stores the daily quota(integer number) for each animal and food, e.g., an animal A1 <food F1, 10; food F2, 5>; animal A2 <food F2, 1; food F5, 2>. A visitor is characterized by a personal number(an id), name and age. A visitor can visit several zoos. Such a visit is defined by a unique identifier, a day, the paid price, the visitor's personal number and the zoo id.
This is how I've done the design:
CREATE TABLE Zoo(
    id INT PRIMARY KEY IDENTITY(1,1),
    administrator VARCHAR(30),
    name VARCHAR(40),
);

CREATE TABLE Animal(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(30),
    dob DATE,
    zoo_id INT REFERENCES Zoo(id)
);

CREATE TABLE Food(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(25)
);

CREATE TABLE DailyQuota(
    id INT PRIMARY KEY IDENTITY(1,1),
    animal_id INT REFERENCES Animal(id),
    food_id INT REFERENCES Food(id),
    quantity INT
);

CREATE TABLE Visitor(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(40),
    age TINYINT
);

CREATE TABLE Visit(
    id INT PRIMARY KEY IDENTITY(1,1),
    zoo_id INT REFERENCES Zoo(id),
    visitor_id INT REFERENCES Visitor(id),
    day DATE,
    paid_price SMALLINT
);

Want to hear any opinions

I also had to create a view that shows the ids of the zoos woth the smallest number of visitors. This is how I have done it:

GO
CREATE OR ALTER VIEW view_smallestCountVisitors
AS
    SELECT t1.zoo_id
    FROM (SELECT v.zoo_id, COUNT(v.zoo_id) MYCOUNT
            FROM Visit v
            GROUP BY v.zoo_id) t1
    WHERE t1.MYCOUNT IN (
        SELECT MIN(t2.MYCOUNT)
        FROM (SELECT v.zoo_id, COUNT(v.zoo_id) MYCOUNT
                FROM Visit v
                GROUP BY v.zoo_id) t2
        );
GO

It works fine but I am just wondering if this can be done in a shorter way.


texts: pg_restore error from pgadmin3 - Postgresql

texts:
i have taken a backup of a postgresql db from a system1 using pgadmin3 tool.  The backup file created is of extension  ".backup"

I am trying to restore it on an another system2 installed with postgresql 9.0 and pgadmin3 1.8
but iam getting the error

  could not execute query: ERROR: unrecognized configuration parameter
  "lock_timeout" Command was: SET lock_timeout = 0;

configuration of both the systems

system1
win7-64bit
postgresql 9.0.13-1 - 64bit
pgadmin 1.8

system2
win7-32bit
postgresql 9.0.13-1 - 32bit
pgadmin 1.8


texts: Design star schema for relatonal database

texts:
I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema.
in this example i assume that the fact table is (order-orderitem-book)
And the measures is (category-customer-time)
My question is about book author how can we put is as measure? Is it allowed to put many to many relationship in star schema ??
And if i am wrong how to draw star schema to this relatonal db?


texts: Cannot drop materialized view privileges from user

texts:
Legacy database to me, pg version 9.6.12 running on a linux ami.

I've a user ('example_user') that I would like to drop. I've confirmed that 'example_user' owns no objects. However, when I run DROP USER 'example_user'; I get this (I've truncated for brevity) error:

exampledb=# drop user 'example_user';
ERROR:  role 'example_user' cannot be dropped because some objects depend on it
DETAIL:  privileges for materialized view "example-prod".view_foo
privileges for materialized view "example-prod".view_bar
privileges for materialized view "example-dev".view_foo
privileges for materialized view "example-dev".view_bar

I've tried about 15 different revocation statements to attempt to kill the privileges, and in some cases Postgres doesn't even complain. For example:

exampledb=# revoke all privileges on all tables in schema public from "example_user";
REVOKE

# OR

revoke all privileges on "example-prod".view_foo from "example_user";
REVOKE

I've tried countless different revokes on the separate schemas, views, databases and nothing seems to work. The privileges are not removed and I I get the same errors when I attempt to drop the user. Not sure if it's in any way related, but pg complains if I don't double quote the user.

How can these specific, or indeed all privileges, be removed from this user? Are there any other strategies for dropping this user if I don't need to worry about preserving object or privileges?

Thanks!


texts: Are there any outstanding critical security issues for Sql Server 2000?

texts:
Are there any outstanding (unpatched) security issues for Sql Server 2000?

This isn't really for me: I moved on from Sql Server 2000 long ago. However, when answering questions here and (more likely) on Stack Overflow, I'm in the habit of leaving a comment like the one at this question whenever coming across a Sql Server 2000 question. If you don't want to follow the link, it goes something like this:

  Sql Server 2000 is end of life. It no longer gets any patches... not even critical security patches. It's irresponsible to continue using it. Convincing management to upgrade is job #1.

Aide from it being, well, Sql Server 2000, how much an issue is this really? In the year-plus since Sql Server 2000 went EOL, has anything big come up I could point to as a more concrete issue?

I'd look at official documentation, but as this is effectively abandonware I haven't found anything published directly by MS.


texts: first row and last row fields value from where data changed

texts:
Assume I have a table like this:

I'm trying the have a output like following picture

How best to achieve this?


texts: Multiple Entries Return Lowest Data With Lowest Date

texts:
We had a few spreadsheets get imported multiple times into a database so we now have duplicate data (which of course had primary keys etc been assigned this would not have happed...)

However - the issue at hand is I need a way to return the data associated with the smallest (oldest) date for each PN in the database.  Below is a sample of the data and the structure.  I tried to use Top 1 but I realized there was no rhyme or reason to what SSMS deemed as the 1st entry.

What would a query be for the syntax below to return the oldest date - expected returned results:

  Number One 111111   2014-01-01
    Number Two 2222     2014-02-22

Sample DDL ->

  Declare @Test Table (pn varchar(200), ECN int, d1 date)
  Insert Into @Test (pn, ECN, d1) Values
  ('Number One', '111111', '2014-01-01')
  ,('Number One', '111111', '2014-01-31')
  ,('Number One', '111111', '2014-02-14')
  ,('Number Two', '2222', '2014-02-22')
  ,('Number Two', '2222', '2014-02-25')


texts: How to turn off header only in psql (postgresql)

texts:
I'm using PostgreSQL 9.1.X

I am trying to build psql script to print results without a header but including a footer.

http://www.postgresql.org/docs/9.1/static/app-psql.html

From the document above

\pset tuples_only

will turn both header and footer off. and

\pset footer off

will turn footer off only.

Is there a way in psql to turn the header off and keep the footer on?

I know there are many ways to work around this issue using shell/perl/whatever text tool you like, however I am wondering why there is a config for the footer but not one for the header?

id <--this line I don't want
---- <-- this line I don't want either
 1  <-- this line, yes
(1 row) <-- yes, I want it!


texts: Prevent duplicate record counts in one to many query

texts:
I'm trying to get a count of ethnicity for a teachers students. My first table contains the student number and ethnicity. The second table contains teacher id's, student numbers, course numbers, and etc... Each student can have a teacher more than 1 time.

Here's my query that is returning duplicate counts:

SELECT DISTINCT Count(StudentsSDRC.Ethnicity) AS CountOfEthnicity,
                StudentsSDRC.Ethnicity
FROM StudentsSDRC
    LEFT JOIN TeacherStudents
        ON StudentsSDRC.StudentNum = TeacherStudents.StudentNum
WHERE (((TeacherStudents.teacherDCID)=43652))
GROUP BY StudentsSDRC.Ethnicity

Any help would be great.


texts: MySQL trigger: Compare value in another table

texts:
I have the following trigger (generated using PHP via mysqli)...

DROP TRIGGER IF EXISTS post_queue_insert;
CREATE TRIGGER post_queue_insert AFTER INSERT ON posts
FOR EACH ROW BEGIN
    IF (NEW.post_type != 'revision' AND NEW.post_status != 'auto-draft') THEN
        INSERT INTO event_queue (action_id, action_do, action_key, action_value, action_event, action_timestamp, sync_complete, SITE_ID) VALUES (NEW.ID, 'post', NEW.post_type, NEW.post_status, 'insert', UNIX_TIMESTAMP(now()), 0, 1);
    END IF;
END;

What I can't figure out is how to check if the 'post_type' value exists in another table and if it doesn't then insert it.

?


texts:
Is it possible to apply MySQL 5.1 bin log to MySQL 5.5.

I am assuming that bin log is portable between two machines (same OS, Architechture but different machines running same version of MySQL)


texts: Need help with relationships in MS Access

texts:
I am in need of some assistance.  I'm fairly new at access & dbs, and have managed to get a bit accomplished, but have run into a wall.  I'm not sure it is my inexperience or if access cannot accomplish what I want (or if it is just not easy...)

Anyhow, I need to create a couple relationships.  I’ve got a few, but now I’m getting in too deep for my level of experience, and can’t figure out how to get it to do what I want.

Basically I need to record the following:

•   Base/Parent Contract (BC)

   o    BC DOES have Line Items (CLINS)

   o    BC CAN have Modifications (Mods)

   o    BC CAN have Delivery Orders (DO)

•   Delivery Orders (DO)

   o    DO DOES have CLINS

   o    DO CAN have Mods

Example:

Contract: F12345-D-17-0001

 Delivery Order 0001

      CLIN 0001AA

      CLIN 0002AA

 Delivery Order 0002

      CLIN 0001AA

      CLIN 0002AA

      CLIN 0003AA

      Modification P00001

      **Modification P00002**

 Modification P00001

 Modification P00002

Each one of these bullets has its own set of data.  The contract number is unique by itself, but its number actually flows down to each of the others, as they get appended to it….  For example, the BOLD Mod above would actually be listed as “F12345-17-D-0001-0002-P00002”.  Under each base contract the Modifications, CLINs and Delivery Orders all basically start from 1 and increase, so you can see that there will be many of those records with the same number, so it is a different ID used as the key for each of those.

Anyhow, I have been able to establish these relationships:

 Subordinate DO to Parent Contract

 Subordinate Modification to Parent Contract

 Subordinate CLIN to Parent Contract

 POC Contact Info to Base Contract

What I am having problems doing is associating:  (I think because they are 2nd layer subordinate to the Base Contract)

 CLINs that go to Delivery Orders and

 Modifications that go to Delivery Orders

It all hinges off the Base/Parent Contract and cascades down from there.

Here is my relationship table.

And this is what my form looks like - how I want the info portrayed in the end if it helps.


texts: Why is CDC capture failing due to a missing dbo principal

texts:
I am having a problem running the CDC change capture job against some of my test databases.

The error message I'm getting is the following:

  Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. [SQLSTATE 42000] (Error 15517).

How can I fix this?


texts:
Could you please help with why am I getting this sys.transmission_queue.transmission_status message after restoring from a snapshot?

  The broker in the sender's database is in single user mode. Messages cannot be delivered while in single user mode.

This is what I've tried without success:

Drop down to single user mode
Restore from a snapshot WITH NEW_BROKER option
Return to multiuser mode WITH NO_WAIT
Check sys.databases.is_broker_enabled and sys.service_queues.is_receive_enabled
Send a message which will get stuck in the transmission queue complaining about single user mode.

Is there another DMV or catalog to query to find the status of the service broker?

Thank you.


texts: Large MySQL table that need to index some fields?

texts:
I would like to know some hints about how to index some columns of a MySQL table that has about 28 million rows.

Maybe I could recreate the table indexing it properly, then import these rows?

Could someone help me with this please.


texts: SSMS cannot determine if remote computer is connected to domain

texts:
I've recently set up a new server with an SQL instance running on it (2017), I am able to connect to the server remotely using SSMS, but when I try to add a new login, I receive the attached error. Both machines are on the same domain.

I have already done some prior research and opened up ports 5002, 5001, 135 and 88 in the below configuration.

I have already confirmed that it is an issue with the firewall, as turning off the domain firewall allows access.

Am I missing something?

In case it's relevant, the server is hosted on Azure


texts: Mysql Command required for getting values from my.cnf

texts:
I am new to MySQL DBA, i have a doubt, Is there any command for finding my.cnf configuration file variables and its respective value which is mention in my.cnf file in mysql prompt itself. like we will see the global variable which are there in mysql in below format
mysql> show variable like'xxxx';

Similar kind of command is there for seeing all parameter present in my.cnf.


texts: MYSQL cluster issue: shutdown data node correctly breaks replication

texts:
I faced issue on mysql cluster (5.7.28), i shuted down properly vm6 (data nodes) and the mysql replication was broken, i'm trying to make the link bettwen the datanode down and the replication broken but i still can't find the reason (below the relevant), is there someone who can help me to find the link
Slave: Got error 4009 'Cluster Failure' from NDB Error_code: 1296 [Warning] Slave: Can't lock file (errno: 157 - Could not connect to storage
engine) Error_code: 1015
Error running query, slave SQL thread aborted. Fix the problem, and
restart the slave SQL thread with "SLAVE START".


texts: High CPU Utilization for Mongodb ReplicaSet Secondary Nodes

texts:
I am using MongoDB server version: 3.6.4 (WiredTiger storage engine) with 3 node replica set on AWS r4 large machines.

Sharing the CPU Utilization graph of all the nodes

Primary (with .33%)

Secondary 1 (with ~99%)

Secondary 2 (with ~99%)

I tried to run

  db.currentOp({"secs_running": {$gte: 3}})

I can see only

{
    "inprog" : [
        {
            "host" : "XXXXXXXXXXXXX",
            "desc" : "WT RecordStoreThread: local.oplog.rs",
            "active" : true,
            "currentOpTime" : "2018-05-23T12:41:42.272+0000",
            "opid" : 18,
            "secs_running" : NumberLong(16636),
            "microsecs_running" : NumberLong("16636047730"),
            "op" : "none",
            "ns" : "local.oplog.rs",
            "command" : {

            },
            "numYields" : 0,
            "locks" : {

            },
            "waitingForLock" : false,
            "lockStats" : {
                "Global" : {
                    "acquireCount" : {
                        "r" : NumberLong(1),
                        "w" : NumberLong(1)
                    }
                },
                "Database" : {
                    "acquireCount" : {
                        "w" : NumberLong(1)
                    }
                },
                "oplog" : {
                    "acquireCount" : {
                        "w" : NumberLong(1)
                    }
                }
            }
        }
    ],
    "ok" : 1,
    "operationTime" : Timestamp(1527079298, 1),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1527079298, 1),
        "signature" : {
            "hash" : BinData(0,"XXXXXXXXXXXXX"),
            "keyId" : NumberLong(0)
        }
    }
}

I tried to restart the secondary machines, but after sometimes CPU utilisation reaches the peak.

I have set readPreference as secondaryPreferred

Thanks in advance


texts: "Aggregation" by hour when data may be much longer or much shorter

texts:
I have duration data which looks like this:

deviceid                          StartRange            EndRange            Duration    eventname   value
57a6f2499aaaeb1580f1ca36    2016-08-18 13:37:02.020 2016-08-18 13:38:22.020 80          Engine  2
57a6f2499aaaeb1580f1ca36    2016-08-18 13:38:22.020 2016-08-18 14:14:06.380 2144        Engine  1
57a6f2499aaaeb1580f1ca36    2016-08-18 14:14:06.380 2016-08-18 14:15:26.380 80          Engine  2
57a6f2499aaaeb1580f1ca36    2016-08-18 14:15:26.380 2016-08-18 14:16:06.380 40          Engine  1
57a6f2499aaaeb1580f1ca36    2016-08-18 14:16:06.380 2016-08-19 00:00:00.000 35034       Engine  2
57a6f2499aaaeb1580f1ca36    2015-08-18 15:02:41.840 2015-08-18 15:04:01.840 80          Engine  2
57a6f2499aaaeb1580f1ca36    2015-08-18 15:04:01.840 2015-08-18 15:04:41.840 40          Engine  1
57a6f2499aaaeb1580f1ca36    2015-08-18 15:04:41.840 2016-08-18 13:37:02.020 31617141    Engine  2

(I basically log the data every time a state changes, so for each point in time there can be only 1 value.)

On this data I need to allow users to get the total duration per value on any given range and on any given interval (minutely, hourly, daily... yearly).

Narrowing the range is not a problem, however the interval I can't figure out.

The problem is that 1 row can be turned into X datapoints in that query but how to open the data and then "bucket it" and aggregate it?


texts:
When log shipping between a primary database and a secondary database, does the secondary database have to be set to read-only?

By read-only, I am not talking about the idea of only reading from the database. I am talking about the database having a specific status of Read-Only or Standby/Read-Only.

I cannot find anything that states that this is required in the Log Shipping Overview and other related pages. This is causing an issue as I am unable to add any indices to a read-only database.


texts: How to selecting data from two queries using union?

texts:
I have three tables. I am trying to taking the data with two times interval after then subtracting from first two second for every possible row and getting the ledger report.

Solution is given by: jkavalik
Solved MySql Fiddle

My Problem
MYSQL FIDDLE

My simple query run and gives me some data:

  SELECT lh.group_id,lh.opening_balance,lh.ledger_head_id,sum(dc.debit)
    AS sum_dr,sum(dc.credit) AS sum_cr FROM ac_debit_credit dc
    INNER JOIN ac_transaction_master tm ON tm.tran_id=dc.tran_id
    INNER JOIN ac_ledger_head lh ON lh.ledger_head_id=dc.ledger_head_id
    WHERE (tm.transaction_date BETWEEN '2015/09/01' AND '2015/10/10') AND lh.group_id IN('8', '9', '56', '10', '11', '12', '13',
    '14') group by dc.ledger_head_id
UNION
   SELECT lh.group_id,lh.opening_balance,lh.ledger_head_id,sum(dc.debit) AS sum_dr2,sum(dc.credit) AS sum_cr2 FROM ac_debit_credit dc
    INNER JOIN ac_transaction_master tm ON tm.tran_id=dc.tran_id
    INNER JOIN ac_ledger_head lh ON lh.ledger_head_id=dc.ledger_head_id
        WHERE (tm.transaction_date<'2015/09/01') AND lh.group_id IN('8', '9', '56', '10', '11', '12', '13',
    '14') group by dc.ledger_head_id

But I need to execute this:

SELECT PF.opening_balance,PF.sum_dr,PF.sum_cr,PF.sum_dr2,PF.sum_cr2,(PF.sum_dr-PF.sum_cr) as cv,(PF.sum_dr2-PF.sum_cr2) as cv2 FROM (
SELECT PR.* FROM(
SELECT lh.group_id,lh.opening_balance,lh.ledger_head_id,sum(dc.debit) AS sum_dr,sum(dc.credit) AS sum_cr FROM ac_debit_credit dc
    INNER JOIN ac_transaction_master tm ON tm.tran_id=dc.tran_id
    INNER JOIN ac_ledger_head lh ON lh.ledger_head_id=dc.ledger_head_id
        WHERE (tm.transaction_date BETWEEN '2015/09/01' AND '2015/10/10') AND lh.group_id IN('8', '9', '56', '10', '11', '12', '13',
    '14') group by dc.ledger_head_id
    ) AS PR
 UNION
   SELECT PV.* FROM(
   SELECT lh.group_id,lh.opening_balance,lh.ledger_head_id,sum(dc.debit)
   AS sum_dr2,sum(dc.credit) AS sum_cr2 FROM ac_debit_credit dc
    INNER JOIN ac_transaction_master tm ON tm.tran_id=dc.tran_id
    INNER JOIN ac_ledger_head lh ON lh.ledger_head_id=dc.ledger_head_id
    WHERE (tm.transaction_date<'2015/09/01') AND lh.group_id IN('8', '9', '56', '10', '11', '12', '13',
    '14') group by dc.ledger_head_id
  ) AS PV GROUP BY ledger_head_id
  ) AS PF;

Where am I wrong in my second query ?


texts: Oracle11.2g post-installation problem on Oracle Linux kernel

texts:
After installation of Oracle 11.2g in silent mode on the Oracle Linux kernel, I have the following error:

[u01/app/oracle/product/11/bin] $ sqlplus

sqlplus error while loading shared libraries: libsqlplus.so: cannot open shared
object file: No such file or directory

However, the shared object seems to be setup correctly:

[/u01/app/oracle/product/11/lib] $ ls -lrt /u01/app/oracle/product/11/lib | grep sqlplus
-rw-r--r--. 1 oracle oinstall   1475820 Aug  1  2009 libsqlplus.a
-rwxr-xr-x. 1 oracle oinstall   1218075 Aug  1  2009 libsqlplus.so

The user is oracle:

[oracle ~]$ id oracle
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba)

The environment variables are:

[oracle ~]$ env
USER=oracle
LD_LIBRARY_PATH=/u01/app/oracle/product/11/lib
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
PATH=/usr/lib/qt-3.3/bin:/usr/local/bin
    :/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin: /home/oracle/bin
    :/u01/app/oracle/product/11/bin
HOME=/home/oracle
ORACLE_HOME=/u01/app/oracle/product/11

kernel version:

2.6.32-300.3.1.el6uek.i686

*UPDATE FOR ANSWER *

[oracle@orcus lib]$ ldd /u01/app/oracle/product/11/bin/sqlplus
    linux-gate.so.1 =>  (0xb771f000)
    libsqlplus.so => /u01/app/oracle/product/11/lib/libsqlplus.so (0xb766e000)
libclntsh.so.11.1 => not found
    libnnz11.so => /u01/app/oracle/product/11/lib/libnnz11.so (0xb7415000)
    libdl.so.2 => /lib/libdl.so.2 (0x4c5dc000)
    libm.so.6 => /lib/libm.so.6 (0x4c616000)
    libpthread.so.0 => /lib/libpthread.so.0 (0x4c5e3000)
    libnsl.so.1 => /lib/libnsl.so.1 (0x4caef000)
    libc.so.6 => /lib/libc.so.6 (0x4c44a000)
    /lib/ld-linux.so.2 (0x4c424000)
    libclntsh.so.11.1 => not found

* after chmod /u01/app/oracle/product/11/lib/libclntsh* *

$ ldd /u01/app/oracle/product/11/bin/sqlplus
    linux-gate.so.1 =>  (0xb7831000)
    libsqlplus.so => /u01/app/oracle/product/11/lib/libsqlplus.so (0xb7780000)
    libclntsh.so.11.1 => /u01/app/oracle/product/11/lib/libclntsh.so.11.1 (0xb5a7b000)
    libnnz11.so => /u01/app/oracle/product/11/lib/libnnz11.so (0xb582e000)
    libdl.so.2 => /lib/libdl.so.2 (0x4c5dc000)
    libm.so.6 => /lib/libm.so.6 (0x4c616000)
    libpthread.so.0 => /lib/libpthread.so.0 (0x4c5e3000)
    libnsl.so.1 => /lib/libnsl.so.1 (0x4caef000)
    libc.so.6 => /lib/libc.so.6 (0x4c44a000)
    libaio.so.1 => /lib/libaio.so.1 (0x4c446000)
/lib/ld-linux.so.2 (0x4c424000)

Any idea why SQLPlus is complaining about libsqlplus.so?


texts: SQL Server 2017 Distributed AG's manual failover

texts:
We have configured SQL Server 2017 SQL Distributed Always-ON from DC1 to DC2. Can you please review below steps for manual failover from DC1 to DC2 and vice-versa. Please add steps If i missed any steps.

Cluster1 (Production):
DC1: DB1 (Global Primary), DB2

Cluster2 (DR):
DC2: DB3 (Forwarder), DB4

Perform Manual failover from DC1 to DC2 (make DC2 as Production and DC1 as DR):

Step 1: Execute below command on DC1-DB1 and DC2-DB3. Is this correct?

ALTER AVAILABILITY GROUP [distributedag]
 MODIFY
 AVAILABILITY GROUP ON
 'ag1' WITH
  (
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
  ),
  'ag2' WITH
  (
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
  );

Proceed to step 2 after the availability group synchronization_state_desc is SYNCHRONIZED.

Step 2: Execute below command on DC1-DB1. Is this correct?

ALTER AVAILABILITY GROUP distributedag SET (ROLE = SECONDARY);

Test the failover readiness and proceed with Step 3

Step 3: Fail over from the primary availability group to the secondary availability group. Execute below command on DC2-DB3. Is this correct?

ALTER AVAILABILITY GROUP distributedag FORCE_FAILOVER_ALLOW_DATA_LOSS;

Step 4: Change back to ASYNCHRONOUS_COMMIT. Execute below command on DC2-DB1. Is this correct?

ALTER AVAILABILITY GROUP [distributedag]
 MODIFY
 AVAILABILITY GROUP ON
 'ag1' WITH
  (
  AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
  ),
  'ag2' WITH
  (
  AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
  );

After executing above steps, DC2 is primary (prod) and DC1 is secondary (DR) as mentioned below. Now make DC2 as DR and DC1 as Prod (roll back)

DC2 (production): DB3 (primary global),DB4

DC1 (DR): DB1 (Forwarder), DB2

Step 1: Execute below command on DC2-DB3 and DC1-DB1. Is this correct?

ALTER AVAILABILITY GROUP [distributedag]
 MODIFY
 AVAILABILITY GROUP ON
 'ag1' WITH
  (
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
  ),
  'ag2' WITH
  (
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
  );

Proceed to step 2 after the availability group synchronization_state_desc is SYNCHRONIZED.

Step 2: Execute below command on DC2-DB3. Is this correct?

ALTER AVAILABILITY GROUP distributedag SET (ROLE = SECONDARY);

Step 3: Fail over from the primary availability group to the secondary availability group. Execute below command on DC1-DB1. Is this correct?

ALTER AVAILABILITY GROUP distributedag FORCE_FAILOVER_ALLOW_DATA_LOSS;

Step 4: Change back to ASYNCHRONOUS_COMMIT. Execute below command on DC2-DB1. Is this correct?

ALTER AVAILABILITY GROUP [distributedag]
 MODIFY
 AVAILABILITY GROUP ON
 'ag1' WITH
  (
  AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
  ),
  'ag2' WITH
  (
  AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
  );


texts: TSQL: how to define efficient index on partitioned panel-data table?

texts:
The underlying table piddt is a panel-data structure containing three fields: (pid, dt, attribs). The defined primary key is (pid, dt).

The number of rows is around 6 Billion, and the table is partitioned by dt (which is month) into around 350 segments. Also table pid stores all unique pids with index defined (a single field).

I would like to extract the first record of each pid, and below is the query:

SELECT [first].*
FROM pid
CROSS APPLY (
    SELECT TOP 1 pid, dt, attribs
    FROM piddt
    WHERE pid = pid.pid
    ORDER BY dt
    ) AS [first];

It runs very slow (taking 5-6 hours), and my suspicion is that it does not take advantage of the index and table partition.

Another query is tried as well, and it is just as slow:

WITH CTE
AS (
    SELECT *
        ,row_number() OVER (PARTITION BY pid ORDER BY dt) AS rowid
    FROM piddt
    )
SELECT *
FROM CTE
WHERE rowid = 1;

My question is bit generic:

what is the best practice to handle such panel data?

The above is actually a common construction. I searched yet didn't find a good solution for now.


texts: Optimize finding newest x rows from category

texts:
The query for this problem is fairly simple and has been answered many times before, I however have problems with optimizing it.

Say I have a table with the columns timestamp(bigint), category(int), and value(int). I need to efficiently find the top x rows sorted by timestamp descending where category matches.

I currently do this via the following query:

SELECT   *
FROM     table
WHERE    category=315
AND      timestamp > 1488983069581
ORDER BY timestamp DESC
LIMIT 10

To speed up the query I use an index:

ADD INDEX `fetch` (`category` ASC, `timestamp` ASC);

This speeds up my query significantly.

Sadly this is still pretty slow. My table has a few million entries containing about 300 different categories and a lot of entries per category. ~100 fetches take about a second on my above average home desktop. It is of course a fairly heavy operation on some millions of rows, but I would not expect it to take over a second. It is not the overhead of the requests as a smaller table returns instantly.

I wonder if I just underestimate the time to fetch or that I do something wrong. From a computer science standpoint this is a very easy query where a lot of preprocessing can be applied (on insertion). For example, using a hash table for the category leading into a descending sorted list would give pretty much constant search time. My MySQL knowledge is limited though, advice would be very much appreciated.

Version: 10.1.16-MariaDB (32-bit)

I would say I have about 1GB RAM available for MySQL. If 1GB is very little, I could probably add more, up to 4GB. The thing is I would like to save some for the application that uses the queries.

innodb_buffer_pool_size 16MB.

Create table

delimiter $$

CREATE TABLE `data_f32` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category` int(10) unsigned NOT NULL,
  `timestamp` bigint(20) unsigned NOT NULL,
  `value` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `data_f32_foreign_data_id_idx` (`category`),
  KEY `fetch` (`category`,`timestamp`) USING BTREE,
  CONSTRAINT `data_f32_foreign_category` FOREIGN KEY (`category`) REFERENCES `config_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13648145 DEFAULT CHARSET=latin1$$

Execution plan

id | select_type | table    | type  | possible_keys                        | key    | key_len | ref      | rows  | Extra
1  | SIMPLE      | data_f32 | range | "data_f32_foreign_category_idx,fetch" | fetch  | 12      | NULL     | 94642 | "Using where"

DB info


texts:
This other Question has Answers about viewing and editing settings in postgresql.conf file using pgAdmin 4. But those Answers did not mention editing the pg_hba.conf file used for authentication of users.

➥ How to use pgAdmin 4 for editing the pg_hba.conf file?


texts: "current security context is not trusted" for job running as a SQL login accessing linked server

texts:
I'm running into the error "Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)" while trying to run a stored procedure from a job, and the stored procedure queries against a linked server. The job is configured to run as a SQL login which I'll refer to as user1, and I have given user1 permission and ownership in every possible place.

Here is how the job is set up:

A SQL Server Agent job whose owner is user1
A step in the job is configured to "Run as user" user1 on the Advanced tab (The "Run as" list on the General tab is a blank list -- not sure if that's a factor)
The step calls a stored procedure which user1 has access to execute
The stored procedure is defined with WITH EXECUTE AS 'user1'
The linked server has a local/remote mapping for user1 with the remote user and remote password entered. The username and password are the same on both servers. The "For a login not defined in the list above, connections will" option is set to "Be made using the login's current security context". I've also tried setting this option to "Be made using this security context", with user1 and its password repeated.
user1 has the appropriate permissions to query on the remote server.
I even made user1 a sysadmin on the local server

Despite all of these, I still get the access error. I must be missing something somewhere... Any ideas?


texts: RMAN backup copy - read once and write many?

texts:
I am trying to find out what happens in this scenario:

BACKUP COPIES 2

What actually happens in the BACKUP COPIES above?  Does Oracle RMAN read the data files each time for each separate copy?  Or does it read only once and write as many copies as specified?


texts: Tablestructure for fast inserts/deletes with foreign keys

texts:
Current Situation
We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product.
Example:

id | competitors | some infos about competitor        | product
---------------------------------------------------------------
1  | C_1         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1
2  | C_2         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1
3  | C_3         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1
4  | C_1         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_2
5  | C_4         | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_2

Every 30 minutes we read in the new competitors. To do so we first delete everyone with the specific product (P_1) and insert afterwards the new ones. So there will never be a competitor in our DB, which isnt at the moment listed for this product.

When we got the new infos, we do some kind of research with the products.
(We also have a table called p with ca. 100,000 rows. In that table we store the infos about the products.)

So back to the research process. We run through every product an have to get the competitors with their infos. For this purpose we have do do a mysql request like SELECT * FROM c WHERE product LIKE "P_1";. Not to say that this is not very performant.

My Goal
I want to achieve, that with only one query i got the product information from p an also the competitor ones from c where the product id is the same.
So that these infos get stored in an array like this:

[0]['id'] => '2'
[0]['product_identifier'] => 'P_1'
[0]['c'][0]['competitors'] => 'C_1'
[0]['c'][1]['competitors'] => 'C_2'
[0]['c'][3]['competitors'] => 'C_3'

Possible Solution
To get the needed information with just one query, i came up with this solution.

In table c the product row contains a foreign key which links to the p table. That would be very easy to realise. But I am asking myself if it would be possible to delete c or p rows easaly. I never worked that intensive with foreign keys - seemed to me always a bit error generating.

And I also wonder if I could make a join that easy, cause I want from p the related competitors and not the other way around. You know what I mean? Not sure if I can do that this way. And if I could, how to tread this foreign keys. What do I have looking for in order to not get messed up with an unclean database.

Example
We will use our table c from above, where c.product stores the foreign key to our table p.product_identifier. Like this:

id | product_identifier | some infos about the product
---------------------------------------------------------------
1  | P_1                | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
2  | P_2                | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3  | P_3                | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I am not sure how this works out or if it's even a good idea, but how has my query look like? If i want to SELECT every product from p and also their competitors from c?


texts: SQL Server Agent & sp_send_dbmail failing on permissions

texts:
I have set up a SQL Server Agent job to call sp_send_dbmail with a very simple select on a certain table in a certain database. Within the properties > permissions of the target database, the Database Role 'public' has select permission. The job runs fine, the email arrives, all good.
The problem is, if I turn off the worryingly generous 'public' select permission and try to add the SQLServerAgent user (which is the user the agent is running under and the user that owns the agent job) and give them select permissions, the job fails with this error message:

Failed to initialize sqlcmd library with error number -2147467259.
[SQLSTATE 42000] (error 22050)

Can anyone shed any light on this for me? I suspect that maybe a different user is involved in some way. I cannot get SQL Server Profiler to run (it's not installed) and I don't have access to the server to install it.


texts: Designing a database for "not seen" queries

texts:
I'm building a large collection of items from which I want to query items that a specific user has not seen before. I will need to track, per user, which items s/he has seen.

Currently the items are stored in mongodb and the 'seen items' I am planning to store in a mysql database.

I have two questions:

How should I design the database of items in order to effectively query for items a certain user has not seen?
As each user can have 'seen' several thousand items, should each user have its own mysql table or should I just have one large table?

EDIT: The items the user get are from the collection of items the user has not seen are selected randomly