texts: Mysql get records more then 3 in interval of 1 minute

texts:
I currently developed a cron that runs every 1 minute to analyze the last 60 seconds of the bot records in database, I need to group the conversation ID's that have more than 3 records within 60 seconds in the same url and client_session_id.
follows the SQL I'm running:
select
    count(session_id),
    client_session_id,
    GROUP_CONCAT(id) as talkIds,
    origin_url
from
    bot_talk
where
    created_date > now() - interval 60 second
group by
    client_session_id, origin_url
having
    count(session_id) >= 3

This query works as I expect, but sometimes my cron service is sometimes down, and I lose those repeated records.
I thought about making an SQL(Cron) at the end of the day to analyze the last 24 hours, and look for the records that are repeated according to the rule I mentioned above?
Currently my database looks like this:

created_date
origin_url
client_session_id

2021-01-18 11:02:24.0
https://chat-app.ttttttt.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:02:35.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:11:03.0
https://chat-app.testett.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:44:28.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:49:36.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:51:05.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:51:15.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:51:19.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:51:43.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:51:50.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 12:01:24.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 12:04:48.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 13:40:50.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:54:38.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:54:45.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:55:08.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:58:07.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:58:11.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:59:56.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 16:08:32.0
https://admin.testete.com/
znkjoc3gfth2c3m0t1klii

fiddle:
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=b8ce65bad5d39ea85fc5b57d7dc0f729


texts: SSRS 2016 report is failing with 'EXCELOPENXML' render format error

texts:
Few reports are failing with 'ExcelOpenXML' render format error after rs migration from 2014 to 2016.
I made following change to the RS config file so I can use old excel format however some of the reports are using 'ExcelopenXML' format.
How can I use both 'Excel' and 'ExcelOpenXML' format so report will be sent out based on render format setup in subscription?

<!--Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/-->
<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>
<Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/>
<!--Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/-->
<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>
<Extension Name="WORDOPENXML" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/>


texts: What does the bit data type map to in a Java Domain Object

texts:
When you have a column of the bit data type what Class would be used to represent that in a Java POJO mapping that table to an object which is using javax.persistence annotations?

I tried Boolean, which didn't work and produced the following errors:

  org.hibernate.exception.SQLGrammarException: could not insert: [com.DomainClassName]
  java.sql.SQLException: Incorrect syntax near '-'.

EDIT:  a Java boolean does map to the bit data type in SQL Server.  I had a column name with a dash in it that I had to change.


texts: How to configure postgresql to use multiple cores?

texts: How can I configure postgresql to use multiple cores in a single machine , is it automatically uses it ?


texts: Coalesce per row - get all Detail IDs grouped per HeaderRow e.g. Invoice1 | 1,2,3 Invoice 2 | 4,5,6

texts:
I have the following demo data (taken from Adventureworks)

SalesOrderNumber    SalesOrderDetailID
SO43659             12
SO43659             11
SO43659             8
SO43659             9
SO43660             14
SO43660             13
SO43661             27

Is it possible to Group per SalesOrderNumber, and show the Detail IDs per SalesOrderNumber?

SalesOrderNumber    SalesOrderDetailID
SO43659             12, 11, 8, 9
SO43660             14, 13
SO43661             27

http://www.sqlfiddle.com/#!3/7708b/1

What would be the best option for this please?
Pivots?
Cursors?

Cursor which calls stored proc for each row , e.g. here ?
Or this option from 4guysfromrolla which uses user-defined function?

This is something that will be run once on only - at night -  I dont need efficiency - just any help please.


texts: What privileges does the SQL Azure Migration Wizard need to copy a SQL Azure database?

texts:
Currently have 2 users in our SQL Azure subscription. One is the god login created when setting up the server, other is very limited account for our www app (has db_datareader and db_datawriter).

The www user does not have enough privileges to use the SQL Azure Migration Wizard. Connecting to server shows the db as grayed out / disabled.

I want to create a 3rd user with just enough privileges to use the mw for copy / backup. What roles / privileges does this user need? Does anyone have some prepared sql scripts?


texts:
I have the following query to get me the values corresponding with the latest date:

SELECT MAX(RowAddedDate), X, Y
FROM dbo.MyTable
GROUP BY X, Y

This is fine, but I need to get the ID of each row in this query.  If I add the ID though, I get everything as the ID needs to be in the GROUP BY.

How do I solve this?


texts: Choosing index fillfactor for large, highly inserted table

texts:
We have a table that will hold records on the 108 order.

╔══════════╦═════╦══════╦══════════════════════════╗
║    id    ║  a  ║  b   ║ ...many other columns... ║
╠══════════╬═════╬══════╬══════════════════════════╣
║        1 ║   1 ║  118 ║                      ... ║
║        2 ║   1 ║ 1022 ║                      ... ║
║        3 ║   5 ║  118 ║                      ... ║
║        4 ║   3 ║  118 ║                      ... ║
║      ... ║ ... ║  ... ║                      ... ║
║    10020 ║   5 ║ 1022 ║                      ... ║
║    10021 ║   1 ║  118 ║                      ... ║
║      ... ║ ... ║  ... ║                      ... ║
║  5000000 ║   2 ║   30 ║                      ... ║
║  5000001 ║   1 ║   30 ║                      ... ║
║      ... ║ ... ║  ... ║                      ... ║
║ 28218321 ║   1 ║  118 ║                      ... ║
║ 28218322 ║   4 ║   57 ║                      ... ║
╚══════════╩═════╩══════╩══════════════════════════╝

Column a always holds one of 5 values.  Column b always holds one of ~5000 values. a and b are unrelated.  So for every value of a there will be at least tens of millions of table rows, and for every value of b there will be at least hundreds of thousands of rows.

A lot of our queries are based on WHERE a=<value> or WHERE b=<value> so we are indexing those columns (two separate, single-column btree indexes).  I am trying to get a sense of what the optimal choice of FILLFACTOR would be for this situation.

This table does not receive many UPDATEs, however it receives large bursts of INSERTs (on the order of 106) in short periods of time.  At the same time it is under moderate, constant read load.

If my understanding of btree index fillfactor is correct it sounds as though we may benefit for a fairly low fillfactor for a and b indexes, allowing lots of empty space for new row references to be added to the leaf pages as new records come in with the same a or b value as previous ones.  Is this correct?

Is this a situation where a low fillfactor would be beneficial and if so what approximate value should we be looking at using?


texts: Best Database specifications for my IOT solution

texts:
i'm working on a IOT environment that basically is a sensor that can measure more then 300 times a second. We receive this measurements remotely using MQTT protocol. I want to set up a painel where i can watch in real time the measures my sensor is doing, i can do this with MQTT with no problem at all. But someone have ever done this using some database function? I know there are something called "Queue watch" for database that can do something similar.

The problem starts when i need to store those measurements, because i need to do analysis on them, draw graphics, and things like that.

As i'm not familiar with databases, there is a lot of data to store ( i imagine ) what would be the best database environment for this kind of thing?

----UPDATE1 -----

How many do you need to store? Last hour? Last day? Last decade? – Basil Bourque

As it's a service for clients, i would say that the lest month of measurements would be "hot data", i want to make them easily accessible and as fast as possible and anything older then that i can store as "cold data" just for eventual analysis, or any request for old measurements from clients.


texts: How does Postgres store many varchars on disk?

texts:
I have a table that is +80 gigs with 200 million rows. I'm trying to speed it up and I noticed that it has a large number of varchar columns. In the schema, their lengths range from 15 chars to 250 chars with most set at 50. The total length of all the varchars is 850 bytes. In actual usage, a lot of the fields are null or the strings are really short.

I know that Postgres uses an 8k page size. Now if I have to do a full table scan and assuming worst case scenario, 8k / 850 = 9.6 records per page. Going through my full table is going to (and does) take a long time. In actuality however, since most of those fields are empty, how much space will be allocated on disk for those varchars? Will there be more records per page or does Postgres put in a little padding just to make things easy for updates later?

The reason I'm asking is because I'm exploring the idea of improving performance by kicking as many of the infrequently accessed varchars columns as possible out of this table and into another one that we would access via a join.

Any confirmation or denial of my logic is appreciated.

mj


texts: after running Galera Replication for MariaDB , performance (TPS) does huge diffrence

texts:
I have two VM machines (with exact hardware-software configuration on two ESXI Servers in same rack and same subnet too) that is running with nginx + PHP-FPM + and MariaDB 10.4.
in each ESXI server I have just 1 VM with 24G RAM, 24 Core CPU
On standalone nodes the TPS is 8000 avg.
But when I switched to galera Master-Master replication mode with below conf, The TPS is decrease into 1100 avg!
each server's app using It's local DB.
here is my.cnf

[mysqld]
skip-name-resolve
binlog_format=ROW
wsrep_on=ON
wsrep_replicate_myisam=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.7.11,192.168.7.12"
wsrep_cluster_name="my_cluster"
wsrep_node_address="192.168.7.11"
wsrep_node_name="node1"
wsrep_sst_method=rsync
wsrep_slave_threads=16
innodb_flush_log_at_trx_commit=2
innodb_flush_method = O_DIRECT

What am I missing?


texts: I need to create a Database as a Service (DBaaS) concept for our Datacenter. Currently we have everything of VMware – but this is not really a DaaS concept I think. Corporate policy does not allow public cloud solutions. Is it possible to implement something like Azure SQL Database in your own datacenter?


texts: When restoring a SQL Server DB from dump files on a remote NAS share, copy before restore or restore directly?

texts: My team is considering backing up all DBs to an Isilon NAS and we're conducting time trials to see if the restore time from the NAS is quicker than performing a copy to local storage before restoring. Besides considering the throughput of the source and target storage and the network link, what else should we consider before making a selection? Thank you!


texts: How to find functional dependancies from simply looking at a relational schema?

texts:
If only given a schema (table header, with primary key indicated), how do I find functional dependencies?

My problem is that functional dependencies are determined in terms of the data stored in the relevant table, and so you could not determine all, but only some functional dependencies by looking at the schema only?

Consider this example:

How do I go about finding the applicable functional dependencies?


texts: Jobs on the Mirror are running against the Primary

texts:
I have a mirrored set of jobs between the my current primary and mirrored failover SQL Server setup.  In the past, the jobs on the mirrored server, when they are enabled, fail as they are not able to open the local database since it's in a restoring state.  However I'm now seeing the jobs succeed and are getting forwarded to the current primary databases.
I've seen this behavior for the SQL Server Driver that .Net uses so it will allow for an automatic reconnect if a failover occurs.  I haven't seen this behavior before on a SQL Job and I can't seem to find any documentation around it.
I've found two other mentions of this behavior ( one , two ) with work arounds, however I'm having a hard time locating documentation on this feature so I know if it was a configuration change that I made or if it was enabled with an updates I installed.
Is this capability documented in any of Microsoft's references?
The jobs are T-SQL steps, and they are reading and (successfully) writing.  Some are inline T-SQL and others call SPs in the target databases.  I'm using standard mirroring and not availability groups.


