texts: Mixed granularity weeks/months time series data in a data warehouse?

texts:
I'm not sure that "anti-aliasing of time series data" is the correct terminology to use, so let me explain:

I have some sources of data that are aligned quarterly, mostly to do with quarterly running costs. I have some other sources of data, some of which are aligned weekly, and some monthly, and these are mostly related to transactions that took place. By the term 'anti-aliasing', I am referring to the problem of how to represent this data on a common time granularity, so that it can all be integrated together into a data warehouse.

How do I get this data which does not align through time, into a data warehouse? The alignment problem mostly stems from the fact that weeks do not exactly fit into months or quarters. The months against quarters issue is less of a problem, since 3 months make a quarter.

For example, if I know the quarterly running cost, should I divide that up, and attribute an equal share of it to each day in the quarter. Then if I know the number of transactions completed on a weekly basis, should I divide them up and attribute them equally to each day of the week. That would artificially break down the grain of the data into days, and allow me to roll it up by week, month or quarter.

(Is there a more correct terminology in data-warehousing for this problem?)


texts: Is there a way to avoid having chained/nested “replace()” items when using an ISO 8601 timezone offset value in a generated column?

texts:
I am using MySQL 5.7 and have a table that has the following columns that are being used to store some data that is source from an Apache common log formatted access log; details extracted from a MySQL schema export:

`timestamp` timestamp NULL DEFAULT NULL,
`offset` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
`date` date GENERATED ALWAYS AS ((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour)) VIRTUAL
`time` time GENERATED ALWAYS AS ((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour)) VIRTUAL
`hour` int(2) GENERATED ALWAYS AS (hour((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour))) VIRTUAL

As you can see I am storing timestamp (0000-00-00 00:00:00) and offset (+00:00) and then using generated columns to calculate, date (0000-00-00), time (00:00:00) and hour (0) values. And this is working well: I get to store the timestamp as UTC and then—by storing the offset value—I can dynamically get other info in non-UTC form.

But I don’t think the chained/nested REPLACE items are too hot. The goal is to be able to take an ISO 8601 timezone offset value like -0400 or +1000 as part of an interval [some value] hour calculation.

So is there a better way to approach this? I might be able to adjust the way that the timezone offset is stored initially so it is—essentially—exactly what I need for column calculations, but that seems messy and non-intuitive so I would rather use that +/-[for digit] format if possible.


texts: Should I install SQL Server 64 bit or 32 bit

texts:
Sorry if this question is lame.

For production purpose, i wanted to know when to install SQL Server 64 bit package and when to install 32 bit package when the client's PC has: -

32 bit OS and 64 bit Processor
64 bit OS and 32 bit Processor

I am just confused what to install when there is a mix and match in bits of OS and processor.


texts: list of views that depend on a given table and column

texts:
I need a script to check if a given column in a given table is used in any views.  Ideally I would like to get the name of the view(s) as well.
I am using T-SQL, and I need to run the script to check this inside an application I am developing.


texts: Is it possible to allow missing value inserts in mysql instead of requiring "NULL" in the query?

texts:
I am writing a script to insert values from a file into a mysql database.  The script reads lines from the file and constructs insert queries from them.  Sometimes particular columns are empty,  so an insert query might look like this...

insert into table (col1, col2, col3, col4) values('abc',100,,'def')

where col3 allows nulls, and also has NULL as its default value.

This insert query throws an error,  while if I ran it in MS SQL it would work, and insert null where the insert query has nothing between the commas.

Is it possible to acheive this behaviour in mysql?

I tried doing a replace of ,, with ,NULL, but this fails when there are two empty values in a row - ,,, (I end up with ,NULL,,) and also adds extra processing (and therefore extra time taken) to the code.   If there is a way to allow insert queries like insert into table (col,col,col) values(val,,val) I could avoid having to replace nothing values with 'NULL' when constructing the insert query string.

Edit:

For convenience I include below the code I have written to work around this problem.  My code is in a windows script file (vbscript) (not tagged due to it not being directly relevant to the question being asked, but I'll tag it if it should be)

So here is code to replace the 'nothings' with NULL...

row = split(arfile.readline,"|")

for i = 0 to ubound(row)

    row(i) = rtrim(row(i))
    if row(i) = "" then row(i) = "NULL"

next

Then further down I have a line that combines them all into an insert query (actually it's more complex, it creates 100 insert rows and then executes the command and then creates another 100)

As part of the construction of the query, some values get single quotes added, because they are string or other types that need it.  For that reason, wherever there has been a NULL replacement it becomes 'NULL' so I need to also deal with this...

conn.execute( insertquerystart & replace(insertquery,"'NULL'","NULL") & ";")


texts: Is this the correct/best way to create a custom column in mysql?

texts:
I needed to select certain records from a selection with a custom column that have another column's modified value as its own value. The following query just works for my needs. But I wanted to know if it's the best/right way of doing this.

/* this is just OK */
SELECT column1,column2,SUBSTRING(column2,4) AS custom_column
FROM MY_TABLE
WHERE SUBSTRING(column2,4) = 'xyz'

Also, I saw I cannot simply say

WHERE custom_column = 'xyz' /* error: custom_column doesn not exixts */

It would throw an error. That's because I used the syntax above instead. But it feels strange to me.


texts: Is there a policy or special server configuration to limit SQL Server query results?

texts:
I have been seeing developers using SELECT * FROM table when testing their queries.

The issue is that many developers are executing such SELECT statements on huge tables, which slows down SQL Server performance.

We have told them to use SELECT TOP 100 * FROM table, but they don't do it.

Is there a policy or special configuration to make SQL Server show limited results (i.e. 100 rows) on huge tables? If so, which SQL Server version does that?


texts: PGBouncer Pause Waits for Remote Connection to Close

texts:
I'm testing the pgbouncer "pause" feature to minimize database downtime when a database restart is required.

For this test, I have a remote psql session connected to my database through pgbouncer.  Pgbouncer SHOW CLIENTS shows my remote session as Active even with no queries running.  The same client shows Idle according to pg_stat_activity.  When I issue the PAUSE command, it hangs until I close my remote session.  Shouldn't PAUSE work without closing my connection?


texts: Determine Oracle session client character set?

texts:
I know how the database characterset (NLS_CHARACTERSET in select * from v$nls_parameters;) and the client character set (the client environment setting NLS_LANG) interact.

What I can't find out however, is how or if I can determine, for an established session, what Oracle thinks the current client characterset is.

Is this possible at all?

Note: SELECT * FROM NLS_SESSION_PARAMETERS; does not include the character set (on 10g2).

To make absolutely clear what I'd like to accomplish:

NLS_LANG is set in client environment to an arbitrary value (for example GERMAN_GERMANY.WE8MSWIN1252)
Database application[*] starts and establishes a connection/session to the Oracle database.
Database application[*] wants to "ask" Oracle (not its OS environment) what the client character set is Oracle will assume.

[*]: If the db application is sqlplus, the example would look as follows:

...
sqlplus /nolog
connect user/pass@example
*magic command*;
   CLIENT CHARACTERSET = ...

Jack's note in his answer raises two important points:

With Oracle, who does the characterset translation. Is it the client-library code or is it done on the server side?
As it appears it is the client, the client would need expose this setting -- what the client lib/tool assumes this setting is. Is there any of the Oracle client libs/tools (sqlplus, OCI/OCCI, Pro*C, ...) that can be queried for what it thinks this setting is?


texts: All four data nodes in MySQL Cluster in same node group

texts:
I am testing MySQL Cluster 7.2. I have two servers, mysql1 and mysql2. I want the management server and two data nodes to run on mysql1 and two other data nodes to run on mysql2. My config.ini file looks like this:

[ndb_mgmd]
hostname=mysql1
datadir=/var/mysql-cluster/ndb_data
NodeId=1

[ndbd default]
noofreplicas=2
datadir=/var/mysql-cluster/ndb_data
DataMemory=8M

[ndbd]
hostname=mysql1
NodeId=3

[ndbd]
hostname=mysql2
NodeId=4

[ndbd]
hostname=mysql1
NodeId=13

[ndbd]
hostname=mysql2
NodeId=14

[mysqld]
NodeId=50

With this configuration I would expect, as per the MySQL Cluster documentation, that the two data nodes 3 and 4 would be in nodegroup 0, while the two data nodes 13 and 14 would be in nodegroup 1.

However, when I start everything up and show the nodes, I see this:

Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     4 node(s)
id=3    @192.168.0.42  (mysql-5.5.30 ndb-7.2.12, starting, Nodegroup: 0, Master)
id=4    @192.168.0.43  (mysql-5.5.30 ndb-7.2.12, starting, Nodegroup: 0)
id=13   @192.168.0.42  (mysql-5.5.30 ndb-7.2.12, starting, Nodegroup: 0)
id=14   @192.168.0.43  (mysql-5.5.30 ndb-7.2.12, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.42  (mysql-5.5.30 ndb-7.2.12)

[mysqld(API)]   1 node(s)
id=50 (not connected, accepting connect from any host)

Everything seems to be in nodegroup 0! What do I have to do to get 3 and 4 in one group and 13 and 14 in another?


texts: Do data checksums apply to large objects?

texts: PostgreSQL 9.3 introduced the data checksums feature (initdb -k). Do these checksums apply to large objects?


texts: Msg 2534 : DBCC CHECKDB allocation errors

texts:
We recently moved our intranet production database from an SQL Server 2008 to an SQL Server 2008 R2 brand new server.

The backup Job have been failing since migrating, due to DBCC Errors (Check database integrity task).

I tried to run a DBCC CHECKDB using the repair_rebuild option on the production database yersterday, and the a subsequent DBCC CHECKDB showed no errors.
The DBCC CHECKDB against the production databas  is showing again error messages, the backup is done, but of course the maintenance plan fails: I have (a long list of)  the following error messages:

Table error: page (1:1100029), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100030), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data) due to invalid allocation (IAM) page(s).
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100031), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100200), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100201), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
There are 14 rows in 3 pages for object "searchfull_dummy_1049".
CHECKDB found 0 allocation errors and 6 consistency errors in table 'searchfull_dummy_1049' (object ID 373210913).
DBCC results for 'foldersRecherche'.
There are 0 rows in 0 pages for object "foldersRecherche".
DBCC results for 'wf'.
There are 0 rows in 0 pages for object "wf".
DBCC results for 'searchfull_dummy_1663'.

I tried to use yesterday's backup , restored the database on the same server with a new db name, and ran a DBCC Update usage(0) on the database, then DBCC CHECKDB showed no more errors on the newly restored database.

I applied the same procedure to the production database but this did not fix the issue on the production database. I'm getting the same errors as above.


texts: How do I get the difference in minutes from 2 timestamp columns?


texts: What is the difference between Schema and Database in Oracle?

texts:
In Oracle, datafile are system files where actual data is stored. The collection of datafiles make tablespace and, at last, database is collection of tablespaces.