texts:
I am trying to learn to mirror a test database and in the course of doing this, was typing in some commands into a query box.  While trying to create a database mirroring endpoint I submitted a command with a typo.  I was returned an error.  I tried to resubmit a corrected command, but then was told that the endpoint already existed. So then I figured I'd just delete my test database and restart. I deleted the database in my primary SQL Server, but now my mirror SQL Server shows my test database as restoring, and I am unable to delete that one.

Is there a way to undo everything that I have done, going back to a installation default of my SQL Server?  There is nothing I need to save, I'm just trying to learn.


texts:
One of our clients recently came to us with a problem he's facing while working with our application. The error in the log file is the next:

  System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  This failure occured while attempting to connect to the Principle server.
  System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

As far as I know, that client has set up mirroring between two database servers and he's currently working on the fail-over server (They do that on purpose each now and then).

I am aware that I could use the CommandTimeout property in my code to increase the time before a timeout is reached.

The application is working normally, so it's connecting to the database way before this error happens. Also, connecting through SSMS is working.

Note that, due to security reasons on my client-side, I can't use a profiler to narrow down the problem to a specific query or procedure.

What I am actually concerned about is the error message in bold.

Does that error mean that the actual problem is a connection problem between the Main DB Server and the Fail-Over server or could it also be triggered by a query taking more than the default 30 seconds?


texts: Is this a valid query that could allow to detect whether compression is supported or not?

texts:
I am developing against a demo Oracle instance and have no access to compression. My code has to detect whether compression is available or not. My thought was to run this statement in a try/catch block and set the flag accordingly:
CREATE TABLE D COMPRESS FOR ARCHIVE AS SELECT * FROM dual;

Can anyone with access to an Enterprise instance tell me if this is the correct query? For me, it always throws:
Error: ORA-64307:  Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type

I am not sure if it correctly tells me that my instance is not Enterprise, or there is a problem with the query itself.


texts: SELECT permission denied trying to import data into Excel

texts:
We recently migrated to a newer version of a program and migrated its database from SQL Server 2000 to SQL Server 2008 R2. We had some Excel files which ran a few custom queries to pull data. I changed the values in the connection string to reflect our 2008 R2 setup:

DRIVER={SQL Server};SERVER=SQL01;UID=PSI;Trusted_Connection=Yes;DATABASE=PSI_DB_A;

Trying to do a refresh in Excel gives the error:

  The SELECT permission was denied on the object 'ARTRNTRX', database
  'PSI_DB_A', schema 'dbo'.

I researched this error on this site and numerous places on the web before posting; trying some of the things suggested.

The only difference is that with SQL Server 2000, the user "sa" had access to everything and every database. We wanted to avoid that in 2008 R2. So I created the PSI user and wanted to give it limited access. We just perform SELECT queries in these few Excel sheets anyway.

Using the SSMS, under Security > Logins > PSI > Properties > User Mapping > I gave the "PSI" user the "db_datareader" role for PSI_DB_A. Then under the Databases > PSI_DB_A database > Properties > Permissions > I select PSI user and granted them the "Connect" and "Select" permissions in the list.

But I'm still getting the SELECT permission error when I try to refresh the data in Excel. I'm at a loss here.

EDIT:

After a bit more digging, I noticed when in Excel I could connect to the SQL Server using From Other Sources > From SQL Server > Enter server name > Select Database PSI_DB_A from the list and it shows me views and tables. But only certain tables are showing, not all of them.

Going back into SSMS, I expanded the table list under the database, right-click a table (on one of the tables which was showing in Excel) > Properties > Permissions. Under users/roles the user "public" is shown which is given SELECT, etc. permissions. But on 90% of the other tables, "public" is not listed.

Why does "public" need to be added at the table permission level, when I defined a login user who should have complete read authorization on all tables in the database?


texts: How to improve MySQL Server Performance..?

texts:
As a MySQL DBA most of the times we are supposed to optimize a poorly performing MySQL Servers.

Now my question is where to start from like we will need to find out many things as

1.Find the duplicate indexes.
2.Find unused indexes on the basis of selectivity.
3.Monitor the Server Parameters(What should be important parameters).
4.Execute MySQL Server performance tuning script.
5.Slow logs

So what should be order of examining the server and what should be exact things that should be monitored/analysed for improving the Performance.


texts: Start failed for Job in SQL Server Agent

texts:
I am trying to schedule a job which executes stored procedure but it is failing.

DECLARE @return_value int
EXEC    @return_value = [dbo].[sp_WarehouseAttendance_New]
SELECT  'Return Value' = @return_value

If I try to run it manually,  I am getting this error.

At the moment I am trying to run EXECUTE storedproc but it takes long time to complete.

I checked Services.msc and it shows as below.

Kindly let me know how to avoid this.

Thanks,
Ar


texts: Having issues with postgresql, what should I log?

texts:
Here's the error message I am receiving.

could not translate host name "localhost" to address: hostname nor servname provided, or not known

From my searching, it seems like postgresql has crashed due to an error. If a proper shutdown is initiated, the message is different.

FATAL:  the database system is shutting down

So I know it's not a proper shutdown of the database service that is occurring.

I am logging to syslog on a FreeBSD system. The log is very basic and essentially only shows me shutdown and startup attempts.

LOG:  database system was shut down at 2017-05-08 09:20:16 EDT
LOG:  database system is ready to accept connections

I am currently using default logging options. What do I need to change in order for PostgreSQL to log errors?

Requested Information

/etc/hosts:

127.0.0.1               localhost localhost.my.domain

DB connection code:

char conninfo[] = "host=localhost port=xxx dbname=xxx user=xxx password=xxx";
conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK) {
print error
}


texts: Cancel SQL Job execution make automatic rollback

texts: I have a SQL SP which has a set a transaction. All the statements handled inside the TRY CATCH block. If we explicitly cancel the job by close the button in the JOb execution window, will it get into the catch block and automaticall rollback all the transaction tried in the TRY block ?


texts: Cant Import mysql system DB from another server

texts:
Both Servers are the same in hardware, OS, and MySQL versions (5.6.11).

One server has MySQL on it and on the other one I just setup a fresh instance.

I want to import all of the data to the second server.

I dump the mysql DB to a .sql file and when I try to import it on the other server I initially get no errors. At that point if I stop the mysql service it won't start up again and I get this error logged in event viewer:

Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

So it looks like the import is not working. Is there another command I have to run after importing the mysql database?


texts: How to make a single row result query to return multiple rows

texts:
I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. To have the measurement in meter I cast the geometries to geography.

I also have a given route as a Linestring with the SRID 4326.

In the SQL statement I fetch 1 Point from the station and search the closest point in my routing network, but I want to do this for all stations from the WITH Query

WITH stations AS (SELECT id, geom FROM de_tt_stations AS s
  WHERE ST_DWithin(s.geom::geography, ST_FromEWKB(%(route)s)::geography, 1000))
SELECT 1378549, destination
FROM (
  SELECT id::integer AS destination
  FROM de_2po_vertex ORDER BY geom_vertex <-> (
    SELECT geom FROM stations LIMIT 1)
  LIMIT 1)
AS foo

From the resulting list then I want to calculate the length of each route so that I can choose the closest filling station. Optimal output would look like

start   | destination      | station_id  | length
--------------------------------------------------------------
1378549 | de_2po_vertex.id | stations.id | SUM(pgr_dijkstra())
...

I'm using PostgreSQL 9.5, Postgis 2.1.5 and pgrouting 2.0.0

Table definitions:

public.de_2po_4pgr
(
  id integer NOT NULL,
  osm_id bigint,
  osm_name character varying,
  osm_meta character varying,
  osm_source_id bigint,
  osm_target_id bigint,
  clazz integer,
  flags integer,
  source integer,
  target integer,
  km double precision,
  kmh integer,
  cost double precision,
  reverse_cost double precision,
  x1 double precision,
  y1 double precision,
  x2 double precision,
  y2 double precision,
  geom_way geometry(LineString,4326),
  CONSTRAINT pkey_de_2po_4pgr PRIMARY KEY (id)
)

public.de_2po_vertex
(
  id integer NOT NULL,
  clazz integer,
  osm_id bigint,
  osm_name character varying,
  ref_count integer,
  restrictions character varying,
  geom_vertex geometry(Point,4326),
  CONSTRAINT pkey_de_2po_vertex PRIMARY KEY (id)
)

public.de_tt_stations
(
  id character varying(255) NOT NULL,
  name character varying(255),
  brand character varying(255),
  street character varying(255),
  "number" character varying(255),
  zip character varying(255),
  city character varying(255),
  premium_e5_time timestamp without time zone,
  premium_e5_value double precision,
  premium_e10_time timestamp without time zone,
  premium_e10_value double precision,
  diesel_time timestamp without time zone,
  diesel_value double precision,
  holiday_identifier character varying(255),
  opening_times text,
  opening_times_extended text,
  override_opening_times text,
  geom geometry(Point,4326),
  CONSTRAINT de_tt_stations_id_pkey PRIMARY KEY (id)
)


texts: MongoDB - Clarification on choosing randomly distributed shard key

texts:
In one of my readings i have noticed following statement for choosing randomly distributed shard keys but not able to understand why is it said so. Could someone provide me with detailed explanation on this.
"The only downside to randomly distributed shard keys is that MongoDB isn’t efficient at randomly accessing data beyond the size of RAM."
Thank you.


texts: Select user activity over past months grouped by cohorts

texts:
In MySQL 5.7, I have a table user that contains the columns registered and lastLogin. My goal is to group the users by calendar months of registration and then select for each group how many of these users were still active after 1 month, after 2 months and so on.

20,000 users have registered in June 2015, from these 10,000 have been active in July 2015 or later, 5,000 have been active in August 2015 or later, etc. Continue this output for all users registered in the past months until today.

I understand how I can select any of the activity months by hand, for example select all users that were active one calendar month after their registration:

SELECT year(registered), month(registered), count(id)
FROM user
WHERE lastLogin >= DATE_ADD(DATE_SUB(date(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY), INTERVAL 1 MONTH)
GROUP BY year(registered), month(registered)

This would give me the number of users who were active one month after their registration or later.

But in addition I would also like to select the number of users who were active 2 months after their registration, 3 months after their registration and so on up to the current month.

The results would be something like this (not necessarily look like this, but it should get the idea across):

Columns:      Cohort |    total | 1 month | 2months | 3months | ... | month n
             07/2015 |   20,000 | 10,100  | 5,500   | 2,600   | ... | 150
             08/2015 |   21,000 | 9,800   | 4,300   | 1,300   | ... | 180
             09/2015 |   19,700 | 11,400  | 6,200   | 3,500   | ... | 200
    ...
             08/2017 |   25,300 |  13,000
             09/2017 |   12,500

How do I change the query to select all months of activity instead of a specific month?


texts: Managing version control of Erwin file changes with multiple environments

texts: What is the easiest way to manage three environments (DEV, QA and PROD) within a single Erwin file or multiple Erwin files?  The Erwin file is separated by subject area, so I'm not sure reverse engineering is a viable option.  We perform migrations every day, but they don't always conform to what is in Erwin, so we have a difficult time showing our end-users what is in QA and what is in PROD.


texts: Can we create multiple users in one login in SQL Server 2008?

texts:
I have one database where I want 4 users in that particular database, so do I have to create 4 logins, one for each user ?

I want multiple users from one login. I tried to create a second user by using the same login but I am getting error

  The login already has an account under a different user name


texts: MySQL 5.7 Can slave be ahead of master during replication

texts:
From my understanding, once commit() is received from client, transactions are written to binlog first before any commit is done to InnoDB.

In this case, is it possible for slaves to be ahead of master if master is suffering from some performance issues?


texts: Invoke-ProcessASDatabase fails with "Cannot connect to server"

texts:
Occasionally I'm getting this error:

Cannot connect to server 'myASCube'. Client with IP Address 'x.x.x.x' is not allowed to
access the server. To enable access, use the Firewall settings in Azure Management Portal.
It may take up to 5 minutes for this change to take effect. More information on configuring
firewall and setting IP address ranges can be found here: https://go.microsoft.com/fwlink/?linkid=2099963

I can go into "Analysis Services" -> "Firewall" and add the IP Address, and then it runs again for a few days, before it fails on a new IP address.

This is the Powershell command I'm running

$AzureCred = Get-AutomationPSCredential -Name "CredForRefreshCube"
Add-AzureAnalysisServicesAccount -RolloutEnvironment 'westeurope.asazure.windows.net' -ServicePrincipal -Credential $AzureCred -TenantId "56cb8dc0-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
Invoke-ProcessASDatabase -server "asazure://westeurope.asazure.windows.net/myASCube" -DatabaseName "mydatabase" -RefreshType Full

I've checked if DbaTools can work with Analysis Services, and searched high and low for solutions. I've found https://www.inovex.de/blog/azure-automation-analysis-services/, where I've copied the Powershell code from.

How to I ensure that the Automation Services does not change IP, or should I just allow all IP address access to my cube?


texts: Oracle 11g: functions or procedures in C/C++

texts:
I want to create a function called by a trigger on oracle but in c or c++. Something like in PostgreSQL: Audit trigger in C.

Or, in any case, is there support for this but in other languages like Python?

Thanks!


texts: SQL Server 2005 on Windows Server 2012 R2 Install Question

texts:
Can I Install SQL Server 2005 on Windows Server 2012 R2 ?

Thanks !


texts: Alternative to updating every row in my table

texts:
My syntax below works (may not be the most efficient syntax, but it almost serves the purpose).  The issue that I have with it, is that when the UPDATE statement is run, due to the JOIN condition each row that fits the criteria is updated.  I would like to only UPDATE 1 row, per empName.  Maybe I could use the field AcctIDWorked to update either the MAX() or the MIN() of this field?  Doesn't matter either way honestly as I just need ONE row per empName updated.

How can I accomplish this?

DDL Below and current syntax

IF OBJECT_ID('dbo.__SalesDetailInfo', 'U') IS NOT NULL
    DROP TABLE dbo.__SalesDetailInfo;

IF OBJECT_ID('dbo.__Sales', 'U') IS NOT NULL
    DROP TABLE dbo.__Sales;

Create Table __Sales
(
    EmpName varchar(max)
    ,TotalSaleAmount decimal(10,2)
)

Insert Into __Sales (EmpName, TotalSaleAmount) Values
('OWT', '167.30'),('Fit D', '512.22'),('Mike',  '4147.60')
,('Curtis', '66.96'),('Curtis', '1165.58'),('Curtis',   '1507.64')
,('Curtis', '1551.79'),('Curtis',   '8132.13'),('Curtis',   '28.62')
,('Sam',    '499.00'),('Curtis',    '1159.76'),('Sam',  '2011.98')
,('Mike',   '450.00'),('Mike',  '1453.60'),('Sam',  '762.45')
,('Curtis', '1056.00'),('Mike', '1596.50'),('OWT',  '1768.80')
,('Mike',   '1200.00'),('Mike', '1500.00'),('Fit D',    '17921.30')
,('Curtis', '456.73'),('Curtis',    '944.23'),('Curtis',    '2242.00')
,('Mike',   '437.50'),('Sam',   '3258.07'),('Sam',  '3258.07')
,('OWT',    '7263.57'),('OWT',  '7225.57'),('OWT',  '224.63')
,('Curtis', '569.89')

Create Table __SalesDetailInfo
(
    EmpName varchar(max)
    ,TotalSales decimal(10,2)
    ,AcctIDWorked int
)

Insert Into __SalesDetailInfo (EmpName, AcctIDWorked) Values
('Curtis', 1), ('Curtis', 2), ('Curtis', 3), ('Curtis', 4)
,('Fit D', 22), ('Fit D', 11), ('Fit D', 44)
,('Mike', 110), ('Mike', 111), ('Mike', 113), ('Mike', 114)
,('Sam', 300), ('Sam', 310), ('Sam', 340)
,('OWT', 500), ('OWT', 550), ('OWT', 580), ('OWT', 590)

;WITH UPDATECTE AS
(Select EmpName, sumTSA=SUM(TotalSaleAmount)
FROM __Sales
GROUP BY EmpName)
UPDATE __SalesDetailInfo
SET TotalSales = sumTSA
FROM UPDATECTE cte
  INNER JOIN __SalesDetailInfo t
    ON RTRIM(LTRIM(cte.EmpName)) = RTRIM(LTRIM(t.EmpName))


texts: How to query in such a way that values becomes columns?

texts:
Having the table foo like:

| id | name |
|  1 | Foo1 |
|  2 | Foo2 |

And the table bar like:

| id | foo_id | key | value |
|  1 |      1 |   a | foo1a |
|  2 |      1 |   b | foo1b |
|  3 |      2 |   a | foo2a |
|  4 |      2 |   b | foo2b |

How can I mount my SQL in a way that produces an output like:

| id | name |   a   |   b   |
|  1 | foo1 | foo1a | foo1b |
|  2 | foo2 | foo2a | foo2b |

I have no idea even if it is possible.

On a high-level problem (maybe there is a better solution) I want to make the keys on a entity dynamic.


texts: Data modeling with the goal to get the best performance in Oracle and SQL Server

texts:
I have a question about how I can model my stock database in order to get the best performance possible.

In SQL Server or in the Oracle, each update executed generates a little lock.

I'd like to know what's the best solution that you could tell me

Solution 1: create a product stock table with quantity column and for each input or output execute a SQL update against this column

Solution 2: create a table for product stock movement where for each input I would execute an insert with a positive quantity and for each output I would execute an insert with a negative quantity.

At the end of the day, I would execute an process for update the quantity of the stock products with the "sum" result of the product stock movement table After that, I would delete all records in the product stock movement table

With solution 1, I would have the advantage that execute an simple select to get the product stock quantity but during the day I would have the disadvantage that have many locks due many quantity updates regarding output sold products

With solution 2, I'd have the disadvantage when, I will need to get the product stock quantity, I'd need to make a query with a join with product stock movement table and make a sum in all inputs and outputs of the consulted product, but in this way, during all day I wouldn't have any locks

What do you think about that two solutions presented?

Is it a good practice to make the modeling described in solution 2?

Thank you so much


texts: MariaDB 10.2.7 doesn't accept trailing Z for DATETIME timezone when inserting, but earlier version does


texts: Is SQL Server Backup single or multi-threaded?

texts:
If you perform a...

BACKUP DATABASE [DB_Name] TO DISK=N'c:\backups\db_name.bak' WITH STATS=10

Will that perform a single or multi-threaded write?

Our Server support company advised us that if we were to multi-thread our backups we would get better disk write performance. They advised us that SQL Backup is multi-threaded, but i think the above statement would be single-threaded. If we specify multiple DISKS to write to, then it will stripe the backup across multiple devices and therefore be multi-threaded by my understanding.

If I perform the following T-SQL while the backup is processing, i can see that the SPID performing the backup has multiple processes, but only one seems to write to disk, the others i presume are reading and doing other things. It's just the write threads i'm referring the question to.

USE MASTER
GO

SELECT *
FROM sys.sysprocesses p
INNER JOIN sys.dm_exec_requests r ON p.spid = r.session_id
WHERE spid=(spid of backup T-SQL)

Thanks in advance.


texts: What is best for a primary key? Bigint of 20 numbers or varchar / varbinary of 15 characters?

texts:
I have already seen some posts making comparisons of varchar with numeric fields, but most only compare with equal values and do not talk about performance in primary keys.
I have a user registration table where each user needs to have a unique code and the auto-incrementing sequential numbers (1,2,3 ...) are not a good idea.
So I'm using PHP's uniquid() command to generate user codes.
Something like this: it5f29b715cff42 containing on average 13 to 15 characters in varchar type fields
I use this code in the primary key because it is the main code of the user's registration, and there will always be a user search for this code in the table.
Some people have suggested switching to a varbinary type field to gain performance. And yet I still don't know if it's the best idea.
So I thought of turning this code into numbers using PHP's hexdec() command. Leaving the code more or less like this: 10001674124319326018 containing an average of 16 to 20 characters. And storing that code in a bigint type field.
I know that the bigint field will always be 8 bytes, but how many bytes would varchar and varbinary in that number of characters occupy? What is the best option to use as a primary key in a table with an average of 2 billion records?


texts:
I have this simple tables (from Northwind database)

Orders-->[Order Details]<---Products

with a query can see  the order and the products using join very simple

  SELECT
  Orders.OrderID,
  Products.ProductID,
  Products.ProductName
FROM Orders
INNER JOIN [Order Details]
  ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products
  ON [Order Details].ProductID = Products.ProductID

OrderID     ProductID   ProductName
----------- ----------- ----------------------------------------
10265       17          Alice Mutton
10279       17          Alice Mutton
10294       17          Alice Mutton

but the problem when a user change the name of the product this will cause display the new name for that product

OrderID     ProductID   ProductName
----------- ----------- ----------------------------------------
10265       17          New product name
10279       17          New product name
10294       17          New product name

old Orders shouldnt change so my question is how keep the related data even if they change the name of the new product

thanks sorry bad english


texts: Why does everyone want me to create an alert for severity 025?

texts:
Brent Ozar and others suggest to listen to severity 025 SQL-Server events. I searched for the meaning of all different high severity events, looked through different articles and read the most recent msdn documentation. But the only severity that seems to be monitored by everyone but explained nowhere is 25.

What is severity 25, where is it explained and what does it mean?


texts: How do you enable SQL Server 2019's result set caching?

texts:
SQL Server 2019 CTP 2.3 added a few new columns to sys.databases, including is_result_set_caching_on:

It's not documented yet in the What's New in SQL Server 2019, nor in the ALTER DATABASE page.

I tried using the same syntax as Accelerated Database Recovery just for laughs:

ALTER DATABASE StackOverflow2013 SET RESULT_SET_CACHING ON;

But no luck:

Msg 5058, Level 16, State 12, Line 8
Option 'RESULT_SET_CACHING' cannot be set in database 'StackOverflow2013'.


texts: fix `mysqldump: Error 2013: Lost connection` on "cloud" server

texts:
I have 6 months of backups left to perform and a single day can take up 200MB in raw mysqldump --extended-insert=false --complete-insert form.  If I try to dump more than a day worth of data I get mysqldump: Error 2013: Lost connection to MySQL server during query when dumping tabletableat row: 657925 (the row number changes).

my mysqldump query is mysqldump -h dbName.db.id.hostedresource.com -u 'user_name' -P 3306 -p'password' --net_buffer_length=4096 --single-transaction --extended-insert=false --complete-insert=TRUE --no-create-info --compact --insert-ignore --quick --where="start>='2016-01-13 00:00:00' AND start<'2016-01-15 00:00:00'" dbName table names

same issue exists with --extended-insert=true or --extended-insert=false and since it's a cloud server I have no access to the server or it's admin account to change buffer or cache settings.  Is there any hope of escaping this mindless drudgery within the next week!


texts:
This question might be deadly simple but I wanted to make sure I am going through the correct path and not making any simple and irrevertible mistakes.

My project will be based on messaging and it won't be as less-used as a side tool like in Twitter or Facebook (not that they are not used but it's not the key option presented to users)

I was wondering will a simple table design like;

from  (id)
to  (id)
content  (text)
created_at (date)
isRead (boolean)

will suffice because, even though it is planned to be a small project, I do not believe that all the messages should be stored in one table and for each user when they want to view their messaging history, I shouldn't be scanning the entire table to do so. I am thinking about how scalable would this structure be and how would I benefit using a NoSQL DB like mongoDB.


texts: Multi-column and single-column index on the same column

texts:
I have two indexes on the same column:

store_deleted_idx (store_id, deleted)
store_idx (store_id)

DDL:

CREATE TABLE `stores_shoppers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `store_id` int(11) NOT NULL,
  `store_shopper_id` varchar(128) NOT NULL,
  `country_code` varchar(30) DEFAULT NULL,
  `zip_code` varchar(50) DEFAULT NULL,
  `email` varchar(128) NOT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `modified_on` datetime NOT NULL,
  `is_marketing_allowed` tinyint(1) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `is_guest` tinyint(1) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `total_spent` decimal(12,2) DEFAULT NULL,
  `orders_count` int(11) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `store_idx` (`store_id`),
  KEY `store_deleted_idx` (`store_id`,`deleted`),
  CONSTRAINT `_stores_shoppers_ibfk_2` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`),
  CONSTRAINT `_stores_shoppers_ibfk_4` FOREIGN KEY (`group_id`) REFERENCES `stores_shopper_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=56909900 DEFAULT CHARSET=utf8;

My query:

SELECT s.*, t.*, g.*
FROM shoppers s
LEFT JOIN shopper_tag t ON s.id = t.shopper_id
LEFT JOIN shopper_group g ON s.group_id = g.id
WHERE s.store_id = '4494' AND (s.deleted = 0 OR s.deleted IS NULL)
ORDER BY s.created_on DESC
LIMIT 10 OFFSET 0;

If I run the explain, it shows the query is using only store_idx index, but if I remove store_idx index and run the explain, it shows store_deleted_idx is used.

Why isn't store_deleted_idx used in the first case?

I can't remove the single-column index.


texts: Linked Server Security under SQL Agent Job Context

texts:
I have two SQL servers V1 and V2. Both 2012.
V1 has linked server connection to V2.
There are two Windows accounts: one running SQL server, the other
running the Agent service. The two accounts are common to V1 and V2.
I have a SQL Agent job that is owned by a non-sysadmin login, an SQL Server account. This account is also present on both servers with the same name and password.
On Linked Server Properties, Security tab, there are no explicit mappings. The default then is set to "Be made using the login's current security context"

Here's the problem.

I run SQL statement against V2 from V1 in SSMS as the non-sysadmin login and it works. However, when I create a job owned by the login with the same query, it fails with:

  Executed as user: NONSYSADMIN_USER.
  Linked servers cannot be used under impersonation without a mapping for the impersonated login.
  [SQLSTATE 42000] (Error 7437).  The step failed.

Then I run in SSMS with EXECUTE AS which results in pretty much the same:

  Msg 7437, Level 16, State 1...
  Linked servers cannot be used under impersonation without a mapping for the impersonated login.

I then schedule another job which is owned by sysadmin, running the same query and that works.

From my understanding, if the job is owned by sysadmin, there is no impersonation, the job runs (and steps as long as there is no run as or proxy set) as the Agent service account.

So, my question is, shouldn't the sysadmin owned job also fail since it's using Windows account, and as far as I know there isn't any delegation configured for the account. Or does the fact that it works indicate that the delegation was configured?

My ultimate goal is to have a job that is owned by a non-sysadmin login, which is a windows account, that runs queries against the linked server. Will this work?

According to the Microsoft's KB article, it would not without employing workarounds. Can anyone confirm it?

Here are some of the references I've used (among others):

SQL Server Agent jobs and user contexts, Tibor Karaszi's post on sqlblog.com
Linked Server Properties (Security Page) on TechNet


texts: I am working on SQL Server 2014. I want to know how can I rename the .ldf and .mdf files of my database after creating it? If it is possible what are the steps?


texts: Can I be automatically notified of prolonged blocking in SQL server?

texts:
About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a certain form and is released once the user has finished scrolling through the form or closes it. Since many of our users open this form as reference, these locks stay around for a while. Any update to the table causes the blocking, and suddenly nobody can select from this table since they're all waiting on the first lock. This is quite a problem for us, since lots of apps rely on this data. I understand that this locking behavior is part of how Access works with linked tables.

I've been solving the problem from Activity Monitor, by killing whichever SELECT process is the Head Blocker whenever I find out about it. This is a problem not only because it takes me time to do it manually, but also because it's reactive. By the time I hear about it, it's already been a problem for a lot of people.

I'd like to know if there is an automatic way to check for these long-lasting blocking chains, and either be emailed or have the problem resolved automatically. The logic seems straightforward enough ("if any process matching this SELECT query has been blocking for longer than a minute, notify me/kill it") but I don't know how to implement this with SQL Server.

For what it's worth, I think the proper solution is to fix or rewrite the app. However, due to departmental politics, this is not an option for the next few months, so I'm looking for a stopgap.


texts: Primary key which is foreign key in an relationship is specified NOT NULL. Why?

texts:
I have question about a foreign key.

Primary key which is foreign key in an relationship is specified NOT NULL in some book's example. Why?

I was thinking that primary key couldn't take on value "null" in an relation. Hence if it is a foreign key in a relationship it couldn't take on value "null" de facto. Otherwise such modification violates key constraints since foreign key has to be one of the values contained in the table it points to in attribute which is primary key.
Thank you very much.


texts: How to Join two tables with the join being regexp_replace

texts:
Need your help, I have two tables “temp” and “temp 1”

The temp table I am deriving a column called numbers. I am
wondering how do you join table temp1 onto temp and joint on

SELECT regexp_replace(indicator,'[^0-9]') as Numbers
from temp

Select Number
From temp2

I tried the following however it’s not recognised

SELECT indicator,
       regexp_replace(indicator,'[^0-9]') as numbers
from temp
Left join temp2
    on temp.number= temp2.numbers


texts:
If I create a table with an identity column as primary key, and all the other columns have default values, for example

create table rr (id int identity(1,1) primary key, dt datetime default getdate())


texts: Suppose that I have logged in to oracle database using user X, then user X creates some roles using create role command. I want to know that is user X the owner of the roles? Can I select all roles created by user X?


texts: Postgres 10 shutting down on installation in Ubuntu 14

texts:
On trying to install postgres on Ubuntu 14 I am seeing postgres is shutting down. I am seeing the below errors in logs

[19043] LOG:  listening on IPv4 address "127.0.0.1", port 5432
[19043] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
[19044] LOG:  database system was shut down at 2017-12-16 02:41:39 PST
[19043] LOG:  database system is ready to accept connections
[19051] [unknown]@[unknown] LOG:  incomplete startup packet
[19043] LOG:  received fast shutdown request
[19043] LOG:  aborting any active transactions
[19043] LOG:  worker process: logical replication launcher (PID 19050) exited with exit code 1
[19045] LOG:  shutting down
[19043] LOG:  database system is shut down

Can some one let me know what could be going wrong? Why is postgres shutting down after the installation ?


texts:
I installed a new version of MySQL server (previously 5.0, now 5.5) and I copied the data folder in to the new one. Now I should perform mysql_upgrade. I opened the command prompt (server is located on Windows Server 2012) and used cd to get in the bin folder. I wrote the file name to run the upgrade. The result was mysql_upgrade: unknown option '--no-beep'. What's that? I tried to do some research on Google, but no one seemed to have the same error.

I'm confused. Even the MySQL documentation doesn't seem to tell anything about the error.


texts: Not getting current IP address in my oracle database

texts:
I have installed Oracle database in my local machine and my local machine IP address is - 192.168.1.55 but in Toad its showing different

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
----------------------------------------------------------
127.0.0.1

SQL>

For that I restarted Database but still it's showing the same.What changes need to do for reflecting current IP in the Database.


texts:
For reporting purposes, I need to be able to query dependencies between views and underlying tables at column level (via sys.sql_expression_dependencies).

One way to get SQL Server to store referenced columns is to use schemabound views. As some of my users will create views too, I would like to enforce those. As far as I've seen it isn't possible to allow only views with schemabinding to be created within a SQL Server database though (although this seems a legit requirement to me).

Are there other ways to force SQL Server to keep track of which columns are being referenced by which views? Or is there a hidden way to enforce schemabound views in SQL Server?


texts: How to solve ORA-011033: ORACLE initialization or shutdown in progress

texts:
I have followed link  to solve the problem.

When it is giving ORA-00600: internal error code, arguments: [kcratr1_lostwrt] then I am using:

SQL> startup mount

the above line should return ORACLE Instance started
but it is showing "cannot start already-running Oracle - shut it down first".


texts: Optimizing key lookup in subquery with UNION ALL

texts:
I am trying to optimize following query (little bit more complex actually, but this is important part):

SELECT Id, StatusDate, [...Lot Of Columns...]
FROM ( (
        SELECT Id, StatusDate, [...Lot Of Columns...]
        FROM Results_201505
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, [...Lot Of Columns...]
        FROM Results_201504
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, [...Lot Of Columns...]
        FROM Results_201503
        WHERE A = 0, B = 1, C = 3
    )
) as result
ORDER BY StatusDate DESC
OFFSET xxx ROWS
FETCH NEXT 25 ROWS ONLY

On every table I've created index covering 'where' part:

CREATE NONCLUSTERED INDEX ix_asdf ON Results_asdf (StatusDate DESC)
INCLUDE (A, B, C)

I can't use index A, B, C, StatusDate, because the WHERE conditions are changing depending on filters that are user selected. It can be WHERE A=, B=, C=, but also WHERE B=, D= or WHERE C= or without WHERE at all).

My idea is that SQL Server will perform fast index scan with my index, and delay key lookup until last possible moment (fetching top 25 rows) - this seems to me like most reasonable thing to do.

And indeed, if I comment out [... Lot of Columns...] everything goes as planned - index is used, no key lookup is necessary and everything is very fast.

But in full form, SQL Server (according to execution plan, as shown by SSMS) tries to do key lookup for each row, before OFFSET ... FETCH 25.

The reason is obvious (I believe) - Sql Server realizes that UNION ALL concatenates multiple tables, so after UNION it is too late to do key lookup (because after UNION SQL Server knows only ID, but doesn't know which results table it came from). ID is globally unique, but query optimizer probably doesn't know that.

Of course I'm able to work this around with something like:

SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ]
FROM (
    (
        SELECT Id, StatusDate, 'Results_201505' as tbl
        FROM Results_201505
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, 'Results_201504' as tbl
        FROM Results_201504
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, 'Results_201503' as tbl
        FROM Results_201503
        WHERE A = 0, B = 1, C = 3
    )
) as result
ORDER BY StatusDate DESC
OFFSET xxx ROWS
FETCH NEXT 25 ROWS ONLY

But my question is - is there a better way? Some kind of hint to optimizer would be perfect, but if it's not possible - what's most elegant/fastest/best way to handle this query?


texts: Delete backups from old incarnation in rman

texts:
I did a refresh on a non-production database and these are the incarnations:

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

1       1       ORCL     1355555557       PARENT  1          20-DEC-11

2       2       ORCL     1355555557       CURRENT 12         17-JUL-14

How do I delete the backups from the old incarnation since they're not needed anymore?
Any help would be greatly appreciated.
Thanks.


texts: distinct values from multiple fields in array

texts:
I have a similar mongodb collection:

[{
    "country": "France",
    "exchange": {
        export: [{
            resource: 'MILK',
            origin: ['Toulouse', 'Bordeaux']
        }]
        import: [{
            resource: 'BEEF',
            origin: ['Toulouse', 'Bordeaux']
        }]
    }
}, {
    "country": "Spain",
    "exchange": {
        export: [{
            resource: 'PORK',
            origin: ['Madrid', 'Barcelona']
        }]
        import: [{
            resource: 'WHEAT',
            origin: ['Valencia', 'Bilbao']
        }]
    }
}]

I want distinct values of resource for both import and export.
Expected result is:

{
    "countries" : ["Spain","France"],
    "export" : ["PORK", "MILK"],
    "import" : ["WHEAT","BEEF"]
}

Here's what I'm doing

db.countries.aggregate([
{$group: {
    _id: null,
    countries: {$addToSet: '$country'},
    export: {$addToSet: '$exchange.export.resource'},
    import: {$addToSet: '$exchange.import.resource'}
    }}
])

which returns:

{
    "countries" : ["Spain","France"],
    "export" : [["PORK"], ["MILK"]],
    "import" : [["WHEAT"],["BEEF"]]
}

these extra arrays (["PORK"]) surrounding values are annoying: how can I avoid them ?


texts: Python SQLite3 horizontal partitioning by string column

texts:
I have a 30 GB SQLite 3 database with a table containing 25 million rows. I want to group the table by a string column and create a new table for every group of records, named after the string and containing all columns.

Is this possible with Python and SQLite 3? I could process chunks in Python, but is there a SQL command?

Start table

all_entries

a   b   c   d
hi  4   234 lala
bye 7   342 lulu
bye 5   433 abc
bye 4   234 abd
hi  56  435 abc
hi  3   455 a

Result Tables

bye

a   b   c   d
bye 7   342 lulu
bye 5   433 abc
bye 4   234 abd

hi

a   b   c   d
hi  4   234 lala
hi  56  435 abc
hi  3   455 a


texts: Best way to document database mappings for migrations

texts:
I'm working on a project that includes mapping database elements for a migration and I wanted to know what tools others were using to do this?

Excel is a very flexible way to document simple mappings but I was wondering if anybody had a specific methodology they followed or other tools they use that they could recommend?


texts:
I have a huge table with 3B of rows containing around 6 months of data running on Microsoft SQL server 2017 (RTM), which is partitioned day by day on a date column (each day on a separate filegroup and each filegroup has 1 data file).
this table has an identity field which is bigint.
I have two indexes :
a clustered index on date and id
a non clustered index on date
I'm trying to run the following query :
select top 500000 * from table with(nolock) where id>@certain_id order by id

but the query is taking a lot of time. I tried to create an non clustered index on id field , but nothing changes!
the weird part I was able to run the same query with no issues and with fast response in the past. But due to some circumstances  I had to format the server and re-attach the database containing the partitioned table, and I'm now having this issue.
any hint is much appreciated.


texts: Finding table name referfences in SSIS packages

texts:
We are in the process of migration onto a later version of SQL Server, from 2012 to 2016.  Our SSIS packages are deployed to SSISDB, in order to find the tables being referenced by the SSIS packages, we have to open it and go through each SSIS package.
Is there a way to query the table names belonging to SSIS packages?

Dennis


texts: Database design issue, with multiple variables and constraints

texts:
I'm using PostgreSQL. I have the following entities Categories, Items, Attributes.

Relationships:

M2M between categories and items (an item can be in multiple categories, a category can have multiple items)
FK between Items and Attributes (an item can have multiple attributes)
M2M between Categories and Attributes (a category can have multiple attributes, an attribute can be find in multiple categories)

The design issues:

constraint: A category can have multiple attributes. An item can have multiple attributes, but only the ones linked to the categories in which the item is assigned.

The attributes have values. There are 2 types of attributes, integers or lists. Example:

Item attributes with integer values:

- length: 32

- strength: 44

The values is only dependent on the product.

Item attributes with string list values:

mode: full or mode: half, or mode: half,full

The values for string list attributes, like mode are a predefined list. An item that have the attribute mode can have one or many of the predefined values.

I want to use the attributes as advanced search:

in case of attributes with integer values(for items), check if the values if is between a minim and/or max value
It is similar to:

in case of attributes with values as a string list, check if at least one of the selected values in the search is also found in the attributes values for the product. It is similar to:

In a way similar to search filter on: ebay, but
mostly with min_max filters a less list checkbox filters.

I don't exactly know how to model the attributes connection and values, to keep them dynamic(add/remove attributes without changing the database structure)

I don't know in which category(es) a product will be, so adding attributes as columns I don't think is a viable option. Besides that some of them being lists add to the problem(I though about using json for their product values) but still maintain a sort of constraint regarding values available.


texts: How do you read MySQL InnoDB Monitor output?

texts:
I am new to DB Administration. I am trying to figure out the cause of deadlock exceptions, but I am unable to make sense of the logs.

The log says that "transaction 1 is rolled back". Why is this the case? The wager table has a foreign key relationship to the account table but is it "normal" for it (the trans running the insert) to not be able to obtain an S lock and therefore die as a result of a deadlock? I mean that seems pretty bad considering it is just a read to verify referential integrity.

And the second transaction is also writing to a wager table but there are no updates. So is the "mechanism" for creating auto-inc keys, or indexing mechanism or both that are causing a deadlock? Why would they not happen in serial fashion? Or may be they are but the wait period of some sort is too short.

Any help, pointers, further reading on how to use the log below to solve a deadlock problem appreciated.

111031 17:39:26
*** (1) TRANSACTION:
TRANSACTION 0 984899905, ACTIVE 180 sec, process no 10882, OS thread id 1104619856 inserting
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 3024, undo log entries 86
MySQL thread id 122, query id 28932942 localhost 127.0.0.1 bt update

INSERT INTO wager (amount_won, confirmation, created_by_partner, creation_date, description, fantasy_league,
     first_game_start, image_thumb, image_wide, kind, last_game_start, last_update_version, locked, name,
     num_future, num_past, num_present, partner, partner_wager_id, percent, progressive_num, result, status,
     sub_kind, teaser_type, user_account, wager_amount, win_amount)
 VALUES (null, 2, 1, '2011-10-31 17:39:26', 'Straight Bet', null, null, 'http://iphonebet_s.png',
     'http://iphonebet_l.png', 9, null, 0, 1, 'NFL: KC +4, -140', 0, 0, 0, 26, '39812820-1',
     null, null, 1, 1, null, null, 2176, '7000', '5000')

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2712 n bits 264 index `PRIMARY` of table `bt/user_account` trx id 0 984899905 lock mode S locks rec but not gap waiting
Record lock, heap no 126

*** (2) TRANSACTION:
TRANSACTION 0 984900638, ACTIVE 61 sec, process no 10882, OS thread id 1076607312 setting auto-inc lock
mysql tables in use 1, locked 1
38 lock struct(s), heap size 6752, undo log entries 147
MySQL thread id 121, query id 28932947 localhost 127.0.0.1 bt update
INSERT INTO wager (amount_won, confirmation, created_by_partner, creation_date, description, fantasy_league,
     first_game_start, image_thumb, image_wide, kind, last_game_start, last_update_version, locked, name,
     num_future, num_past, num_present, partner, partner_wager_id, percent, progressive_num, result, status,
     sub_kind, teaser_type, user_account, wager_amount, win_amount)
 VALUES (null, 2, 1, '2011-10-31 17:39:26', 'Straight Bet', null, null, 'http://iphonebet_s.png',
     'http://iphonebet_l.png', 9, null, 0, 1, 'NFL: SD/KC o +7.5', 0, 0, 0, 26, '39812916-1',
     null, null, 1, 1, null, null, 2045, '260', '200')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 2712 n bits 264 index `PRIMARY` of table `bt/user_account` trx id 0 984900638 lock_mode X locks rec but not gap
Record lock, heap no 72
Record lock, heap no 107
Record lock, heap no 126
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `bt/wager` trx id 0 984900638 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)


texts: How to replace multiple unicode characters

texts:
I have identified a couple of unicode characters in my database by means of the following query:

SELECT name FROM table WHERE name NOT LIKE '%'+CAST(name AS VARCHAR(1000))+'%'

...with which I get stuff like this:

MORRISON CPA LIMITED  E¦¬Ç¦®EŒÆŒƑÈ¨ˆÅ¸«Äº‹Å‹™Æ‰€ÆŒ‰E™Å…¬Å¸

Is it possible to get the collation when using a REPLACE function? Something like:

UPDATE table SET name = REPLACE(name,NOT LIKE '%'+CAST(name AS VARCHAR(1000))+'%','')

I know I can individually replace them REPLACE(name,'Æ','') but is there a way to replace everything that is unicode?

Edit: I don't know why but I get less rows if I use

SELECT name FROM table WHERE name != CAST(name AS VARCHAR(1000))


texts: Case when not return more than one value

texts:
I try this, but the SwitchCase can not return more than one value.

WHERE CP.NumIntAut IN (
    CASE WHEN @NumIntAut IS NOT NULL
        THEN (@NumIntAut)
        ELSE (SELECT NumIntAut FROM @TAB_NumIntAut) END)

How can I do?


texts: What to set innodb_buffer_pool and why..?

texts:
I have 170GB of InnoDB index and Data.

I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB.

So what should be the optimal size of innodb_buffer_pool.

UPDATE

we are going to migrate our this local database to ec2 so will set the RAM according to the current statistics of innodb that's why i need the size of buffer pool so we can have available RAM there.

File Per table is enabled.

I am using Linux machine.


texts: SQL Server Error 14013 - This database is not enabled for publication

texts:
I am trying to configure a database publication with T-SQL.

use master
exec sp_replicationdboption
    @dbname='anun',
    @optname=N'publish',
    @value = N'true';

It runs successfully and I see this if I run the second time

  The replication option 'publish' of database 'anun' has already been set to true.

  Msg 14013, Level 16, State 1, Procedure
  sys.sp_MSrepl_addlogreader_agent
  This database is not enabled for publication.

I can't understand why it brings this error if I have already set replication option True for this db. Is there other replication option that I should also enable?


texts: Question about the UNION operator and its implicit data type conversions

texts:
Is there a limit to the number of UNION operators to combine query results?

I have an odd situation where I have a table called TransmittalSheet TS that have a double (1 to Many) relationships with each of the 2 tables: (DocumentInfo and EquipmentJobPlan tables), they both have almost the same exact column names and data types.
Each primary key (TransmittalID)  that’s coming from the parent table TS above, have two corresponding foreign keys in each of the children tables above. FK pair columns are called: (Docs_Implementing, Docs_Obsolescing) and (JobPlan_TS_Implementing and JobPlan_TS_Obsolescing).

I need to query if each Transmittal have any documents or equipmentJobPlans are associated with it (either Imp or Obs for each table association), so I believe I need to have 3 UNION operators like this:

Select A, B, C , ‘Imp Doc’ as DocType
From DocumentInfo As Di INNER JOIN TransmittalSheet TS
ON Di.DocumentTransmittal_IMP = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Obs Doc’ as DocType
From DocumentInfo As Di INNER JOIN TransmittalSheet TS
ON Di.DocumentTransmittal_OBS = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Imp JP’ as DocType
From EquipmentJobPlan As EQ  INNER JOIN TransmittalSheet TS
ON EQ.JobPlanTransmittal_IMP = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Obs JP’ as DocType
From EquipmentJobPlan As EQ  INNER JOIN TransmittalSheet TS
ON EQ.JobPlanTransmittal_OBS = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’

Is there a better way to do this?  Using SQL Server 2008R2.

Also, it seems that Union is doing some data conversion for column C for example: both columns are capturing document versions. It has a data type of varchar(75) in the DocumentInfo table, but it has a smallDateTime in the JobPlan table, , should I have an explicit conversion in place or the implicit Coversion that the Union is doing will be OK? Thanks


texts:
We are using SSIS for ETL purpose. There is a shortcoming in SSIS as it doesn't provide any in built component which will bulk upsert the destination. The alternative to it to use Staging tables which is not an option as we already discarded it from maintenance perspective. While exploring the market we found ZappySys's "SSIS Upsert Destination" component which might fulfill our requirements.
Having said that, there is a caveat that it uses temporary tables as Staging tables and do the operations like upsert. Since we would be dealing with millions of rows, it is something which is not normal in our environment to use tempdb for such workload and might not be recommended.
So do we need to consider anything before proceeding with it? Anyone using the above mentioned component who can share the experience?
TIA.


texts: Storing arrays of data in a time-series database

texts:
I'm building an low utilization time-series database to capture yearly data points for a set of items fewer than 100,000.

My question has to do with storing arrays of data in a way that is easily queried later.

Right now the yearly_visits table looks something like:

visitID       MEDIUMINT primary key
userID        SMALLINT id of individual submitting yearly data
weight        SMALLINT weight of individual

The intake form also contains a checkbox with a list of favorite colors (via numeric value) from a lookup table.  Users can select one or more colors.

Should colors be stored in a separate visit_colors table that looks something like:

 visitID MEDIUMINT
 colorID SMALLINT

Or is there a better way of storing arrays of data in a time series?  I haven't written any code yet, so I want to design this in a way that doesn't bite me down the road when I'm asked to query against the color data down the road.


texts: what is the difference between 'order' and 'order line'?

texts: I was trying to understand degenerate dimensions. But I quite often encounters the term "order line" as grain in an transaction table. Could someone please explain the difference between the order and order-line ?


texts: Duplicate check constraint on multiple tables?

texts:
I have an integer value for a data to be included into two or three tables, Say it is part no. The integer value of part no must be between 1 to 8. I want to add that constraint into the tables.

What method should I follow? Should I create constraint on each of the tables or just create one table with the values for part no and reference the table from others?


texts:
I have an issue with MariaDB Server 5.5.56 replication. The slave got stuck and when I realized that we had already lost some of the bins. So this is the scenario :

In master :

MariaDB [(none)]> show binary logs;
+-------------------+------------+
| Log_name          | File_size  |
+-------------------+------------+
| master-bin.000320 | 1073742333 |
| master-bin.000321 | 1074247558 |
| master-bin.000322 |  753717941 |
| master-bin.000323 |  883803465 |
+-------------------+------------+
4 rows in set (0.01 sec)

cat master-bin.index
./master-bin.000320
./master-bin.000321
./master-bin.000322
./master-bin.000323

In slave ( excerpt )

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State:
              Master_Log_File: master-bin.000312
          Read_Master_Log_Pos: 405852801
               Relay_Log_File: mariadb-relay-bin.000942
                Relay_Log_Pos: 405852988
        Relay_Master_Log_File: master-bin.000312
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 405852703
              Relay_Log_Space: 405853426
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

So my idea to try and recover the slave is :

1.- In master : stop mariadb service
2.- In master : copy from backup bins 312 to 319
3.- In master : manually add bins 312 to 319 to master-bin.index
4.- In master : start mariadb service
5.- In slave : start slave

Would that work ? Is there any other way to solve my problem ? I know manually modifying master-bin.index is not recommended but I can not think of any other alternative to avoid recreating the slave.

Thanks in advance.


texts:
I am using mongodb Atlas (M0), which does not provide db backup feature. I am creating a nodejs script that will export all the collections automatically periodically.
So far I have done the following:

created a programmatic api from my login.
whitelisted my ip
installed mongocli
created a default profile using public and private api keys.

And command looks like this:
mongoexport --uri mongodb+srv://${mongoUser}:${mongoPass}@mongoprojectname.lok0l.mongodb.net/${database} --collection ${collection} --type ${fileType} --out ${fileName} --profile default
I am not able to figure out how to run this command. If I run this command in my terminal I get this error:
/bin/sh: mongoexport: command not found\n


texts: Is conditionally SQL Server log backup possible?

texts:
I have a SQL Server database and right now we have weekly full backup, daily differential backup and 5 minutes log backup. Most of the time there are reads and writes happening in the database. Sometimes, for example there was continuously eight hours period yesterday, there was only read operations happened in the database and no writes. In this period I don't want to do the 5 minutes log backup as there isn't any modifications happened. Is there a way for me to test whether some modifications occurred in the database so that I can actually take the log backup, otherwise the log backup session will just skip it. Is this idea reasonable and doable? I simply don't want to have too many log backup files which makes it a little bit harder to manage.

Edit:

I considered the function fn_dblog. My idea is to count(*) the result of this function and if it increased then I'll take a log backup. But I'm not sure whether read will cause log record increase.


texts: How to access another Connect server table in my Local server in SQLyog 7.01?

texts:
In same server name using same database

select * from area;(Working Good)

Same Servername but Different Database

select * from mysql.event(Working Good)

Now i am connection TWO Different server i want to access one Remote server database table into local server database table in my sql using SQL YOG 7.01
Examples my server like this

root@192.168.12.35
root@192.168.12.89


texts: How can you have a combined primary key for one table?

texts:
I have exactly the same question as posted here.
But in my case I do not use MySQL but Postgres. Can this be done with Postgres? All things indicate I can not use a combined index in Postgres, so assuming it can not: what solution would be most recommended?

Original question:

I have a table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. (...)

What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.

This is my current setup:

CREATE TABLE `invoices` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `invoicenumber` mediumint unsigned NOT NULL,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL
) COMMENT='' ENGINE='InnoDB';

To check the latest invoice, I run:

SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'


texts: How can I upgrade from Developer edition to Standard SQL Server 2017

texts:
I had accidentally installed the wrong edition of SQL Server on our Live environment.

How can I upgrade from Developer edition to Standard edition?

Thank you .


texts:
I saw a database schema that looked like this

CREATE TABLE foo (
    name_hash bigint,
    name varachar(500),
    a varchar(500),
    b varchar(500),
    ...
    PRIMARY KEY (name_hash),
    KEY ...
);

This seems to be an attempt to limit index size, by using a 8-byte integer rather than a 100-byte string. When looking up a value by name, the application hashes it, and then uses that hash in the SQL query instead of the name.

This is rather tedious, and I'm not sure if it is necessary.

Is there a feature of MySQL InnoDB that does something similar -- that looks up a string by its much shorter hash in order to fit index into memory?

Or does it do something like that already?


texts: How to access the data of collections from the .ns and other core storage files in mongodb?

texts:
My database in MongoDB was not accessible after the journal file became of the size > 1 GB . So I restarted the mongod process with a different data path to store the mongodb files in my machine.
Now I want to recover my old data from the old path but I am unable to do so. Can anyone help?

The DB is on a 64 bit Ubuntu box.


texts: SQLite writing a query where you select only rows nearest to the hour

texts:
I've got a set of data where data has been taken approximately every minute for about three months and the time has been stored as a UNIX timestamp. There is no regularity to the timestamp (i.e. the zero minute of the hour may not contain a reading, 00:59:55 and the next measurement could be 01:01:01) and days may be missing.

What I need is the row nearest to the hour, with the time-step rounding to the hour, as long as the nearest value is not more than 30 minutes away from the hour.

Where a matching hour could not be found it would be helpful if the query could include a time but no value.

I realize I am asking a lot, but this would be incredibly helpful.

By the way, the table is just PK(autoincrement), timestamp, value, sensor id(FK). I have tried this to get the data out:

SELECT strftime('%S', time, 'unixepoch'),
       strftime('%M', time, 'unixepoch'),
       strftime('%H', time, 'unixepoch'),
       strftime('%d', time, 'unixepoch'),
       strftime('%m', time, 'unixepoch'),
       strftime('%Y', time, 'unixepoch'),
       value
FROM Timestream
WHERE idSensor = 359;


texts: Best index for case insensitive match on one field and ordering by another field

texts:
I want to run a query like this:
SELECT *
FROM "core_user"
WHERE UPPER("core_user"."email"::text) = UPPER('emailsearch@example.com')
ORDER BY "core_user"."last_login" DESC
LIMIT 1;

I've tried adding two indexes:
CREATE INDEX core_user_upper_idx ON public.core_user USING btree (upper((email)::text));
CREATE INDEX core_user_last_lo_0521b8_idx ON public.core_user USING btree (last_login);

core_user_upper_idx works well if I remove the ordering clause, but in its current state explain gives
 Limit  (cost=0.29..10.33 rows=1 width=431)
   ->  Index Scan Backward using core_user_last_lo_0521b8_idx on core_user  (cost=0.29..5531.53 rows=551 width=431)
         Filter: (upper((email)::text) = 'EMAIL@EXAMPLE.COM'::text)

which doesn't perform very well.
Is there a better index for me to add here? I'm a bit unsure as to whether I should "prioritise" the email or last_login fields.


texts: SSDT - Unresolved Reference to temporary table in linked server

texts:
Here's the scenario.

I am extracting data from a table in an Oracle database via a linked server as part of an ETL process. I am developing the solution in Visual Studio 2013 with SQL Server Data Tools (SSDT).

The ETL procedure uses a temporary table which is dynamically created in the Oracle database via EXECUTE AT syntax. This is so that I can insert a small number of records from my SQL Server database into the temporary table in order to perform a join to a much larger source table (~20m rows) in the Oracle database. The insert statement uses four-part naming as follows:

INSERT INTO [linked-server]..[owner-name].[table-name]
SELECT a, b FROM [stage].[table-name];

However, because the table is temporary, my project contains a warning about an unresolved reference:

  SQL71562: Procedure: [schema].[procedure-name] has an unresolved
  reference to object [linked-server].[].[owner-name].[table-name]

Also, when I publish the database project, the publish operation fails because the temporary table does not exist. The error message reads:

  The OLE DB provider "OraOLEDB.Oracle" for linked server
  "[linked-server]" does not contain the table
  ""[owner-name]"."[table-name]"". The table either does not exist or
  the current user does not have permissions on that table.

I can publish the project by creating the temporary table first via SQL Management Studio then dropping it after the project has been published. But this seems to go against how database projects should work.

I've seen suggestions about suppressing T-SQL warnings for unresolved references, but that doesn't enable me to publish the project.

Notes:

The Oracle database belongs to a 3rd party application so I cannot
create permanent tables in the database
I have created the linked server object in the SSDT project


texts: SQL Server - Create Additional Database File for New Schema

texts:
I'm trying to create a change script for some upcoming changes. We're adding a new schema to the database and our client wants the new schema to make use of a new database file.

How do I, in T-SQL, create the new file and assign the new objects within the schema to use the new file?

We're using SQL Sever 2012.


texts:
Having run sp_BlitzIndex on my database I've found a number of "Aggressive Indexes" (https://www.brentozar.com/blitzindex/sp_blitzindex-aggressive-indexes/). Not only but I am finding that the number of "Aggressive Indexes" is slowly increasing.

I'm a dev who dabbles in the DBA side of things and I can't work out how to go from the information provided by sp_BlitzIndex about aggressive indexes to improving them.

I'm OK at performance tuning an individual query, but I don't know how to find out what query might be happening when the index gets aggressive, and I am assuming the locks are happening because the server is under load - which wouldn't be the case if I tune an individual query.

Any tips on how to investigate further would be appreciated.


texts:
Maybe the title is misleading but I'll try to explain what I'm trying to do.

I'm building an e-commerce site. I have my products and my order.
I want add constrains on how the order will be deliver to the customer depending on each product.

For example if I sell a big and fragile product I want to restrict it from being send with courier, the customer should only have the option to take it from the shop.
Or a specific product can only be sent to limited countries.

I thought I could create an extra table related to products.

That way I could know which types of constrains each product have.
And I have no limitation on how many extra type of restriction I can make.

For example

restrictions(1,124,'post.courier',false)

But if do this,

restrictions(2,1322,'country','Greece')

the value field it's a different type.

My second thought was to use a field restrictions type TEXT and pass inside both values and restriction in a JSON format or XML

Another thought was to create separate tables for each kind,

and join them all, but it didn't seem proper.

What would be the best in my case?
Is there a better way I could design my DB?


texts: MySQL: Configuring the right innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size on RDS with huge RAM

texts:
We are on a db.r4.8xlarge MySQL (version 5.7.23) AWS RDS instance with 16 cores, 32 vCPUs, and 244GB RAM. We set the Buffer Pool Size to around 144GB at the default innodb_buffer_pool_chunk_size and 32 innodb_buffer_pool_instances.
According to the documentation at: https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

To avoid potential performance issues, the number of chunks
(innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) should not
exceed 1000.

For us, that value is 1,152. In addition, we see occasional swap usage, and according to: https://dev.mysql.com/doc/refman/5.7/en/memory-use.html

A buffer pool that is too large may cause swapping due to competition
for memory.

Given this, does it make sense to:

Increase innodb_buffer_pool_instances to 64, even though there are
only 32 vCPUs? I'd need to increase innodb_read_io_threads and
innodb_write_io_threads as well, yes?

Increase innodb_buffer_pool_chunk_size so the calculation above is less than 1000?

EDIT 1:
I have been plotting mutex waits and the one related to buffer pool (resizing?) is not only a lot higher than the next, but it seems to be growing in the past couple of days at a higher rate.

Our DB is very I/O intensive and originally there were higher mutex waits related to AHI, which didn't really have a good hit rate, so we disabled that.


texts: I am using the postgres.app. I get a terminal window when I start the postgres app. However, when I go into a bash shell and type in psql it says Command not found. I assume the path to the app is not set in my profile. How can I find out where the postgres.app is located and how do I tell that my profile?


texts: Extract stats from datetime intervals in MySQL

texts:
I have a MySQL database with some table stored as InnoDB type. One of them is work_session and its structure is:

`id` int(10) unsigned auto_increment primary
`project_id` int(10) unsigned index
`begin` datetime
`end` datetime

The table is used to register the time spent for each project. Of course in each row end is bigger than begin. Multiple rows may have the same project_id but intervals denoted by begin and end of rows with the same project_id don't overlap.

My aim is to extract some stats from this table. In particular:

Time spent for each project_id
Time spent in each day for each project_id, from the first recorded activity to now
Time spent by hour of the day for each project_id
Time spent by day of the week for each project_id

I was able to achieve this using PHP but I want to learn how to do it with MySQL.

I was able to obtain the first stat with

SELECT
  `project_id`,
  SUM(`work_session_duration`) AS `project_duration`
FROM
  (SELECT
    `project_id`,
    TIMESTAMPDIFF(SECOND, `begin`, `end`) AS `work_session_duration`
   FROM `work_sessions`) AS `t1`
GROUP BY `project_id`

The other three stats are very similar, in particular the last two, but I'm not able to figure out a way to them with MySQL. Any hint, suggestion, link to some MySQL reference its appreciated.


texts: Specifying Order of Executing Stored Procedures

texts:
Issue:

The order in which my stored procedures are called produces no errors if run independently, but when run as once, yields the following error:

Msg 2627, Level 14, State 1, Procedure sp_ETL_S1_tbl1, Line 40
Violation of PRIMARY KEY constraint 'PK__tbl1__EE1C17C10200000'. Cannot insert duplicate key in object 'S1.tbl1'. The duplicate key value is (0).
The statement has been terminated.

Hypothesis:

I believe this is due to the first stored procedure containing the DROP TABLE statements, and for some reason it doesn't get called first when all the stored procedures are run together.

Offending Code:

mssql
execute_sps.sql

USE db1;
GO
EXEC sp_ETL_db1_CreateSchemas;
   EXEC sp_ETL_dbo_tbl1;
   EXEC sp_ETL_dbo_tbl2;

Procedures above defined:

Stored procedure to create schema and tables.

sp_ETL_db1_CreateSchemas.sql

USE db1;

DROP PROCEDURE sp_ETL_db1_CreateSchemas;
GO
CREATE PROCEDURE sp_ETL_db1_CreateSchemas
AS

GO
DROP TABLE S1.tbl1
DROP TABLE S1.tbl2

DROP SCHEMA S1;
GO
CREATE SCHEMA S1;
GO
CREATE TABLE S1.tbl1(
    SalesGroupID       varchar(20)
        NOT NULL         PRIMARY KEY
,   SalesGroupType     int
,   SalesGroup         varchar(20)

)
;
GO
CREATE TABLE S1.tbl2(
    [NameID]                    int
        NOT NULL                    PRIMARY KEY
,   [FirstName]                 varchar(50)
,   [MiddleName]                varchar(50)
,   [LastName]                  varchar(50)
,   [LastUpdated]               datetime
)
;

Stored procedures to ETL data from (olddb.tbl1, olddb.tbl2) to (db1.S1.tbl1 , db1.S1.tbl2)

USE db1;
GO
DROP PROCEDURE sp_ETL_S1_tbl1;
GO
CREATE PROCEDURE sp_ETL_S1_tbl1
AS

SET NOCOUNT ON
IF object_id('tempdb.dbo.#SalesGroup') IS NOT NULL
BEGIN DROP TABLE #SalesGroup END

SELECT
    [SalesGroupID]
,   [SalesGroupType]
,   [SalesGroup]
INTO #SalesGroup
FROM olddb.tbl1
;

INSERT INTO db1.S1.tbl1(
    [SalesGroupID]
,   [SalesGroupType]
,   [SalesGroup]
)
SELECT
    [SalesGroupID]
,   [SalesGroupType]
,   [SalesGroup]
FROM #SalesGroup

USE db1;
GO
DROP PROCEDURE sp_ETL_S1_tbl2;
GO
CREATE PROCEDURE sp_ETL_S1_tbl2
AS

SET NOCOUNT ON
IF object_id('tempdb.dbo.#Names') IS NOT NULL
BEGIN DROP TABLE #Names END

SELECT
    [NameID]
,   [FirstName]
,   [MiddleName]
,   [LastName]
,   [LastUpdated]
INTO #Names
FROM olddb.tbl2
;

INSERT INTO db1.S1.tbl2(
    [NameID]
,   [FirstName]
,   [MiddleName]
,   [LastName]
,   [LastUpdated]
)
SELECT
    [NameID]
,   [FirstName]
,   [MiddleName]
,   [LastName]
,   [LastUpdated]

FROM #Names


texts: Do I need to enable Trace Flag 1117 for equally sized data files?

texts:
I was reading about fill proportional algorithm in SQL Server and then I recalled TF1117. BOL states:

  When a file in the filegroup meets the autogrow threshold, all files
  in the filegroup grow. This trace flag affects all databases and is
  recommended only if every database is safe to be grow all files in a
  filegroup by the same amount.

What I can't understand is if data files are filling proportionally, won't they auto-grow proportionally either? In that case we can't omit using TF1117.

EDIT

Here is the version of SQL Server


texts:
I have the following schema:

table_1
id, updated_at

table_2
id, updated_at

table_3
id, updated_at

and in total there are about 12 tables that I want to get the updated_at column and determine the MAX() aggregate value from all of them, based on a single id value.

How would I set up this query?


texts: Does Oracle Streams replicate unused columns?

texts:
Since you can now mark columns as unused with

alter table <table> set unused column <column>

are columns, set as unused, replicated by Streams? Or is it configurable by rules or restrictions?

It would be an efficient way to delete unused columns from the dictionary by just using streams and switching the database at some point to get free from all the unused columns data.


texts: sql server maintence plans. What account to use?

texts: When setting up a maintenance plan specifically for backups, what account should you use as the owner.  Currently we use the domain account that sql server runs as.  Is this best practice or should we create a local account / domain account just to run jobs?


texts:
I need to fix an SQL statement in MySQL that should calculate a field using values from two of the columns and I prefer to do this using set-based programming, ie not procedural. What needs to happen is that in a separate column called delta the value of level is copied depending on whether in_event is 0 or 1. If it is 1, then the value of level is copied, else the last known value of delta is copied.

This is similar to how data is copied down in spreadsheets for underlying cells that have no value, except that in this case data is copied up instead of down.

These are the statements I am using:

  create table events (
  date date default null,
  sortorder int(11) not null default '0',
  level float default null,
  in_event int(1) not null default '0'
);

insert into events values
('2018-09-04', 2, 1547.8, 0),
('2018-09-03', 3, 1571, 1),
('2018-08-31', 4, 1541.6, 0),
('2018-08-30', 5, 1575.4, 0),
('2018-08-29', 6, 1596.6, 0),
('2018-08-28', 7, 1626.4, 0),
('2018-08-24', 8, 1575, 0),
('2018-08-23', 9, 1536, 0),
('2018-08-22', 10, 1579, 0),
('2018-08-21', 11, 1559, 1),
('2018-08-20', 12, 1554, 0),
('2018-08-17', 13, 1534, 0),
('2018-08-16', 14, 1543, 0),
('2018-08-15', 15, 1542.2, 0),
('2018-08-14', 16, 1646.8, 0),
('2018-08-13', 17, 1680.4, 0),
('2018-08-10', 18, 1667.6, 0),
('2018-08-09', 19, 1711.6, 0),
('2018-08-08', 20, 1726.6, 0),
('2018-08-07', 21, 1736.2, 1);

I have gotten as far as the following, but it copies the data down instead of up:

alter table events add column delta decimal(10, 2);
update events set delta = level where in_event = 1;
select date, sortorder, level, in_event, case when delta is not NULL && @d := delta then delta else @d end as delta
from events
order by date desc;

This is the result that I am looking for:

insert into events values
('2018-09-04', 2, 1547.8, 0, 1571),  -- value of 1571 is copied up, because it is the last known value and in_event is not 1
('2018-09-03', 3, 1571, 1, 1571),    -- value of 1571 is attained, because in_event is 1.
('2018-08-31', 4, 1541.6, 0, 1559),
('2018-08-30', 5, 1575.4, 0, 1559),
('2018-08-29', 6, 1596.6, 0, 1559),
('2018-08-28', 7, 1626.4, 0, 1559),
('2018-08-24', 8, 1575, 0, 1559),
('2018-08-23', 9, 1536, 0, 1559),
('2018-08-22', 10, 1579, 0, 1559),
('2018-08-21', 11, 1559, 1, 1559),
('2018-08-20', 12, 1554, 0, 1736.2),
('2018-08-17', 13, 1534, 0, 1736.2),
('2018-08-16', 14, 1543, 0, 1736.2),
('2018-08-15', 15, 1542.2, 0, 1736.2),
('2018-08-14', 16, 1646.8, 0, 1736.2),
('2018-08-13', 17, 1680.4, 0, 1736.2),
('2018-08-10', 18, 1667.6, 0, 1736.2),
('2018-08-09', 19, 1711.6, 0, 1736.2),
('2018-08-08', 20, 1726.6, 0, 1736.2),
('2018-08-07', 21, 1736.2, 1, 1736.2);


texts:
Given these 2 tables:

CREATE TABLE aaa (
  x number primary key,
  y varchar2(10))

CREATE TABLE bbb (
  x number primary key,
  y varchar2(10))

I insert two columns:

insert into aaa values (1, 'foo')
insert into bbb values (1, 'bar')

Then, I query for:

select a.x, a.y, b.y from aaa a
LEFT JOIN bbb b on a.x = b.x

which returns:

1 foo bar

Then, I insert a row with x = 2 into bbb:

insert into bbb values (2, 'bar')

Re-running the above query returns the same result.

However, I'd like to write a query to get a result of:

1 foo bar
2 null bar

Can I achieve that generically without doing:

select b.x, b.y, a.y from bbb b
LEFT JOIN aaa a ON b.x = a.x


texts:
I have some questions regarding backup and restore strategies.

I want to take backup and restore of large databases approx. 60 to 70 GB and it is a critical database on SQL Server.

Backup schedule approx. I decided to:

Full backup every day
Differential backup every 2 or 3 hours
Transaction-log backup every 15 minutes

Is this a correct approach? If not then please suggest better option.

Which recovery model is best suited? (Simple, Full, Bulk-logged)

In case any of the backup files (full backup file, differential backup file or log backup) are corrupted, then what are the options to recover database quickly with minimum data loss?


texts: Query to join two subsets of table data (get it faster)

texts:
TABLE1:

+-----+----------------------+------+-------+
| id  |        value         |weight|  stat |
+-----+----------------------+------+-------+
|  5  | OB_4772FB7E65        |    5 |     1 |
|  6  | OB_A339546708        |    6 |     1 |
|  7  | OB_6CA632C6BC        |    7 |     1 |
|  8  | OB_58757E6E9C        |    8 |     1 |
|  9  | OB_8FF337BE76        |    9 |     1 |
|  10 | OB_CCA8FEC422        |   10 |     1 |
+-----+----------------------+------+-------+

TABLE2 (it's a translation table, where there is always an entry with lang_id=5 and there may or may not be a translation in other languages (here I show only one another lang_id=10). The key keycode here is the same as value in TABLE1) :

+---------------+-----------------+--------+
| id  | lang_id  |    keycode     | teksts |
+---------------+-----------------+--------+
| 940 |  5       | OB_4772FB7E65  | suga1  |
| 941 |  10      | OB_4772FB7E65  | spec1  |
| 946 |  5       | OB_A339546708  | suga2  |
| 947 |  10      | OB_A339546708  | spec2  |
| 949 |  5       | OB_6CA632C6BC  | suga3  |
| 955 |  5       | OB_CCA8FEC422  | suga4  |
| 956 |  10      | OB_CCA8FEC422  | spec4  |
+---------------+-----------------+--------+

The needed result (get all keycodes with stat=1 and their translation or an empty string if there is no translation. The results must be sorted first by weight and then by basis language):

+----+----------------+-----------+
| id |   keycode      | teksts_ln |
+----+----------------+-----------+
|  5 | OB_4772FB7E65  | spec1     |
|  6 | OB_A339546708  | spec2     |
|  7 | OB_6CA632C6BC  |           |
| 10 | OB_CCA8FEC422  | spec4     |
+----+----------------+-----------+

I have managed to write a mysql query, it works ok but it is slow. Maybe I have overdone and it can be simplified?

SELECT glv.id, glv.keycode, gln.teksts_ln FROM
(SELECT t1.id, t1.weight, t1.value AS keycode, t2.teksts AS teksts_lv FROM TABLE1 AS t1 LEFT JOIN TABLE2 AS t2 ON t1.value = t2.keycode AND t2.lang_id=5 WHERE t1.stat="1") AS glv
INNER JOIN
(SELECT t1.value AS keycode, t2.teksts AS teksts_ln FROM TABLE1 AS t1 LEFT JOIN TABLE2 AS t2 ON t1.value = t2.keycode AND t2.lang_id=10 WHERE t1.stat="1") AS gln
ON glv.keycode = gln.keycode ORDER BY glv.weight ASC, glv.teksts_lv ASC;

My main concern is how to make the query work faster.


texts: My mysql database crashed, how to recover

texts:
I have a mysqldump of all databases, including mysql.

I'm using mysql 5.6 on Windows 2008 R2. How can I restore my backup ?

The problem is that I cannot start the server when the database mysql is missing.

Edit:
This is what happened exactly:

I got this error while running some query:

  [ERROR] Out of memory; check if mysqld or some other process uses all
  available memory; if not, you may have to use 'ulimit' to allow mysqld
  to use more memory or you can add more swap space

I tried to restart mysqld, but it did not restart because of this error in the log:

  InnoDB: Database page corruption on disk or a failed
  InnoDB: file read of page 2568.
  InnoDB: You may have to recover from a backup.