Correct me if I am wrong on the concepts of datafile, tablespace and database.

I would like to understand the difference between Schema and Database in details. Online resources are helpful but seemed confusing, regarding this difference.


texts: MYSQL - Problem with foreign keys on same table

texts:
In Mysql 5.5, I have a table, in which I store some informations about "events".. In order to have a chronological order, I made two columns that reference to the previous and to the next event. So I made two foreign keys that reference to the primary key of the same table.
If a row is the first in the chronological order, the value of the previous element would be null.

So, my table is:

calendars
--------
calendar_id (PRIMARY KEY)
calendar_prev
calendar_next
other columns...

I have this data (as an example)

+--------------+---------------+---------------+
|  calendar_id | calendar_prev | calendar_next |
+--------------+---------------+---------------+
|       1      |    NULL       |       2       |
|       2      |      1        |       3       |
|       3      |      2        |       4       |
|       4      |      3        |       5       |
|       5      |      4        |     NULL      |
+--------------+---------------+---------------+

and these foreign keys.

ALTER TABLE `calendars`
ADD CONSTRAINT `calendars_ibfk_3` FOREIGN KEY (`calendar_next`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `calendars_ibfk_2` FOREIGN KEY (`calendar_prev`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE;

This works fine on my server and on sqlfiddle.

The problem comes when I try to modify the ID of an element.
If I'm not missing something about how foreign keys work, if I edit the calendar_id 5 and set it, for instance, to 7, the row with calendar_id = 4 should change the calendar_next value as well, from 5 to 7. But i just get an error.
In other words, if I try this:

UPDATE `calendars` SET `calendar_id` = 7 WHERE `calendar_id` = 5;

I get this error:

 Cannot delete or update a parent row: a foreign key constraint fails (`db_2_813f44`.`calendars`, CONSTRAINT `calendars_ibfk_3` FOREIGN KEY (`calendar_next`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE):

I really can't figure out what I am doing wrong.


texts: Is there any way, in SQL Server, to cause a batch to time out if its CPU usage exceeds some value specified in the batch?  I need this to occur at the batch, and not the connection level.


texts: Synchronous SQL Server Replication only for specified subscribers

texts: I would like to create database replication between multiple localisation. There will be one publisher and multiple subscribers. Is it possible to define that synchronisation is synchronous only for one subscriber and for the other is asynchronous operation. Is it possible to dynamically modify which subscriber will get data synchronously? Are there any external tools allowing to do that?


texts: mongorestore only one field of a document

texts:
i'm trying to restore my users :

{
  _id: ObjectId,
  created_at: Date,
  preferences: String
}

I don't know why but created_at field from my users all reseted. So i need to use my .bson backup file to restore this field but i need to only edit created_at field.

Any idea ?


texts: Can't run rs.initiate() when do mongodb replication set

texts:
Using CentOS 7.

MongoDB version: 3.2.15

Hostname: node1

Make directory:

mkdir /mongo-metadata

/etc/mongod.conf

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /mongo-metadata
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1  # Listen to local interface only, comment to listen on all interfaces.

#security:

#operationProfiling:

replication:
  replSetName: rs0

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:

Start the replication member:

mongod --config /etc/mongod.conf

Run mongo:

mongo
# try
rs.initiate()
{
        "info2" : "no configuration specified. Using a default configuration for the set",
        "me" : "node1:27017",
        "ok" : 0,
        "errmsg" : "No host described in new configuration 1 for replica set rs0 maps to this node",
        "code" : 93
}

# try
config = {
    _id : "rs0",
     members : [
         {_id : 0, host : "node1:27017"},
         {_id : 1, host : "node2:27017"},
     ]
}
rs.initiate(config)
{
    "ok" : 0,
    "errmsg" : "No host described in new configuration 1 for replica set rs0 maps to this node",
    "code" : 93
}

Reference:

https://docs.mongodb.com/manual/reference/command/replSetInitiate/

So what's the reason? It can't realize the hostname?


texts: How can I extract a list of dependent tables from an execution plan?

texts:
Is there a way to extract a full list of tables and views used in a query?

I need to replicate a portion of a database so that we can avoid locking tables when a long query runs.

I have the execution plan. Is it possible to extract all the database objects the query touches?


texts:
I'm a web developer looking to leverage more of the power of my RDBMS. I'm currently learning about normalization of data and database design.

In the book that I am reading, we started off with one table that contained information about students, teachers, tests, grades, etc, and broke that down into separate tables for each entity.

However, I'm looking at the test table (see picture below), and I am wondering if this table also violates a few design principles.

In particular, I think that the Test column violates the "no redundant data" principle, and the "no partial dependencies principle". (On this second point, I am assuming that the primary key for this table is a composite key of TestID and TeacherID, in which case the Test column depends only on the TestID part of the primary key).

What I would do in this case is further break up the test table into a Tests table containing the a TestID as a primary key, Name as the name of the test, and TotalPoints. Then, I would create a AdministeredTests table that contains a TestId, a TeacherId, and a Date, and the primary key would be a composite of TestId and TeacherId.

Would I be on the correct track here? I'm asking because the author also shows the following table:

Again, this suffers from redundant data (although it has no partial or transitive dependencies). Based on my current understanding, I think the author simply has not introduced join tables yet, hence the reason why normalization "stops" at a certain point. Also, the author is quick to point out: "...we presented one possible database design as an illustration of how to normalize data. In truth, many possibilities exist for designing this database." Just wondering the possibility I presented here is reasonable.


texts: How to update JSON using logic equivalent to WHERE clause

texts:
My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a specific key-value pair, and then update the value.

Here is a mock-up of my JSON structure using dummy values:

declare @data nvarchar(max);
set @data = N'{"add":[{"id":-1,"valA":7,"valB":8,"valC":"text","valD":{"3":"text"}},'
+ '{"id":-2,"valA":5,"valB":8,"valC":"Moretext","valD":{"2":"textB","3":"Moretextagain"}}'
+ '{"id":-1,"valA":9,"valB":8,"valC":"SecondCase","valD":{"2":"textX","3":"MoretextagainSigh"}}'
+ ']}';
select * from openjson(@data);

Basically the problem is: if you can find "id":-1 then update it to "id":999.

(The use case is that the data here are linked to other data and none of it has been persisted. The other data is identified by the negative ID values. After persisting the other data I get a "real" ID back from the database. I now need to update this linked data to remove the negative ID and replace it with the one that the database provided.)

From the other question I can see one option is to extract all of this into a table, perform the update and rebuild the JSON. I feel the structure is non-trivial (for example, the object for valC may have different numbers of elements). It's simple, yes, but outputting all this to a table then rebuilding it - I think is not simple.

I really want this pseudocode to work:

update @data
set 'add.id' = 999
where 'add.id' = -1;


texts: SQL server tools to identify query optimisations and missing indexes

texts:
I have a broad question regarding SQL Server that was given to me on a quiz and I am not sure what the answer is as I do not have access to SQL Server at home. My question is how can I utilise SQL Server tools to identify query optimisations, missing indexes etc?

Can somebody give me a very brief answer to this?

Thank you


texts: Avoid PLE drops while performing DELETE operations

texts:
If my quesion is not specific enough please tell what details are missing.
We are runing daily archive operations on the whole database where we keep data from the last 30 days. We are deleting data in 100 records batches. For one particular table Which has the biggest(in terms of size) records it take around 3-5 hours to delete all 2 millions of old records even just after index maintenance operations. Also deletions of that particular table causes PLE to drop. My question is how to accelerate that proces and avoid PLE drops?


texts: Update value in same view using CASE expression

texts:
I am creating a view where i have daily data of different items and each item has a state. I created a column that shows what the column was yesterday using

LAG(state) OVER(PARTITION BY item_id ORDER BY currentdate) AS yesterday_state

Now I want to count how long the state has been the same value and im doing so with this:

COUNT (CASE WHEN state = yesterday_state THEN state ELSE NULL END) OVER(PARTITION BY item_id ORDER BY currentdate AS state_age

This is working properly but I need to find a way to set the value back to 0 when state != yesterday_state

This all is happening inside of SELECT statement as I'm creating a view. How could I go around doing this so that the state_age sets to 0 when state is not the same value as yesterday_state

SOLUTION:

CREATE VIEW view AS (
    WITH
        cte1 AS (SELECT *, LAG(state) OVER(PARTITION BY item_id ORDER BY currentdate) AS state_yesterday
            FROM table),
        cte2 AS (SELECT *, CASE WHEN state = state_yesterday THEN 1 ELSE 0 END AS state_remained
            FROM cte1),
        cte3 AS (SELECT *, CASE WHEN state_remained != 0 THEN SUM(state_remained) OVER(PARTITION BY item_id, state ORDER BY currentdate) ELSE 0 END AS state_age_days
            FROM cte2)
        SELECT *
        FROM cte3
)


texts: what replication option should I choose?

texts:
Just looking for some feedback here, there are lots of replication options out there, and I'd like to try something that those more expert than I have found to be successful.

Here's my scenario:

1 SQL STD 2008 R2 SP2, few desktop clients, many web-clients edit and read;
1 SQL ENT 2008 R2 SP2, desktop clients only;

lots of SP's and triggers;

Many (but not all) tables have App-managed Primary key (not an identity!) and/or a GUID;

Many (but not all) tables that have a GUID, use that GUID in 1-n relationships with 1 or more tables;
Some app-managed tables have neither a PK or a GUID;

The schema of the app-managed tables and sp's cannot be changed in order to satisfy a replication requirement (e.g remove @@rowcount or add not for replication contraint to a PK);

Schema changes will be frequent;

I'd like to try to achieve two-way replication, as updates can occur at either end. Redundancy (hot-swap, failover) is not an issue for us, as our business strategey isn't dependent upon 5-9's uptime, instead the ability to recover from failures, which we can do very well. Real-time replication may be very limited due to horrific WAN performance, is log shipping a good solution here? The goal is to make a DB available to clients in two physcial locations whom cannot oridinarily communicate otherwise. In addition, having updates lag a few hours or even a day is not a roadblock, as long as we can force a synch if we need a more recent one. Icing on the cake would be the ability to have SQL express clients participate in this topology as well, giving our mobile clients the ability to grab a copy of the DB when they can, and check in edits when they can (again).

I look forward to hearing how others have achieved this sort of environment. Thanks!


texts:
I am getting error when trying to run the following query :

INSERT stock_daily_data (item_code,date,quantity_used,stock_left) select
distinct item_code,'2017-11-07',0,stock_left from ( select *, row_number()
over (partition by stock_daily_data.item_code order by stock_daily_data.date
desc) as rn from stock_item_detail join stock_daily_data on
stock_item_detail.item_code = stock_daily_data.item_code ) tt where rn = 1;

Error is :

The column 'item_code' was specified multiple times for 'tt'.

I am using mssql 2012.
I am quite naive to SQL .
Thanks in advance for helpful advice.


texts: Is there a way other than WKT to insert geometry to SQL Server?

texts:
I am using design data from *.dwg and *.dgn to insert geometries to SQL Server field which is set to geometry. All examples I see are using something called WKT which is a specification by a company called OGC but they do not offer any converters so not straightforward to convert from *.dwg / *.dgn to prepare the insert statement.

Is there a different way to the WKT syntax to insert into SQL Server?


texts: MySQL 5.5 - view all queries executed on certain table

texts:
I need to explore queries, which were executed on certain table.

For example, in Oracle we could investigate the V$SQL_TEXT, maybe something similar exists in MySQL 5.5?


texts: Should I use data type SERIAL for table id columns?

texts:
I found out that there is a mysql datatype called SERIAL, it appears to be designed for use as an id column. However some of my peers don't think that the real data type BIGINT implied by SERIAL is appropriate for tables that may never get big.

Is there any reason to avoid using SERIAL type for a table id column?

Are there any obvious caveats?


texts: "Heap Blocks: " Number meaning on Query Plan for a bitmap heap + index scan

texts:
What does the number next to Heap Blocks represent in the query plan? I'm not sure if it means the # of blocks (8kb per) of data located on disk representing your data, or in some way the bitmap, which correlates more closely to idea of exact/lossy.
I initially thought it was the former, but I saw in this video (https://youtu.be/UXKYAZOWDgk?t=753) that even with 4mb of work_mem, he could still hold ~2800 heap blocks, which would amount to like 22mb, correct?


texts: Can I have two `psql` sessions use the same database connection?

texts:
Is it possible to open two psql sessions that use the same database connection?

My goal is to show that a second user on the same connection can see uncommitted INSERTs, wherease a user on a different connection can't see INSERTs until the first connection runs COMMIT on its transaction.


texts: MSSQL Mirroring equivalent to Oracle Data Guard Snapshot Standby

texts:
Does MSSQL support an operating mode on the failover partner / mirror that is functionally equivalent to Oracle database's Snapshot Standby mode?

ie A temporary active copy of the replicated database that can be open for read/write but can be reverted back to a replication target without rebuild?


texts: drawback of including primary key creation along in the table creation script

texts:
Sample script:

CREATE TABLE [dbo].[Countries](
    [Id] [nvarchar](2) NOT NULL,
    [Name] [nvarchar](100) NULL
 CONSTRAINT [PK_dbo.Countries] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I was told by a DBA that I should not include the Primary Key  constraint in the table create script but I don't really understand.

Could anyone tell me why and what is the best practice in this situation (for example: create table first and add primary key later in alter table)?

Thanks,


texts: SQL - I have just Differential backup but database exists

texts:
I have Differential backup from my database.one table is deleted.my data is lost.i create that table again but i cannot restore Differential backup because sql wants a full backup first.if i get full back up an restore it with norecovery.when i want restore Differential backup sql get error.it says that:

  this Differential backup cannot be restored because the database has
  not been restored to the correct earlier state.

What should i do?


texts: SELECT multiple sensor values in one query

texts:
Background

I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of these values (the latest logged) one by one and displays them to the user. But currently this takes too long time because there are too many values that needs to be fetched, the fetch takes about 8ms per value and in total we talk about around 300ms increase in total page load time - for a relatively good page.

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL, --- stored as unixtime
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
);

The table is partitioned weekly on the Date column.

What I do now

So, what I do is the following. I select the largest value in each partition that is before the current date/time. and pick out the largest value.

SELECT TOP (1) ca.SensorValue, ca.Date
  FROM sys.partitions AS p
  CROSS APPLY
  (
  SELECT TOP (1) v.Date, v.SensorValue
    FROM SensorValue AS v
    WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
    AND v.DeviceId = @fDeviceId
    AND v.SensorId = @fSensorId
    AND v.Date <= @fDate
    ORDER BY v.Date DESC
  ) AS ca
  WHERE p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
  AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
  AND p.index_id = 1
  ORDER BY p.[partition_number] DESC, ca.Date DESC;

What I want to do

I want to select all values in one query. E.g. select the latest value for DeviceId=1 and SensorId=1,2,3,4,5. I have come up with the following so far, where I select with the IN keyword to get values for multiple sensors. However, I still need to group them and sort out the one with the highest date. I'm thinking of adding a GROUP BY clause, but don't know how to get it right (the ones I've tried has failed so far).

SELECT ca.SensorValue, ca.Date
  FROM sys.partitions AS p
  CROSS APPLY
  (
  SELECT TOP (1) v.Date, v.SensorValue
    FROM SensorValue AS v
    WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
    AND v.DeviceId = @fDeviceId
    AND v.SensorId IN (@fSensorId1, @fSensorId2, @fSensorId3)
    AND v.Date <= @fDate
    ORDER BY v.Date DESC
  ) AS ca
  WHERE p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
  AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
  AND p.index_id = 1
  ORDER BY p.[partition_number] DESC, ca.Date DESC;


texts: Best practices for generating unique multi-column keys for weak entities?

texts:
How should one generate non-unique, non-natural identifiers for weak entities?

For example, if order_id is the primary key for an order table, and (order_id, item_number) is the primary key for an order_item table with a foreign key on order_id, how best to generate item_number?

A few of possibilities come to mind, but none seem ideal:

Autoincrement item_number: the order_item entity is no longer weak, and the composite key is redundant.
Use a trigger to search for the current max item_number for a given order_id, then increment: if a row is deleted this could lead to reassigning a PK to a different record - that doesn't seem like a good idea? (edit: this can also be done without using triggers as explained in joanolo's answer)
Use a trigger to create a new sequence for every order_id, and somehow draw item_numbers from the appropriate sequence: this is functionally the desired behaviour, but seems like it would be a mess to implement. Is it even possible to reference a sequence by an order_id?

Edit - closely related (if not duplicate):

Create unique sequence per set of rows sharing the same foreign key value
Serial numbers per group of rows for compound key


texts:
I'm trying to find the best way to calculate percentiles for a dataset in bulk on Postgres, returning both the percentile and the value.

generate_series() plus percentile_cont() and lateral join will work, though this can be very slow with many percentiles, as percentile_cont() is called for each item in the series:

select percentile, value
from generate_series(0, 1, 0.2) as percentile,
lateral (select percentile_cont(percentile) WITHIN GROUP (ORDER BY SIZE)
  as value from mytable
) values

This is much faster but doesn't return the percentile corresponding to each value:

select unnest(
percentile_cont(
    (select array_agg(s) from generate_series(0, 1, 0.2) as s)
) WITHIN GROUP (ORDER BY SIZE))
from mytable

And trying to join that one with a second generate_series() seems to require an ugly hack to get a column to join on:

select percentile, value
from (select row_number() OVER () as rownum, value
    from (select unnest(
        percentile_cont(
            (select array_agg(s) from generate_series(0, 1, 0.2) as s)
        ) WITHIN GROUP (ORDER BY SIZE)) as value
        from mytable) s2
    ) s3
join (select row_number() OVER () as rownum, percentile
    from (select generate_series(0, 1, 0.2) as percentile) s) p
    on p.rownum = s3.rownum

Or taking another approach, ntiles() makes for a cleaner query, but values don't quite match with percentile_cont(). (Maybe they'd match with percentile_disc()?) And this is about half as fast as 2nd/3rd queries above.

select ((nt-1)/5::float) as percentile,  min(size) as value
from (select size, ntile(6) over (order by size) as nt
   from mytable
 ) as dt
group by nt
order by nt asc

I've gotten this far, but it's still not quite right, and something tells me there's a clean, performant approach out there. Any thoughts on how to improve on these?

EDIT: modifying Sven's answer slightly to return named columns:

SELECT unnest(
  (select array_agg(fraction) from generate_series(0, 1, 0.2) AS fraction)
) as percentile,
unnest(
  (select percentile_cont((select array_agg(s) from generate_series(0, 1, 0.2) as s)) WITHIN GROUP (ORDER BY SIZE) FROM mytable)
) as value;


texts: Restore/import tables from unsuccessful database dump file

texts:
I'm in an urgent need of restoring a backup dump taken three weeks ago which was not completed successfully. I'm using Oracle 11g database in nonarchive log mode. The export command used was as follows.

expdp "sys/********@TEST AS SYSDBA" DUMPFILE=Backup_01022017.dmp LOGFILE=BakupLog_01022017.log FULL=y

The backup log shows that the process terminated with the following error.

ORA-39097: Data Pump job encountered unexpected error -1089
ORA-39065: unexpected master process exception in MAIN
ORA-01089: immediate shutdown in progress - no operations are permitted

But, fortunately the necessary tables are seen as exported. Are there any ways by which I can restore these tables or can I import these tables?


texts:
On https://www.mysql.com/products/community/, the InnoDB Cluster is listed as part of the MySQL community edition, but when clicking the "Download MySQL Community Edition" link and further, I end up on the NDB cluster documentation. So I'm confused about whether the InnoDB cluster is only availabla via the $5000 enterprise package, or if it's available for free too.

Have anyone looked into this, and know if the InnoDB Cluster is available for free?


texts:
I have been seeing a lot of deadlocks occurring on a system with deadlock graphs similar to this one:

I have been advised that it is a multi-victim deadlock but I cannot re-create a deadlock graph such as this one. Also, why does it only show one victim?

Any help or advice would be most appreciated.

Andrew


texts: Improving query performance with two inner joins on tables that have > 40M rows

texts:
Context
I have three tables - nodes, edges and sections. This is a graph network of paths from open street maps where edges are created from two neighbouring 3D nodes (that give distance, elevation gain etc.) and sections are a collection of edges between decision nodes. In effect sections build on edges which build on nodes.
This is a Postgres 12.5 database running on AWS RDS service on a m5 Large instance.
In terms of size:
Nodes - 40M rows
Edges - 80M rows
Sections - 15M rows
The tables have the following indexes:
Nodes

nodeid

Edges

edgeid
edgestartnode
edgeendnode

Sections

sectioned
startnode

Aim
I have a routing algorithm that returns an ordered list of section ids that I then want to use in a query (below) to pull out the relevant information from the database (from the nodes, edges, and sections tables) so that I can plot it on a map and display relevant information to the user. The list of sections can contain thousands of values, with the query performance information related below to a list of length 8646.
db fiddle for query below here
SELECT row_to_json(results)
FROM (
SELECT id, edgeid, lat, lon, elevation AS elev, edgelength As dist, edgegradient AS grad
FROM (SELECT ord, sectionid as id, unnest(edgelist) as edges
FROM unnest(ARRAY[IDs])
WITH ordinality AS arg_list(id, ord)
INNER JOIN sections s on s.sectionid = id
ORDER BY ord) as ed_list
INNER JOIN edges e on e.edgeid = ed_list.edges
INNER JOIN nodes n on n.nodeid = e.edgeendnode) results;

Issue
If the database is 'cold' and hasn't been used in a geographical region recently, when this query runs it takes a very long time, with the following query plan here.
However, if the query or a similar query is run in a short time after it, the performance is much better, as shown in this plan, with a 100X improvement.
Question
I've done a lot of reading about trying to improve joins and tried implementing different techniques, like here and tried the following query (db fiddle here)
WITH sections_data(ord, id, edges) AS (
SELECT ord, sectionid as id, unnest(edgelist) as edges
FROM sections
JOIN   unnest(ARRAY[IDs]) WITH ORDINALITY t(sectionid, ord) USING (sectionid)
),
edge_data(ord, id, edgeid, end_node, dist, grad) AS (
SELECT ord, id, edgeid, edgeendnode as end_node, edgelength As dist,
edgegradient AS grad FROM edges , sections_data WHERE edgeid = edges
),
node_data(ord, id, edgeid, lat, lon, elev, dist,  grad) AS (
SELECT ord, id, edgeid, lat, lon, elevation as elev, dist, grad FROM nodes, edge_data WHERE nodeid = end_node
)

SELECT row_to_json(results) FROM (SELECT id, edgeid, lat, lon, elev, dist, grad FROM node_data ORDER BY ord
) results;

but when I tried implementing that it resulted in a slightly slower query for me. So is there a better way to do it than I have currently?


texts:
Please look at the following Mysql table: http://pastebin.com/b0NDSbdz

I've partitioned the table by sent_at and I'm making sure that most of the queries pass sent_at. do I need to include sent_at in all of the indexes?

do you spot any potential redundant indexes?

when I do include the sent_at in the indexes, does it have to be the first field in the index declaration?


texts: Merging into a self referential table with identity columns

texts:
I have a number of records I need to add/update in a table that represents a hierarchical tree structure. The table has three columns

Id(PK, IDENTITY, int) | Description(varchar(50)) | ParentId(FK(Id), nullable, int)

A null value in the ParentId indicates the root node of a tree.
If possible, I'd like to insert/update these records in a single operation but since the identity isn't assigned until a record is inserted I'm having trouble figuring out how to capture this so child nodes' ParentId can be populated.

So given an existing table:

Id | Description | ParentId
1  | 'Foo'       | NULL
2  | 'Bar'       | 1
3  | 'Fizz'      | 1
4  | 'Buzz'      | 3

Is it even possible to insert a new root node and it's child nodes in a single operation?

I've attempted combining MERGE with UPDATE but it's either not possible to reference the columns of INSERTED in the same operation that populates them or else I just don't know how to do it.


texts:
I'm facing the following problem.

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In

  current row format, BLOB prefix of 768 bytes is stored inline.

It appears just when I'm uploading a image to the database (it has <1Mb). Precisely, just 100kb.

I've tried a lot of things: to change the properties "max_allowed_packet", "innodb_log_file_size" (namely, to increase the size to 512M) and nothing...

I dont know the cause of the trouble.

To illustrate, the table

TABLE(
    `passeio` int(4) unsigned NOT NULL COMMENT 'identitificador do passeio',
      `data_inclusao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `nome_passeio` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `inicio` date NOT NULL,
      `fim` date NOT NULL,
      `por_que_ir` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `pdf_roteiro` mediumblob NOT NULL,
      `incluso` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `nao_incluso` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `valor_descricao` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `valor_vista` decimal(10,0) NOT NULL,
      `valor_total_parcelado` decimal(10,0) NOT NULL,
      `numero_parcelas` int(2) unsigned NOT NULL,
      `forma_pagamento` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `avisos_importantes` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `foto_principal` blob NOT NULL,
      `foto_2` blob NOT NULL,
      `foto_3` blob NOT NULL,
      `foto_4` blob NOT NULL,
      `foto_5` blob NOT NULL,
      `foto_6` blob NOT NULL,
      `foto_7` blob NOT NULL,
      `foto_8` blob NOT NULL,
      `foto_9` blob NOT NULL,
      `foto_10` blob NOT NULL,
      `foto_11` blob NOT NULL,
      `foto_12` blob NOT NULL,
      `foto_13` blob NOT NULL,
      `foto_14` blob NOT NULL,
      `foto_15` blob NOT NULL,
      `foto_16` blob NOT NULL,
      `foto_17` blob NOT NULL,
      `foto_18` blob NOT NULL,
      `foto_19` blob NOT NULL,
      `foto_20` mediumblob NOT NULL,
      `valor_entrada` decimal(10,0) NOT NULL,
      `data_partida` date NOT NULL,
      `local_partida_1` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_1` time NOT NULL,
      `local_partida_2` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_2` time NOT NULL,
      `local_partida_3` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_3` time NOT NULL,
      `local_partida_4` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_4` time NOT NULL,
      `local_partida_5` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_5` time NOT NULL,
      `local_partida_6` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_6` time NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;


texts: What is the meaning of "available RAM" for calculating MySQL max_connections

texts:
The question was asked previously, "how do I calculate MySQL max_connections" and this was the answer:

  max_connections = (Available RAM - Global Buffers) / Thread Buffers

Since I don't have enough reputation to add a comment to the existing question I had to ask a brand new question.

I can get the buffer values by executing : SHOW VARIABLES LIKE '%buffer%'; but what is the available RAM part of this equation exactly?

Is this value in bytes, kilobytes, megabytes?  Something else?  Also, do I use the available RAM shown in the resource monitor while my site is being used, or do I need to use the total amount of RAM installed on the machine?


texts:
When you create a partition scheme against a particular partition function, you specify a series of filegroups.  I cannot find the metadata store where this filegroup list can be found & queried.

For example, I execute

CREATE PARTITION FUNCTION [ABPF](int) AS RANGE LEFT FOR VALUES (10);
CREATE PARTITION SCHEME [ABPS] AS PARTITION [ABPF] TO ([PS1], [PS2], [PS3]);

I would like to then execute a query:

select * from <table(s)>

And return, PS1,PS2,PS3 in a table, preferably with an indicator that PS3 is the "next used" filegroup.

I've explored the following and cannot seem to see or find the answer:

sys.partition_functions
sys.partition_schemes
sys.partition_parameters
sys.partition_range_values
sys.partitions
sys.dm_db_partition_stats
sys.filegroups
sys.sysfiles
sys.database_files
sys.data_spaces
sys.allocation_units


texts: Hardware specs for MySQL database with millions of records

texts:
For 35 million records I have this table

CREATE TABLE IF NOT EXISTS `records` (
  `code` varchar(12) NOT NULL,
  `type` varchar(255) NOT NULL,
  `used` set('y','n') NOT NULL default 'n',
  PRIMARY KEY  (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The code is alphanumerical and unique. I just have to check if a code exists and whether or not it has been used.

What server specs would you recommend? Any other suggestions on arquitechture? We will probably be using Rackspace so if you have any experience with them that would be great.


texts:
We have a time history table named PRTH that has EmployeeID, PostDate (this is the day worked), and hours. Our ERP system does not track termination and rehire date history so I have been asked to look at the PRTH table to determine when an employee was terminated and rehired based on days without hours.

For example, if the last time I had hours was 1-1-17 and I had hours again on 3-1-17 then it should show my term date as 1-1-17 and rehire as 3-1-17. This appears to be an island and gap issue but I am drawing a blank.

    SELECT Employee, PostDate
    FROM dbo.PRTH
    WHERE Employee BETWEEN 400 AND 450
    ORDER BY Employee, PostDate

Employee    PostDate
400 2013-09-30 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-30 00:00:00
400 2013-10-30 00:00:00
400 2013-10-30 00:00:00
400 2013-10-30 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00

The sample data above should be divided into 3 groups: (2013-09-30, 2013-09-30), (2013-10-15, 2013-10-15) and (2013-10-30, 2013-10-31). If employee 400 worked 1-1-2015 and the next date he worked was 5-1-2016 then we would want to see emp 400, term 1-1-2015, hired 5-1-16. Then if he had another gap in time history we would want another line with those and so on throughout all of our employees.

I have created a master calendar and tried the below statement but it has millions of rows. If I could only capture each term and end date instead of every day without pay that would be great.

WITH CTE_GAPS AS
(SELECT Employee,
    LAG(P.PostDate) Over(Partition by P.Employee Order By    P.Employee,P.PostDate) GapStart ,
    PostDate GapEnd,
    (DATEDIFF(DAY,LAG(P.PostDate) Over(Partition by P.Employee Order By P.Employee,P.PostDate),P.PostDate)-1) GapDays
FROM dbo.PRTH P)

SELECT  P.Employee,
    C.Calendar_Date
FROM Master.dbo.Calendar C CROSS JOIN CTE_GAPS P
WHERE
GapDays>29 AND
C.Calendar_Date
    BETWEEN DATEADD(DAY,1,P.GapStart ) AND DATEADD(DAY,-1,P.GapEnd)
    GROUP BY P.Employee, P.GapStart, C.Calendar_Date


texts: SQL server CPU 100% - Turn on Optimize for Adhoc Workload

texts:
Experiencing high CPU the more connections there are, no blocking or long running queries on the server.

Highest wait types are:
SOS_SCHEDULER_YIELD & CX_PACKET

Cache Usage %:

UsrTab   0.01
Default  0.01
Rule     0.04
Trigger  0.06
Prepared 0.64
Proc     0.7
View     2.05
Adhoc    95.65

Adhoc queries are excessive and have seemed to increase since use of ORM functionality has increased.

I would like to turn on  Optimize for Adhoc Workload but want to know the pitfalls, also what are the implications of turning this on in a production environment?

Will enabling this option help reduce CPU load?


texts: What are the things need to be considered while creating tablespace?

texts:
I am creating temporary/permanent tablespace on Oracle. As I am going to create an application that is transactional there will be traffic. I bumped into concept called tablespace in oracle. What kind practial things should I consider while creating tablespace?
Should I create temporary table or should I use default temp tablespace?
Are there any resource that I can study except theory?

I have though to create tablespace as:

 CREATE TEMPORARY TABLESPACE OPTIMADATA_TEMP TEMPFILE
    '/opt/app/oracle/oradata/orcl/test01.dbf' SIZE 1024M reuse autoextend on,
    '/opt/app/oracle/oradata/orcl/test02.dbf' SIZE 1024M
    reuse
    autoextend on
    extent management local;

Do I create multiple files or Single file is sufficient?

CREATE TABLESPACE OPTIMADATA DATAFILE '/opt/app/oracle/oradata/orcl/test.dbf' size 2000M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL;

Do I always do Autoextend on ??


texts: User info retrieval takes too long - ASP.NET Membership

texts:
I have an application that has been taken from a sharepoint user control and converted to plain web pages. Everything is working fine - I can sign up and retrieve my password, etc. However, when I try and display the registered users on a webpage the results will take 2+ minutes. Going to page 2 of results takes another 2+ minutes and so on. At last count we had less than 3000 users.

More details - In addition to rebranding the app to aspx pages, it has been connected to a different database server. The previous server (Server 2008, SQL server 2005) serves up membership pretty quickly, but the new server (Server 2016, SQL Server 2017) takes forever.  The new server is an Azure server (the original server was on prem).

We have other apps on the server that utilize the same Azure server. But they are nothing like ASP.NET Membership.

Is there a setting or configuration that I am missing when it comes to getting users from Membership Provider? Please let me know if I need to supply more detail.

The tables were restored to the new server from the other one, if that makes a difference.

Here is the execution plan! https://www.brentozar.com/pastetheplan/?id=HkNBZyp_U

Actual execution plan (script as -> execute to): https://www.brentozar.com/pastetheplan/?id=rykOgPuYL

Actual execution plan with @applicationname (execute -> entered value) https://www.brentozar.com/pastetheplan/?id=BkQyYOOF8


texts: How can it take longer to replay a captured trace than to capture it

texts:
I am attempting to use trace captures to determine how much of a performance issue was database related and how much was code related. However, the trace is taking longer to replay than it took to capture in the first place.

What I was after was the total time it would take to run our SQL statements alone, without the overhead of our actual code. If the replay took half the total time of the capture, I was going to assume that the fault was both code and database, if the replay took almost the same time as the capture, I was going to assume it was database, if the replay took almost no time compared to our capture, I was going to assume it was our code. I was assuming the replay was a very efficient way of executing all the statements.

Instead when I replay my trace it takes at least twice as long to run as the original capture took. This is true whether I allow multiple threads or require it to be sequential. I am new to this, so I could be doing something dumb (possibly the whole idea here being dumb), but it seems to me that replay ought to be faster, not slower.

My capture trace is the replay template + SQL:statement completed (so that it doubles as a tuning capture).


texts: How to join three table using inner join in postgres sql

texts:
I am trying to get the data from the three table using inner join but its sows me empty result.

select comp.gps_time as complaint_register_date,comp.accept_date as complaint_accept_date,comp.name as citizen_name,comp.road_id,dept.dept_name,circle.circle_name,division.div_name
from complaints as comp
   INNER JOIN department as dept ON dept.dept_code=comp.dept_code
   INNER JOIN circle as circle on circle.circle_code=comp.circle_code
   INNER JOIN division as division on division.div_code=comp.div_code
where comp.templateid != null

  complaint table

Complaint_Id    Roadid  Dept_code   Div_code    Circle_code Name    Templateid  Accept_date             Gps_time
162            167889       3         1             4        Xyz       3        2019-02-25 09:40:39        2019-02-23 09:40:39
168            167880       2         4             6        abc     null       2019-02-25 09:40:39       2019-02-23 09:40:39

  Department table

Dept_code   Dept_name
3           Abcdept
2           Zyzdept
1           syzdept

  Circle table

Circle_code Circle_name
4           Xcircle
6           ycircle

  Division Table

Division_code   Division_name
1                Ydiv
2                ndiv
4                zdiv


texts: Incomplete MysqlDump Files - Mysqldump Inside For Loop

texts:
I am running a Windows 10 Machine with WAMP installed. I have also a Git Bash Installed. I was trying to backup all my databases in each separate file using the code below.

for DB in $(mysql -uroot -proot -e 'show databases'); do mysqldump -uroot -proot --complete-insert $DB > "$DB".sql; done;

The Problem is I am only getting incomplete dump files for all databases except the last one. The last dump is complete.

I have tried gzip the sql file by the following command

for DB in $(mysql -uroot -proot -e 'show databases'); do mysqldump -uroot -proot --complete-insert $DB > "$DB".sql; [[ $? -eq 0 ]] && gzip "$DB".sql; done;

Got the same result. Only the last database is getting dumped and gzipped completely.

Note: It seems the next iteration happens before the previous finishes. I have tried using wait and sleep inside but nothing works. I have also tried using mysqldump options --single-transaction, --force, --quick but none seem to work.


texts:
I am calling the stored procedure below, and get this warning, is this warning a false positive or is my query unsafe?

1 queries executed, 1 success, 0 errors, 1 warnings

Query: call User_Signup('lee3@gmail.com', 'password', 'Lee', 'Brooks', null, null, null, null)

1 row(s) affected, 1 warning(s)

Execution Time : 0.042 sec
Transfer Time  : 0.093 sec
Total Time     : 0.136 sec

Note Code : 1592
Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

Here is the call:

CALL User_Signup('lee3@gmail.com', 'password', 'Lee', 'Brooks', NULL, NULL, NULL, NULL);

Here is the stored procedure:

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
  email VARCHAR (250),
  hashedPassword BINARY(60),
  firstName VARCHAR (100),
  lastName VARCHAR (100),
  gender ENUM ('Male', 'Female'),
  dateOfBirth DATE,
  height DECIMAL (3, 2),
  currentWeight DECIMAL (4, 1)
)
BEGIN
  INSERT INTO USER (
    Email,
    HashedPassword,
    RoleId,
    FirstName,
    LastName,
    Gender,
    DateOfBirth,
    Height,
    CurrentWeight,
    CreatedAt
  )
  VALUES
    (
      email,
      hashedPassword,
      (SELECT
        id
      FROM
        Role
      WHERE `Code` = 'CUSTOMER'),
      firstName,
      lastName,
      gender,
      dateOfBirth,
      height,
      currentWeight,
      UTC_TIMESTAMP()
    ) ;
END $$

DELIMITER ;


texts:
I have have looked for an answer for my questions everywhere but they seem confusing to me.Can a foreign key be a primary key in the same table? Can a foreign key be a primary key in another table?

Can a foreign key take values that repeat themselves?


texts: Lock a table for a CTAS upsert in Azure SQL DataWarehouse

texts:
I have a type 2 dimension in Azure SQL Data Warehouse.

Essentially I am creating an interim table with

CREATE TABLE myDimension_temp
AS
SELECT
...
FROM myStagingTable;

etc

After the CTAS is complete I do a

RENAME OBJECT myDimension TO myDimension_old;
RENAME OBJECT myDimension_tmp TO myDimension;
DROP TABLE myDimension_old;

Is there a way to lock these tables so that any actions against myDimension are blocked until the renaming of both tables is complete?

If so, is this worth implementing or is the time it takes to swap out tables in this manner so negligible that the chances of this occurring are too small to worry about or am I looking at/going about this in completely the wrong way?


texts:
This question was born in my mind looking at this kind of code:

select sum(value)
  into positive
  from my_table
 where value > 0;

 select sum(value)
  into negative
  from my_table
 where value < 0;

This works well for take the totals of this table.

But, my question is how I can get the sub-totals of my_table using only a statement.

expected result:

Having this data on my_table

VALUE
-----
  1.0
 -2.0
  3.0
 -4.0
  5.0
 -6.7
  8.9

I'd a DML statement who can give me this output

positive | negative
---------+---------
    17.9 |    -12.7

Is this kind of operation possible?
If yes, how can i get it?


texts: Error on DDL statement never invokes CATCH

texts:
When I try to close the SS Management Studio window below is the error

I understand that DDL statements are implicit COMMIT and the below WARNING message is because BEGIN TRAN never COMMIT or ROLLBACK. Alter DDL statement aborted and never looped through CATCH block.
 Unable to understand why the control is not passed to CATCH block after the error (this does happen with any other DML statement)
Can you please explain why the statements in the CATCH never executed for this DDL error?


texts:
I need to find out how much cache is used, and if there is free cache available.

Background:

Queries on a production server take a lot of time when the server is under heavy load. I suspect that the available cache is too small to serve a high number of different queries.


texts: No connection could be made because the target machine actively refused it. MYSQL work bench?

texts:
I have created php and Mysql site.Its Working Locally Xampp well. Finally I moved My site to server..i have Used Mysql Workbench. connected client database in Mysql Workbench in My system.when I Run the code its showing Error:

Validating Information....error with query:
SELECT ID,fname,lname,email,userlevel,user_group,orgID
FROM students
WHERE username='test'
AND password='123456'
No connection could be made because the target machine actively refused it.


texts: Help me understand and resolve MySQL deadlock - "lock mode AUTO-INC waiting"

texts:
I am experiencing an issue with MySQL deadlocks and here is what I see in the MySQL error log:

2017-07-24 09:09:29 7fd5235db700InnoDB: transactions deadlock detected, dumping detailed information.
2017-07-24 09:09:29 7fd5235db700
*** (1) TRANSACTION:
TRANSACTION 177707078, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 20887150, OS thread handle 0x7fd522d7a700, query id 918997514 10.80.11.16 staging update
INSERT INTO `n0_table` (`post_id`, `meta_key`, `meta_value`) VALUES (411902, 'from_header', 'barberklingen <****************>')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 480 page no 44003 n bits 768 index `post_id` of table `staging`.`n0_table` trx id 177707078 lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 177706917, ACTIVE 1 sec setting auto-inc lock
mysql tables in use 1, locked 1
121 lock struct(s), heap size 13864, 15196 row lock(s), undo log entries 93
MySQL thread id 20887160, OS thread handle 0x7fd5235db700, query id 918997545 10.80.11.15 staging update
INSERT INTO `n0_table` (`post_id`, `meta_key`, `meta_value`) VALUES (411901, '_wc_customer_order_csv_export_customer_is_exported', '0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 480 page no 44003 n bits 760 index `post_id` of table `staging`.`n0_table` trx id 177706917 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `staging`.`n0_table` trx id 177706917 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)

Currently the MySQL InnoDB lock mode is:

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 0     |
+--------------------------+-------+

The hosting setup for this website which generates those deadlocks is the following:

Requests go to two load balancers and from there are forwarded to 4 application servers.
The application servers then use one and the same MySQL master database.
There is a slave which is configured in case the master fails.

I am trying to figure out why those deadlocks are occurring and also how to prevent them. In my understanding the auto increment should be working without deadlocks in this case but I am probably missing something. Thank you and let me know if case other information is required.

UPDATE:

Here is the show create table:

mysql> show create table n0_table\G;
*************************** 1. row ***************************
       Table: n0_table
Create Table: CREATE TABLE `n0_table` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=8014133 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


texts: One Availability group, two primary nodes

texts:
I have two nodes A & B, I'm trying to set Node A to be primary for DB1 with Node B as its secondary and then set up Node B as primary for DB2 and Node A as its secondary. (I'm using SQL 14.0.1000.169)

Do I need two Availability groups, two listeners, two failover clusters?
Currently I have one of each.

I was able to successfully create the Availability group for the first scenario but now I'm lost.


texts: Is this application creating stored procedures on the fly?

texts:
We are running a transaction program that uses MS SQL Server. Lately as our number of users and locations have grown, speed/response seems to be an issue.

When looking at *sys.dm_exec_sql_text*, I see a lot of queries starting with "CREATE PROCEDURE", of which some are over 1000 lines of code. Is the application creating stored procedures on the fly or is it merely executing these stored procedures? I thought executing a stored procedure is started using "EXEC"?

If the application is constantly (re)creating the procedures, is it likely that this causes performance issues?


texts:
I am trying to create a schema for documents and collections for a MongoDB database I am creating. I have read a few documents describing the _id field that is generated by each document upon creation.

I have also read this article about implementing One-to-N relationships in Mongo and have decided that my case lies in the "One-to-Many" category. As a result I am going to reference multiple other documents in multiple other collections by their _id field from my "current" collection. From what I can tell in the article this seems that it will work just fine. Though I faintly remember reading an article saying that _id fields are not guaranteed to be unique over different collections, but they are within collections (forgive me, I cannot find a link to it).

So for example say I have three collections for a company that sells Apples and Oranges; Orders, Apples, and Oranges. Orders holds documents about orders for Apples and Oranges. Apples and Oranges holds data about various types of those fruits.

In a Orders documents I might have the fields

{
    ...
    "OrangesOrdered" :
    [
        { "Amount" : 2, "OrangeID" : "OrangeObjectID12"}
    ]
    ...
    "ApplesOrdered" :
    [
        { "Amount" : 4, "AppleID" : "AppleObjectID65"}
    ]
    ...
}

Where OrangeObjectID12 is the _id field of a specific type of Orange and AppleObjectID65 is the _id field of a specific type of Apple.

Is it possible that these two _id could ever be the same?


texts: Moving Postgres 10 data dir on Ubuntu 18

texts:
I am trying to move the initial Postgres data dir from
/var/lib/postgresql/10/main

to another harddrive. The harddrive is accessable within the terminal via
/media/chris/Datenspeicher

I used this guide to switch my data dir. Postgres is starting up after modifying the postgres.conf file but im not able anymore to access the database through pgadmin or the terminal.
When trying to access the database its asking if the database is running on port 5432 and accepting connections.
Why? What am I doing wrong?
Update:
After clean install of ubuntu and postgres all errors still remain.
The postgres log output is:

2018-07-06 14:16:04.958 CEST [5664] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-07-06 14:16:04.998 CEST [5664] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-07-06 14:16:05.124 CEST [5665] LOG:  database system was shut down at 2018-07-06 14:00:21 CEST
2018-07-06 14:16:05.167 CEST [5664] LOG:  database system is ready to accept connections
2018-07-06 14:16:05.714 CEST [5672] [unknown]@[unknown] LOG:  incomplete startup packet


texts: Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQL

texts:
Suppose I have two tables A and B. Table A has a primary key, A.ID for instance, and each row in B contains a reference to some A.ID (there is a foreign key column in B). As a specific example:

A:

ID | Name | Age
1  | Bob  | 25
2  | Dan  | 30
3  | Tina | 20
4  | Val  | 36
5  | Joe  | 51
6  | Mel  | 22

B:

AID  |  Color
1     |  Blue
1     |  Green
1     |  Black
3     |  Green
4     |  Blue
2     |  Red
5     |  Green
3     |  Red

Now Suppose I want to get the rows of A amended and ordered by the count of references each row has in B. In this example I would want

ID | Name | Age | Count
1  | Bob  | 25  | 3
3  | Tina | 20  | 2
2  | Dan  | 30  | 1
4  | Val  | 36  | 1
5  | Joe  | 51  | 1
6  | Mel  | 22  | 0

One way I've basically managed to do this is the following query:

Select Count(*), A.ID, A.Name, A.Age, B.Color From
  B Left Join A
  On B.AID=A.ID
  Group By A.ID
  Order By Count(*) Desc, B.Color Desc

That will return:

Count | A.ID | A.Name | A.Age | B.Color
3     | 1    | Bob    | 25    | Blue(not actually sure what color would go here)
2     | 3    | Tina   | 20    | Green
1     | 2    | Dan    | 30    | Red
1     | 4    | Val    | 36    | Blue
1     | 5    | Joe    | 51    | Green
1     | 6    | Mel    | 22    | NULL

This works because I can guarantee that the null values will show up after the rows that do have a non-null color due to the order by. However, the counts here are not exactly what I'm looking for, I'd prefer to have Mel's row display a 0 for the count.

I'm sure there is a better way to perform this type of query, does anyone have any suggestions on how I could make this cleaner?

Thanks!


texts:
MAIN TABLE

CREATE TABLE `sec_role_access_m` (
  `ROLEACCESS_ID` int(10) NOT NULL AUTO_INCREMENT,
  `ROLE_ID` varchar(10) DEFAULT NULL,
  `MODULE_ID` varchar(10) DEFAULT NULL,
  `FUNCTION_ID` int(10) DEFAULT NULL,
  `REC_STATUS` varchar(1) DEFAULT NULL COMMENT 'A- ACTIVE, X-DELETED',
  `MAKER_ID` varchar(10) DEFAULT NULL,
  `MAKER_DATE` datetime DEFAULT NULL,
  `AUTHOR_ID` varchar(10) DEFAULT NULL,
  `AUTHOR_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`ROLEACCESS_ID`),
  KEY `INDX_ROLE_ID` (`ROLE_ID`),
  KEY `INDX_SRAM_MODULE_ID` (`MODULE_ID`),
  KEY `INDX_SRAM_FUNCTION_ID` (`FUNCTION_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=13972 DEFAULT CHARSET=utf8;

HISTORY TABLE

CREATE TABLE sec_role_access_hst (
  `ROLEACCESS_ID` int(10) DEFAULT NULL,
  `ROLE_ID` varchar(10) DEFAULT NULL,
  `MODULE_ID` varchar(10) DEFAULT NULL,
  `FUNCTION_ID` int(10) DEFAULT NULL,
  `REC_STATUS` varchar(1) DEFAULT NULL,
  `MAKER_ID` varchar(10) DEFAULT NULL,
  `MAKER_DATE` datetime DEFAULT NULL,
  `AUTHOR_ID` varchar(10) DEFAULT NULL,
  `AUTHOR_DATE` datetime DEFAULT NULL,
   `Status` varchar(10) DEFAULT NULL,
  `log_Timestamp` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have created history table with reference to main table to store main table data before update as a audit log.

TRIGGER

DELIMITER $$

CREATE TRIGGER UPDATE_role_access_M BEFORE UPDATE ON sec_role_access_m
    FOR EACH ROW BEGIN
    INSERT INTO
         sec_role_access_hst
(ROLEACCESS_ID,ROLE_ID,MODULE_ID,FUNCTION_ID
 ,REC_STATUS,MAKER_ID,MAKER_DATE,AUTHOR_ID
 ,AUTHOR_DATE,Status,log_Timestamp)
     VALUES
(OLD.ROLEACCESS_ID,OLD.ROLE_ID,OLD.MODULE_ID,OLD.FUNCTION_ID
 ,OLD.REC_STATUS,OLD.MAKER_ID,OLD.MAKER_DATE,OLD.AUTHOR_ID
 ,OLD.AUTHOR_DATE,'UPDATE',NOW());

END;
$$

DELIMITER ;

BUT this trigger isn't working while update on main table the  data isn't getting saved on history table.

I need before update history log on my history table.

MAIN  TABLE DATA AS BELOW FOR REFERENCE

4793    59  CPS 3000106     ADMIN   2016-08-31 15:10:39 ADMIN   2016-08-31 15:10:39
4794    59  CPS 3000111     ADMIN   2016-08-31 15:10:39 ADMIN   2016-08-31 15:10:39
4795    59  CPS 3000116     ADMIN   2016-08-31 15:10:39 ADMIN   2016-08-31 15:10:39
4796    59  CPS 3000952     ADMIN   2016-08-31 15:10:39 ADMIN   2016-08-31 15:10:39
5013    65  CPS 3000296     ADMIN   2016-11-11 15:56:42 ADMIN   2016-11-11 15:56:42
5014    65  CPS 3000901     ADMIN   2016-11-11 15:56:42 ADMIN   2016-11-11 15:56:42
5015    65  CPS 3000960     ADMIN   2016-11-11 15:56:42 ADMIN   2016-11-11 15:56:42
5016    65  CPS 3000952     ADMIN   2016-11-11 15:56:42 ADMIN   2016-11-11 15:56:42
5017    65  CPS 3000951     ADMIN   2016-11-11 15:56:42 ADMIN   2016-11-11 15:56:42
5215    73  SEC 1000141     ADMIN   2016-11-11 11:30:19 ADMIN   2016-11-11 11:30:19
5325    72  CPS 3000106     ADMIN   2016-11-11 13:23:52 ADMIN   2016-11-11 13:23:52
5326    72  CPS 3000111     ADMIN   2016-11-11 13:23:52 ADMIN   2016-11-11 13:23:52
5327    72  CPS 3000116     ADMIN   2016-11-11 13:23:52 ADMIN   2016-11-11 13:23:52
5328    72  CPS 3000960     ADMIN   2016-11-11 13:23:52 ADMIN   2016-11-11 13:23:52
5329    72  CPS 3000952     ADMIN   2016-11-11 13:23:52 ADMIN   2016-11-11 13:23:52
5330    67  CPS 3000296     ADMIN   2016-11-11 13:26:34 ADMIN   2016-11-11 13:26:34
5331    67  CPS 3000361     ADMIN   2016-11-11 13:26:34 ADMIN   2016-11-11 13:26:34
5332    67  CPS 3000901     ADMIN   2016-11-11 13:26:34 ADMIN   2016-11-11 13:26:34
5333    67  CPS 3000902     ADMIN   2016-11-11 13:26:34 ADMIN   2016-11-11 13:26:34
5334    67  CPS 3000960     ADMIN   2016-11-11 13:26:34 ADMIN   2016-11-11 13:26:34
5335    67  CPS 3000952     ADMIN   2016-11-11 13:26:34 ADMIN   2016-11-11 13:26:34
5336    67  CPS 3000951     ADMIN   2016-11-11 13:26:34 ADMIN   2016-11-11 13:26:34
6955    75  CPS 3000100     UHF0036 2018-10-22 16:29:50 UHF0036 2018-10-22 16:29:50
6956    75  CPS 3000106     UHF0036 2018-10-22 16:29:50 UHF0036 2018-10-22 16:29:50
6957    75  CPS 3000111     UHF0036 2018-10-22 16:29:50 UHF0036 2018-10-22 16:29:50


texts: Find Underlying Data Type (int - integer) of Oracle Table instead of number

texts:
I can create an example table using following syntax.

CREATE TABLE "TABLE2" (
  "COLUMN_INT" INT NOT NULL,
  "COLUMN_INTEGER" INTEGER NOT NULL,
  "COLUMN_SMALLINT" SMALLINT NOT NULL,
  "COLUMN_NUMBER" NUMBER NOT NULL,
  "COLUMN_DECIMAL" DECIMAL NOT NULL);

After that I query USER_TAB_COLUMNS table using following query.

SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TABLE2';

I get following result.

COLUMN_NAME                    DATA_TYPE
------------------------------ -------------
COLUMN_INT                     NUMBER
COLUMN_INTEGER                 NUMBER
COLUMN_SMALLINT                NUMBER
COLUMN_NUMBER                  NUMBER
COLUMN_DECIMAL                 NUMBER

If I use VS.NET add-in for oracle to get DDL for this table. I get following.

-- ****** Object: Table DENEME.TABLE2 Script Date: 05.05.2013 21:43:11 ******
CREATE TABLE "TABLE2" (
  "COLUMN_INT" NUMBER NOT NULL,
  "COLUMN_INTEGER" NUMBER NOT NULL,
  "COLUMN_SMALLINT" NUMBER NOT NULL,
  "COLUMN_NUMBER" NUMBER NOT NULL,
  "COLUMN_DECIMAL" NUMBER NOT NULL,
    CHECK ("COLUMN_INT" IS NOT NULL),
    CHECK ("COLUMN_INTEGER" IS NOT NULL),
    CHECK ("COLUMN_SMALLINT" IS NOT NULL),
    CHECK ("COLUMN_NUMBER" IS NOT NULL),
    CHECK ("COLUMN_DECIMAL" IS NOT NULL))
/

If I use SQL Developer

--------------------------------------------------------
--  DDL for Table TABLE2
--------------------------------------------------------

  CREATE TABLE "DENEME"."TABLE2"
   (    "COLUMN_INT" NUMBER(*,0),
    "COLUMN_INTEGER" NUMBER(*,0),
    "COLUMN_SMALLINT" NUMBER(*,0),
    "COLUMN_NUMBER" NUMBER,
    "COLUMN_DECIMAL" NUMBER(*,0)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table TABLE2
--------------------------------------------------------

  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_DECIMAL" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_NUMBER" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_SMALLINT" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_INTEGER" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_INT" NOT NULL ENABLE);

If I use TOAD.

Toad give me following DDL.

CREATE TABLE TABLE2
(
  COLUMN_INT       INTEGER                      NOT NULL,
  COLUMN_INTEGER   INTEGER                      NOT NULL,
  COLUMN_SMALLINT  INTEGER                      NOT NULL,
  COLUMN_NUMBER    NUMBER                       NOT NULL,
  COLUMN_DECIMAL   INTEGER                      NOT NULL
)
;

It seems that I can not get INT, INTEGER values, I used in my create table syntax?
Int, Integer, small int seems to be a syntactic sugar only. But Toad at least holds information about INTEGER part even though he considers smallint,int,decimal and integer same.

Is there any way to find that if a column is INT/INTEGER/SMALLINT using SQL?


texts:
The dates in the SQL Server error log look very strange.
A subset for messages between Sep/29/2014 8:00 am and Sep/29/5:40pm show a date of Feb/16/2014. Have you seen this before? Any idea why the date would look different?


texts: How to decrese MySQL Innodb crash recovery time (force MySQL to do it faster)

texts:
I'm running MySQL 5.7 with 1GB of innodb_log_file_size on CentOS 7 x64 and I can't change the value of that parameter right now.
In the case of crashes, it takes about 20 minutes to recover, but within this 20 minutes mysql process doesn't seem to use system resources efficiently (i.e. it has a very mild CPU usage).
I need to know is there any way to force MySQL to do this process a little bit faster using configuration parameters? (for example by increasing number of recovery threads or allocating more RAM space)


texts: phpmyadmin - Login issue, takes me back to the login page after logging in

texts:
I'd setup phpmyadmin quite a while ago and it worked well and I got everything setup and working etc... However, I went to login recently and it always just takes me back to the login page. The credentials are correct, but never the less I always end up at the login screen.

I can't find anything in my error logs, and in the access logs everything is status code 200, except the POST which is 302 followed by 304s. None of that really stands out.

I'm using Nginx+PHP5-FPM+MySQL if that changes anything.

What types of things might I have changed that would cause this sort of activity?


texts: Will inserting a row keep it in the buffer cache

texts:
I understand that selecting a set of rows from the database will make sure they remain in the buffer cache (assuming they fit).

Will inserting a set of rows make sure they are available to select from the buffer cache as well?

Context:
We are considering forcing the current-day partition of a table in the buffer cache, but if inserting rows will enter them into the buffer cache anyway, then there would be no need to make the extra effort. (Rows are entered with the current timestamp, never in the past or the future, partitioning is by day).


texts: Count Total Transaction each specific row even if 0

texts:
I have a 3 Columns namely:

Payment Channel
Payment Name
Total Transaction

The Total Transaction will be the COUNT of each Payment Channel and Payment Name.

In the Payment Channel, values have PC01-PC09.

I want my result to display all the Payment Channel even if no transactions.

Here is my SQL Script:

SELECT
    B2C_BUY_LOG.PG_CHANNEL AS Payment_Channel,
    COMM_CODE.CODE_NAME AS Payment_Name,
    COUNT(B2C_BUY_LOG.PAY_ID) AS Total_Transaction
FROM B2C_BUY_LOG
INNER JOIN B2C_BUY_HIST ON B2C_BUY_LOG.PAY_ID = B2C_BUY_HIST.PAY_ID
INNER JOIN COMM_CODE ON B2C_BUY_LOG.PG_CHANNEL = COMM_CODE.CODE
WHERE B2C_BUY_LOG.RET_CODE = 1
AND B2C_BUY_LOG.PAY_ID LIKE '190220%'
AND COMM_CODE.CODE IN('PC01', 'PC02', 'PC03', 'PC04', 'PC05', 'PC06', 'PC07', 'PC08', 'PC09')
GROUP BY Payment_Channel, Payment_Name;

Here is the result of my Query:

Payment_Channel   Payment_Name    Total_Transaction
PC01              Name-1          14
PC02              Name-2          2
PC03              Name-3          7
PC04              Name-4          9
PC06              Name-6          21
PC08              Name-8          18
PC09              Name-9          95

This query returns only the match values between the joined tables because it is INNER JOIN and the PC05 and PC07 is missing on the result because it has no transaction. I also tried different JOINS.

How can I display the PC05 and PC07 with a count of 0 if no transaction?

Thanks!


texts: MySQL non-sequential primary key of certain length

texts:
what's the best way to create a non-sequential primary key of a certain length in MySQL?

I'm looking to use the primary key as a 'Pin' number which real actual human people can use to refer to the object in the database to join a game. I'd like them to be non-sequential so it's not quite so easy to find other games which they aren't necessarily supposed to access.

I've added the criteria that they are a certain length, but I guess I really want them to be at least a certain length.

Could anyone point me in the right direction? I've looked at http://seventhfloor.whirlycott.com/2008/05/27/generating-primary-keys/ but it seems a bit over the top, and I wondered if there were a more straight forward way.


texts:
I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year.

The table structure looks like:

id | year | amount

The query I'm currently executing is:

SELECT year, count(year) FROM mytable GROUP BY year

This works just fine and gives me the results I expect.

The issue is that this these queries are being done as part of a web application and they currently take about 6-7 seconds to execute. I have an index on the year field (and on amount). I don't think I can pre-compute summary statistics because the query can have arbitrary where clauses added to it, e.g.

SELECT year, count(year) FROM mytable WHERE amount > 500 GROUP BY year

Though I could pre-compute the query in the case when there are no additional WHERE clauses.

Is there anything I can do to speed up the computation of the count part of the query? That seems to be where the slowdown is.


texts: Does a dual key or single key lookup perform better on a table with a lot of data?

texts:
We have table where rows unique between 3 columns. GroupA, GroupB, ProductID.

GroupA & GroupB are used in combinations to Group sets of ProductIDs and will always be used to lookup sets of productIDs. Sometimes a ProductID will be used in in the query also.

I wasnt sure if there would be any performance gains from combining GroupA and GroupB into the same column so that the index is clustered.

   GroupA | GroupB | ProductID
   ----------------------------
   A1     | B1     | 1
   A1     | B1     | 99
   A1     | B2     | 1
   A1     | B2     | 99
   A2     | B1     | 1
   A2     | B1     | 99
   A2     | B2     | 1
   A2     | B2     | 99

Dual group method above OR Single group method below.

   GroupAB | ProductID
   --------------------
   A1-B1   | 1
   A1-B1   | 99
   A1-B2   | 1
   A1-B2   | 99
   A2-B1   | 1
   A2-B1   | 99
   A2-B2   | 1
   A2-B2   | 99

   GroupA | GroupB | GroupAB
   ----------------------------
   A1     | B1     | A1-B1
   A1     | B2     | A1-B2
   A2     | B1     | A2-B1
   A2     | B2     | A2-B2


texts: How do I get the PostgreSQL query execution time in PHP using the pg_* functions?

texts:
I've spent hours on this page now, looking through everything: https://www.php.net/manual/en/ref.pgsql.php
I can't figure out how to get the "query execution time" from a pg_query (and related query execution functions).
I'm not talking about measuring the time before and after the query in the PHP code, but to get the actual "query execution time" (in milliseconds or similar) of the query, which could be slightly different from the time before and after the function was called due to various reasons.
I'm 99% sure that both PostgreSQL and all other SQL database softwares send this information back for each query somehow, but how? It has a bunch of "statuses", but not the time the query took to run from what I can tell.


texts: Avoid duplication when using other table in WHERE and JOIN clause in Postgres


texts:
Recently when i was trying to find information about latch_ex wait type, i come across one blog as below which stated about latch and lock.

After reading this blog , i m just curious in one thing. When app submit a request, SQL server will look for the information in buffer cache first and if the page not in buffer cache ,then only it will read from disk and put it in buffer cache before send info to app. My question based on the screenshot as above where it stated latch and lock need to avoid two thread updating same page. Basically all request come to SQL server will go to buffer cache first, if the page in buffer cache is busy updating, another thread will have to wait. It wont go back to disk because the page already in memory. Then what is the purpose of lock since every request will be done through memory and there is latch to protect the page


texts: PostgreSQL socket not listening on the right one

texts:
I'm trying to get my PostgreSQL to work.

It is clearly running, according to: ps aux | grep postgres | grep -v grep

I am getting -

username            5781   0.0  0.0  2441364    524   ??  Ss    9:05AM   0:00.33 postgres: stats collector process
username            5780   0.0  0.0  2445216   1628   ??  Ss    9:05AM   0:00.19 postgres: autovacuum launcher process
username            5779   0.0  0.0  2445084    532   ??  Ss    9:05AM   0:00.14 postgres: wal writer process
username            5778   0.0  0.0  2445084    576   ??  Ss    9:05AM   0:00.14 postgres: writer process
username            5777   0.0  0.0  2445084    592   ??  Ss    9:05AM   0:00.00 postgres: checkpointer process
username            5775   0.0  0.1  2445084   3544   ??  S     9:05AM   0:00.12 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log
username            6044   0.0  1.6  2884196  66420   ??  S     9:44AM   0:21.62 /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/bin/java -Dfile.encoding=UTF-8 -classpath /Applications/RubyMine.app/plugins/DatabaseSupport/lib/jdbc-console.jar:/Applications/RubyMine.app/lib/util.jar:/Users/username/Library/Preferences/RubyMine50/jdbc-drivers/postgresql-driver-license.txt:/Users/username/Library/Preferences/RubyMine50/jdbc-drivers/postgresql-9.1-901.jdbc3.jar com.intellij.persistence.database.console.RemoteJdbcServer org.postgresql.Driver

But when I try to createuser -s -U $USER, I am getting:

createuser: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

However, it is clearly running, but just not talking to the correct socket. I tried: sudo find / -name .s.PGSQL.5432 -ls and am getting:

find: /dev/fd/3: Not a directory
find: /dev/fd/4: Not a directory
48453370        0 srwxrwxrwx    1 username   wheel    0 Apr 24 11:59 /private/tmp/.s.PGSQL.5432

How do I get PostgreSQL to talk with the correct socket?

Update:

I checked ls -la under /var/pgsql_socket/.s.PGSQL.5432:

total 0
drwxrwxrwx   2 _postgres  _postgres   68 Sep  19  2011 .
drwxr-xr-x  28 root       wheel      952 Nov   8  2011 ..


texts: SQL Server Restore from one database to another

texts:
One of our devs backed up a dev database, and then restored it in production. It's a new database for a new app that was deployed last night intentionally to prod.

Now in the backupset table (msdb.dbo.backupset) on prod, I can see a record for the dev database, with a backup start date time of when the restore was done.

Record from prod backupset table:

name:         DatabaseName_UAT-Full Database Backup
server_name:  COMPNAME-SQLDEV02
machine_name: COMPNAME-SQLDEV02

I would not expect to see this record. Can anyone explain why restore would insert into the backupset table on prod?

Can I delete this record from the msdb.dbo.backupset table? Or not such a good idea?


texts: Improving SQL Server performance on VMware under Linux

texts:
I have a Linux server in a small company (3 users) acting as a domain server and running two VMs under VMware: one has  SQL Server 2000 on it and one has SQL Server 2005, in each case running on Win2k. Each of the SQL Servers has a couple of small-but-important databases.

As near as I can tell, the Linux server spends its life utterly bored. It has 2GB of memory, but even with the VMs it never uses more than about 512MB, with the rest being allocated to cache.

But the VM performance stinks! The databases are slower than before I moved them from dedicated (but ancient) machines. If a database hasn't been used for a while, you can go have a coffee before it starts responding again.

What can I do to improve the performance?


texts: Restoring Postgresql database from WAL without backup

texts: I am trying to restore data in my database. How can I restore data from WAL without any database backups? Can I generate a backup file from WAL logs?


texts: Mariadb 10.1.25: create type ERROR 1064 (42000)

texts:
cut and paste from the manual:
CREATE TYPE UDT_1 AS CHAR(15) FINAL;

results in:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TYPE UDT_1 AS CHAR(15) FINAL' at line 1

clean install. all global and schema privileges granted.  create view & create table work.

thanks for any insight.


texts: MSSQL: Conditional noexpand hint for view that may be indexed or not

texts:
As documented in other Q&A on this site, if you create an indexed view you may have to give the noexpand hint to use it as such.  Without the hint, cheaper versions of MSSQL will always treat it as an ordinary view, and even the 'enterprise' versions may still need the hint in practice.

But noexpand is not valid on an ordinary, not-indexed view.  That means if you change a view from indexed to ordinary, queries stop working.  You can't fully hide the implementation detail of whether it's an indexed view or not, unless you make another wrapper view.

Is there some hint that means the same thing as noexpand when applied to an indexed view, but is a no-op when applied to an ordinary view?

select name from myview (noexpand_if_necessary) where id = 55

Then if myview is an indexed view you get the benefit of the indexes; if myview happens to be an ordinary view the query happens as if the hint were not given.

I'm currently using MSSQL 2008 R2 but I would be interested in answers for newer MSSQL versions too.


texts: I've seen multiple answers about kicking all of the other users but is there a way to stop the process id and kick just one user out of the database. Eg. user A B and C are connected to the database trying to generate reports but I only want C to stop and disconnect while A and B are still able to continue with their processes.


texts:
I have a sql server db and have a question regarding foreign key indices.

Here's a snippet from the db:

In programme_affiliates, I have a computed field, this is always set to '2' in this instance.

The reason for it is that it maintains integrity between programmes and programmes_affiliates.

I also have a link to programme_types to ensure that '2' is always valid as a programme type.

I believe that you should always provide an index for a foreign key, but as it is a calculated fixed field, is this necessary in this case?


texts: Does anyone have example for setting up a job to reduce SQL Server max memory and recycle SQL server service?

texts:
I have both SQL Server 2008 and SSAS running on the same machine (Don't ask me why, I have to live with it for now!). Since we run ETL and Cube generation in sequence, I wanted to allocate large portion of memory to SQL server right before ETL jobs start, and do the same for SSAS right before Cube generation starts.

Is there a way to automate this dynamic memory increase/decrease operations via a job?

Could someone with experience in this please share your thoughts/scripts?


texts: Check if postgresql database exists (case insensitive way)

texts:
Is there a "elegant built-in" case-insensitive way to check if db is exists?

I've found only SELECT datname FROM pg_catalog.pg_database WHERE datname='dbname', but this is a CS check.
The first thing that comes to mind to retrieve all db names and filter them by hand, but I think there is more elegant way to do it.


texts: Query optimization when no row is returned

texts:
I am using explain to figure out what is happening in my query which is:

explain select t from c where u1_id = 1 group by t;

I see "Using where" in the Extra column of the resultset. But this only happens if I use a valid u1_id. So if I run the same query, on the same server but with an invalid u1_id I get "Using where; Using temporary; Using filesort" in the Extra column.

I assume this means that when the user_id is not in the table then MySQL takes another route to process the query. Does anyone know why this happens and how can I prevent this from happening?

EDIT

Here is my table:

CREATE TABLE  c  (
   pk  int(11) NOT NULL AUTO_INCREMENT,
   u1_id  int(10) unsigned NOT NULL,
   u2_id  int(10) unsigned NOT NULL,
   s  decimal(11,5) NOT NULL DEFAULT ''0.00000'',
   date  datetime NOT NULL,
   f  decimal(11,5) NOT NULL,
   t  enum(''F'',''P'') ,
   ti  decimal(11,5) DEFAULT ''0.00000'',
   ts  decimal(11,5) DEFAULT ''0.00000'',
   latest  tinyint(1) DEFAULT ''0'',
   sc  decimal(11,5) DEFAULT ''0.00000'',
   p_id  int(10) unsigned NOT NULL,
  PRIMARY KEY ( pk ),
  KEY  i1  ( u1_id ),
  KEY  i2  ( u2_id ),
  KEY  i3  ( u1_id , u2_id ),
  KEY  i4  ( sc ),
  KEY  i5  ( sc , u2_id , latest ),
  KEY  i5  ( p_id ),
  KEY  i8  ( ti ),
  KEY  i9  ( t ),
  KEY  i10  ( p_id , u1_id , latest , t ),
 )


texts: Python transfer all data of table to table of different database

texts: I can connect to a database Postgres with psycopg2, but this just 1 database. I have a problem that use Python code to move data of this table (database No.1) to another table (database No.2). The second database is in the same server. Any suggestions for me?


texts: How to insert into a table daily automatically in postgresql?

texts:
I'm trying to create a log of an inventory that runs daily

Basically on my log table i want to sum how many units have status1, how many units have status2, etc for every day

is there a way to fill up my log table without having to do it manually every day?

My log table is id serial, date date, count_of_status1 integer, count_of_status2 integer

My insert query would be

insert into logtable(date, count_of_status1, count_of_status2)
select now(),  count(*) filter (where status =1), count(*) filter (where status = 2)
from mytable

How to make this insert to be done daily in an automated form rather than manually?


texts: innodb_flush_log_at_trx_commit=1 and flush-to-disk reality

texts:
I use the default settings of MySQL 5.5 on various servers and I have the following table:

create table test._perf (
    name varchar(255) not null
) engine=InnoDB;

I have an .sql script which contains 1000 lines of:

insert into test._perf values('abc');

and I run it like that:

time mysql -u root < 1000-inserts.sql

Since it runs on auto commit, there are 1000 transactions happening, one after the other.

The default setting of innodb_flush_log_at_trx_commit is 1 which does full ACID. (documentation: https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit)

The documentation also states that:

  Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1...

All of the servers I'm testing the 1000-inserts.sql script are modern ("fast") but very different between them (metal vs vm, windows vs linux, ext4 vs other filesystems, busy vs idle), but the results are always consistent and fall in one of the following two categories:

1000 inserts finish in 0.2-1 seconds (fast)
1000 inserts finish in 20-55 seconds (slow)

Setting innodb_flush_log_at_trx_commit to 2 in the "slow" servers makes them "fast".

I'd like to know the answer to the following questions:

can I assume that the servers with the "slow" behavior are the right and correct ones that really implement innodb_flush_log_at_trx_commit=1?
can I assume that the servers with the "fast" behavior are wrong and it is the behavior explained in the documentation? (Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not.)


texts: Create table in Oracle as select from SQL Server - varchar size

texts:
I am trying to create table in Oracle 11g as select from a table in SQL Server 2012 via database link.

The table in SQL Server contains column: hslakkis varchar(3)

In Oracle it created the table with the same column but different size:
hslakkis varchar2(9)

This behavior causes me a lot of problems with the columns size in Oracle.
How can I prevent Oracle from increasing the column size?


texts: Can Postgres use an index-only scan for this query with joined tables?

texts:
This is a follow-up to: Do covering indexes in PostgreSQL help JOIN columns?

Consider the inverse of the schema in the other question where you filter in the joined-on table:

CREATE TABLE thing_types(
   id              INTEGER PRIMARY KEY
 , first_lvl_type  TEXT
 , second_lvl_type TEXT
);

CREATE TABLE things(
   id         INTEGER PRIMARY KEY
 , thing_type INTEGER REFERENCES thing_types(id)
 , t1c1       INTEGER
);

And a query like so:

SELECT things.t1c1
FROM   things
JOIN   thing_types ON things.thing_type = thing_types.id
WHERE  thing_types.first_lvl_type = 'Book'
AND    thing_types.second_lvl_type = 'Biography';

Is it madness to have an index like:

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

which covers the primary key for use in that join? Will the index be used as a covering index to help the JOIN in the above query? Should I change my indexing strategy to cover the primary key more often when I know the table is going to be JOINed on like this?