texts: Large rolling update on huge table: Getting slower and slower

texts:
SQL Server 2014 Standard Edition

We have a table with 100million+ rows.

We need to update values in a couple of columns.

We did the below, and it has been running for 18 hours now, and has only done 17 million rows. It is getting slower and slower.

It is a reasonably hot server and disk system (EMC RAID10 and all that). The CREATE INDEX took about 20 minutes (an unfortunate outage we had to tolerate).

What approach can we use to get through this faster? (on line strongly preferred)

CREATE NONCLUSTERED INDEX RECORD_DELETED_INDEX ON [dbo].[huge-table] ( [deleted] ASC, [deletedDate] ASC );
GO

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 4000  -- to stay under lock escalation threshhold

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END


texts: Rename Field And Add Field With Same Name In One

texts:
I have a stored procedure that creates a table, inserts data into the table, and now I need to alter the table to

  1) Rename an existing field to a new name
  2) Add a new field with the same name as the field we just renamed

I tried this syntax however my SSMS freezes anytime I execute and I have to ctrl+alt+del to close....I feel that is unrelated to the syntax, but wanted to ask

    Create Table TMI (empName varchar(500), userID varchar(100), TV float)

CREATE NONCLUSTERED INDEX eNIndex
ON TMI (empName ASC)

dbo.sp_RENAME '[dbo].TMI.TV' , 'OrigTV', 'COLUMN'

ALTER TABLE [dbo].TMI
ADD TV float;


texts: Differences between .out and .sql in pg_dump PostgreSQL

texts:
I create a backup file of postgeSQL database:
>pg_dump --dbname=db_test --host=localhost --username=user -F c -f dump_test.out

In documentation for pg_dump is used extension .sql and for pg_dumpall is used .out

https://www.postgresql.org/docs/9.2/app-pgdump.html
https://www.postgresql.org/docs/9.2/app-pg-dumpall.html

What is the differences between the extensions of .out and .sql in pg_dump in PostgreSQL? what is the recommendation and why?


texts: Can mongoDB split read and write, with primary on HDD, replica sets on SSD?

texts: I have three machines, each machine both has SSH and HDD, I want to save SSD space, and in my env: less write, lots read. So does mongoDB support splitting read and write, with primary on HDD, replica sets on SSD. If so, how do I go about doing this?


texts: How do I store World/event data in a treelike structure SQL/SQLite3

texts:
I would like to create a database which contains tables of the following form, where each record in a table, is a table in and of itself. Ideally at any one time I would be able to query any table of the following format. e.g I would be able to lookup/export Paris table and see names of persons and other data contained in that table.
I've currently not found a solution for this use case and any help would be much appreciated
Continent | Occurrences in continent|
Europe. ....
...
Country | Occurrences in country
France ...
...
City | Occurrences in city
Paris. ...
...
Name of persons in City | number of occurrences | City | Country | Continent | Unrelated data
...
I'm not too familiar with building databases and I think one issue I have is that currently, the only unique identifier would be names of persons for each city. Naturally, the number of occurrences in each continent would be the sum of the occurrences for each country in that respective continent and so on. I would like the db to work such that an increase in number of occurrences in the lowest table would be reflected in all tables.
So far I've created the relevant tables at each node but I don't know how to relate them to one another


texts: How does one know when to stop normalizing?

texts:
Alright, so basically I feel that I tend to over-normalize things, and maybe I am doing it at the cost of performance. So, to illuminate on the problem, I have created the following schema to use as an example:

As you can see, I have outlined two different approaches. The idea here is that all universities have programs (e.g., Engineering), and all programs have majors (e.g., Electrical Engineering). For this example to work, we must assume that there are 40 programs, and say 1,000 majors, and that schools have the same programs/majors.

Now, my typical approach in this scenario is to take anything that may be repeated (i.e., majors and programs), and put those items into their own table; then have a relationship, as modeled above. Another approach, one I tend to stay away from, is the second model, in which program and major are columns with repeated values (e.g., Engineering may be repeated 1,00s of times over the table). Basically, if the value is repeated, I create a table for it.

Now, I'm not so much interested in which one of these is the better approach, since I am only using them as an example to illuminate the true question: How does one know when they are over-normalizing? I know that there is a point when you're going too far in normalizing your tables, but I never quite know what the measurement is.

Addendum

A university need not have all the majors in a program, thus the reason universities are tied to majors, not programs (e.g., University X has an Engineering school but does not have Nuclear Engineering, which is part of the Engineering program).


texts: Cannot rebuild index, but there's no reason why not?

texts:
I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one particular index and I see no reason why I should.
It fails with the following message:

Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index 'I_520CUSTVENDRELIDX' because the index contains column 'MEMO' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

However, when I run the query based on a suggestion by this chap, shown below, I get no results:
SELECT *
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
AND ((c.system_type_id IN (34,35,99,241)) -- image, text, ntext, xml
 OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary
     AND max_length = -1))
INNER JOIN sys.indexes as si
on si.object_id = ic.object_id
AND ic.index_id = si.index_id
inner join sys.tables t
on t.object_id = ic.object_id
where t.name = 'CONTACTPERSON'
and si.name = 'I_520CUSTVENDRELIDX'

On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX).  Could there be something I'm missing here?
For the record, this is a clustered index.


texts: Foreign key constraints check during replication?

texts:
I am a little bit new to replication and can't seem to find an answer to this anywhere.
Let's say I have a simple relation in my database: Department table with Code column as PK and Person table referencing Department by Person.DepartmentCode -> Department.Code.

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_Person]
FOREIGN KEY([DepartmentCode])
REFERENCES [dbo].[Department] ([Code])

Now, I would like to implement merge replication to a branch database of only relevant departments and people. I could set up filtering by DepartmentCode in all the tables that will be partitioned, which would do the trick. But I wanted to see if I could leverage referential integrity to do part of the filtering for me. I.e. only filter dbo.Department and rely on constraints to not let irrelevant people into the replica's dbo.Person. However, what I get in the replica is the dbo.Department with filtering properly applied and dbo.Person with DepartmentCode values breaking the foreign key constraint (and the new records with "wrong" DepartmentCode continue to be replicated into the branch's DB). The constraint is applied and enforced for any other transactions, though. So, if I try to insert a record breaking the integrity with a user INSERT, I can't.

So it looks like the referential constraint isn't being checked during replication of the data.
1. Is there a way to enforce constraint checking for data inserted by replication?
2. Would it be a good idea to use that for filtering/partitioning?


texts: How encryption in SQL login authentication works

texts:
I am going through the below link

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server

and in that it says

  When SQL Server logins are used, SQL Server login names and encrypted
  passwords are passed across the network, which makes them less secure.

how are the password encrypted and how does sql server decrypt it?  What kind of encryption is involved in it?


texts: Scale MySQL trigger for thousands of inserts per seconds

texts:
We are using MYSQL database for an Analytics app. I have two table token and token-big (for overview of actual data) both have id(int) unix_starttime(int) unix_endtime(int) details(varchar 256)

A row is added to table token every second using python MySQLdb. I have an after insert trigger on token table which does the following:

Get the latest entry (last) from token-big's
If unix_starttime of newly inserted row (in token) is no more than 5 seconds apart from unix_endtime from token-big:

update unix_endtime of last row in token-big to unix_endtime of newly added row in token
else insert new row in token-big same as the newly added row in token

This works fine for a few token token-big table pairs but I start to get deadlocks if these pairs (tables) are in number of hundreds.

How can I scale this method? Is there any better alternative?

I don't wish to use events as it may delay the data shown to user. Same goes for background jobs which run every few minutes or so.


texts: Postgres Slow Queries - Autovacuum frequency

texts:
We've noticed the performance of our platform drop in recent weeks so I've run the following:

select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
from pg_stat_user_tables
where relname like 'core_%';

And noticed that our primary table hadn't been autovacuumed for more than a week. So last week I ran:

vacuum analyse verbose TABLENAME

Which seemed to help, but we've run into the same issue again now. Upon closer inspection, a lot of the tables have either never been analysed (auto or otherwise) and aside from the manual vacuum analyse run last week, none of the tables have been manually vacuumed, and a lot of the other tables haven't been autovacuumed since, at best, a few days ago, and at worse a few weeks ago.

My understanding of the terms is as follows:

vacuum: Clears out deleted records from disk
analyse: Updates the
query planner

In postgres.conf, the autovacuum property is commented out, but the documentation states that this is on by default, so my presumption is that even though it's commented out, it should still be on?

Can someone explain why the tables wouldn't be getting vacuumed and analysed frequently and more specifically, would these values not getting updated actually have that much of an impact on the system?

Info:
Postgres 9.1
OS: Ubuntu 12.04

Output of

SELECT relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) As "Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

     Table       | Size | External Size
-----------------+------+---------------
"Primary Table"  | 27G  |     8232M


texts:
Environment:

Windows Server 2012
SQL Server 2014 Standard
Public website (Site-A) on Server
Private website (Site-B) on Server
Windows App running on workstations

SQL Server 2014 Standard:
2 databases - DB-A & DB-B

Class Library:
Handles get/set of all data via stored procedures. Data is on DB-A & DB-B

Site A:
Public facing eCommerce ASP.NET website (http://www.rackattack.com) using a class library to interface with DB-A & DB-B. Contains additional calls (via Stored Procedure) to both DBs

Site B:
Private ASP.NET website using a class library to interface with DB-A & DB-B. Contains additional calls (via Stored Procedure) to both DBs

Windows App:
In house app that uses class library to interface with DB. Contains additional calls (via Stored Procedure) to both DBs.

Question: How do I want to set up the Server Logins, Database Schemas, Database Roles, Database Users to comply with best practices for our Environment?

My Thoughts:

Server Login for Site-A, Site-B, WinApp
DB Role for ClassLib - permission to call stored procs for Lib
DB Role for Site-A - permission to call stored procs for Site-A on DB-A & DB-B
DB Role for Site-B - permission to call stored procs for Site-B on DB-A & DB-B
DB Role for WinApp - permission to call stored procs for WinApp on DB-A & DB-B
DB User for Site-A (member of Role for ClassLib & Site-A)
DB User for Site-B (member of Role for ClassLib & Site-B)
DB User for WinApp (member of Role for ClassLib & WinApp)

Question:

Does this set up make sense?
Do I want separate roles for the ClassLib and the Site users?
Where do I add the premissions to execute the list of SPs in need, in
the role or should I be using schema for this as well? If so, how?

Disclaimer: I am a developer and not a DB admin. I have a basic understanding of the concepts but please "Explain like I'm 5". Thank you!


texts: How I can copy a collection from a Mongo Cluster to another Mongo Cluster?

texts:
I have a collection with more than 1 TB size and wanted to move it another MongoDB cluster

PS: Both clusters are sharded over 5 shards and they are on-prem infra.


texts: ERROR 1146 (42S02): Table 'db.tablename' doesn't exist

texts:
I have a two databases, when I prompt SHOW DATABASES I can see a list of the databases, including mysql, performance_schema and informations_schema.
I can prompt 'SELECT DATABASE_NAME' on both of them, I can prompt 'SHOW TABLES' on both of them, but when I try to query I get the error mentioned in the title of this question.
I use MySQL 5.7.7, and the InnoDB is set to FILE_PER_TABLE. All files are in the correct folder, including the ibdata1 and both of the log files.

Does anyone has a solution?


texts: getting the error message from another trigger in sql server

texts:
i have a table 'Event' and it contains a trigger trEvent which updates table 'Position', and table 'Position' has a trigger trPosition which validates the Position records.
whenever trEvent is fired one record in Position table is updated and the trPosition trigger is fired to check the validation of position, if it is a valid position there is no problem but if the position is invalid how the trEvent should know there is something wrong with the Position which is updated.

in simple words how to access error message from trPosition trigger in trEvent trigger?

thanks in advance!


texts: sp_send_dbmail unable to execute from service broker activation sp

texts:
I'm using Service Broker to [maybe] monitor my database events, so I have my activation sp in some user database and it should react on some events by sending e-mail to admin. But it fails with the error

  The EXECUTE permission was denied on the object 'sp_send_dbmail',
  database 'msdb', schema 'dbo

while trying to use sp_send_dbmail.

How can I manage the permissions in my case?


texts: Do I need an Availability Group Listener if I have "Failover Partner" in my connection string?

texts:
I am a developer trying to work with a new Sql Server 2016 Always On cluster.

In reading documentation I am getting conflicting information.

Some documentation tells me that I just need to add "failover partner=MySecondaryServer" to my connection string.
Other sites talk about using an "Listener".  And in my Always On section in SSMS 2016 there is an Availability Group Listeners section.

What is the difference between these options?

Are these competing options (meaning you pick just one) or do they work together?

Notes: I have tried the "failover partner" and got limited success.  When the primary rebooted, I had to restart my Entity Framework service before it "failed over" to the secondary server.  I am thinking about setting up a Listener to see if it will work more dynamically.


texts: When you finish writing a query/stored proc/function, what's the most informative way to quickly get some performance parameters?  Do you run the query and view the actual execution plan?  If so, what are the things you look for?  Obviously table/index scans are the bit hits, but what else?


texts:
Ok,

So I'm working on an app and I can't remember, for the life of me, why I set a zerofill on an Auto_Increment column when I created the database. And I couldn't exactly find any compelling info while searching as to why I'd use or not use zerofill on this column.

After all, it does not affect the data in the filed (or the int value), it is just for formatting sake.

So, I figured I would get your take on this matter.

Why Use (or not Use) Zerofill on an Auto_Increment field (or for that matter any int field).


texts: SSRS Query Timeout settings: rsreportserver.config verses RDL Query.Timeout

texts:
I'm trying to understand the interaction between the SSRS DatabaseQueryTimeout (set at the server level, defaults to 120 - does this only apply to connections to the ReportServer internal database?)

RsReportServer.config Configuration File

and the RDL Query.Timeout setting (set in individual RDL, defaults to 30)

Setting Time-out Values for Report and Shared Dataset Processing (SSRS)

If I have an RDL with no setting defined, will it timeout at 30s or 120s?


texts: How to see amount of reusable space for a table

texts: I had a huge table(about 300mil rows). After massive DELETE and VACCUUM table files have stopped to grow. That's exactly what I expected. But is there any way to see how much space became reusable?


texts: on insert rows in a table how to update that inserted data in another table?

texts:
I'm trying to update large amount of rows (20,000) rows, so i created a temp table similar to this one. and i want to create a trigger on insertion or update and a function to take the inserted or updated data and updated in the B table where identifier column is the unique id in both A and B tables

The structure of both tables will be something like this:

user (text)
identifier (text)
product (text)
status (text)
sales (numeric)
purchase (numeric).

how to write a trigger and a function in postgreSQL to update data in B table on insert or update in A table?

Note
I want to insert the data in a temp or normal table AKA(A table) and then do the update in B table with SQL only


texts: How to use Blob Storage as shared storage in a Windows Azure Failover Cluster?

texts:
In the context of SQL Server 2012 Standard Edition, is it possible to make Azure Blob storage available to a SQL Failover cluster directly as a shared SCSI resource on a Windows Cluster composed on VM's on Azure? An Azure blob is effectively shared storage, and a newly added disk on an Azure VM, does show up as a SCSI disk, before it is converted into a Volume (GPT).

But converting it into a GPT volume on one of the nodes, or on another node defeats the purpose (making that node a single point of failure). The ideal solution should involve directly exposing it to the Windows Cluster (before setting up the SQL Server Cluster).

Does SQL Server support writing to such a shared blob storage, directly, from two clustered instances?


texts: MySQL failed connection attempts hitting max_connections

texts:
I run a couple of websites but I believe one of them is causing too many Connections Failed Attempts that causes max_connections variable to hit.

Currently, max_connections=2500 (already large) but total connections per hour due to failed attempts have become greater than 3000. How do I debug this? The httpd error log for the website does show a couple of PHP warnings/notices though. For example, using mysqli instead in the database config.


texts: 2 Very similar queries return different number of result (one is null)


texts: extra steps after changing storage engine and adding index

texts:
Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases.

I have a php/MySQL site uses the MyISAM storage engine.  It doesn't have any indexes on the columns.

I want to change the engine to innoDB and add indexes to the columns.

Some tables have a couple hundred thousand rows so it's not a very small database.

My question is mostly about the data that already is in the database.  Do I need to do anything after these changes to make the already stored data aware of that ? or make them compatible with these changes ?


texts: PostgreSQL: How to list all stored functions that access specific table

texts:
Introduction:

PostgreSQL database with several hundred of stored functions, including obsolete, not used etc.

Problem

I need to find out all the stored functions that have any relationship to the table X - as I want to change the table structure. Some of them might be not used, so I can't do that just by looking through the code.

The solution I have ATM is running psql's \df+ and grepping output, but I'd prefer more database-like solution, i.e. by using information schema. This will definitely be a repetitive task and I'd like to have it nice and clean.

Any suggestions?


texts: Where are user defined functions saved in PostgreSQL?

texts:
To clarify the issue I am having, I am writing pgtap unit tests on DB and one of my functions returns a table. However, the pgtap function function_returns does not have a definition for table check and their columns (names and data types).
These are all the overloads of the function:
SELECT function_returns( :schema, :function, :args, :type, :description );
SELECT function_returns( :schema, :function, :args, :type );
SELECT function_returns( :schema, :function, :type, :description );
SELECT function_returns( :schema, :function, :type );
SELECT function_returns( :function, :args, :type, :description );
SELECT function_returns( :function, :args, :type );
SELECT function_returns( :function, :type, :description );
SELECT function_returns( :function, :type );

You may read the full definition and what each parameter stands for in the official documentation
Basically the issue is that :type does not work for table.
So I am wondering where these user defined functions are saved in Postgres (trying to write my own version and hopefully contribute to the pgtap project).
After digging in the source code I found the function used in the background to check for this, it's called _func_compare
CREATE OR REPLACE FUNCTION _func_compare( NAME, NAME, NAME[], anyelement, anyelement, TEXT)
RETURNS TEXT AS $$
    SELECT CASE WHEN $4 IS NULL
      THEN ok( FALSE, $6 ) || _nosuch($1, $2, $3)
      ELSE is( $4, $5, $6 )
      END;
$$ LANGUAGE SQL;

And I can't find out how the author was checking for valid return definition of the function.


texts:
I have a table like:

ID   A0  A1  A2 A3 A4
14   A   B   A  C  A
15   A   A   A  A  A

I need to compare all 5 columns and check if they have same value and retrive the only one that have all same values....in this case the one with ID 15.
Is there a way to accomplish that in T-SQL?


texts: Moving a hard disk with PostgreSQL data folder to another server

texts:
I'm running a NAS server with an SD card mounted as / and RAID-1 storage mounted under /media/hdd.  /etc folder is on SD card.  PostgreSQL data folder is on the RAID-1 disk.

/-+
  +- etc
  |  +- postgres
  |     +- 10
  |        +- mycluster
  |
  +- media
     |
     +- hdd
        +- pgsql
           +- data
              +- mycluster

If I want to move RAID-1 disks to a different box, what is the correct way to re-create a PostgreSQL cluster using exiting data folder?  Let's assume I stopped the cluster, shutdown the system, took out hard disks and attached them to another box.  Can I create a new cluster on that box using the existing data folder on the attached hard disks?

Thank you in advance.


texts: Exhaustive SQL stored proc validation make insert slow

texts:
So basically our team is working on a module to retrieve rows value from excel file and insert it during batch job.

The requirement are as follow:
- Exhaustive validation should be done for each column, and the validation error message should be descriptive according to requirement specification
eg: Error in Column [x] due to invalid date format/exceed max length.
- Error in each column should be inserted into database (not log file)
- Insertion should be done ROW by ROW, no bulk insert. Any fail insertion description (due to invalid data) should be inserted into database.

The proposed solution is as follow:
So, first we will insert excel data row by row into excel file list. If it pass all validation, it will include all value except ErrorDesc. If it is failed it will include Error Desc and other excel row value except line will be null.

As what we observed over here, the validation (easily go to 30 to 40 depending on column number and validation requirement), as well as checking whether the memberclassname and membercountryname has corresponding ID in the database makes the insertion slow, ranging from 0.2 to 0.5s per insertion in ExcelContentList. We are currently performing select statement for each insertion to check whether memberclassname and membercountryname exist and has corresponding ID.

We appreciate if there is any suggestion/improvements.


texts: SQL Server - Combining Bulk Insert with Manual value

texts:
I have a Comma separated CSV file containing ID, FirstName, and LastName. I am trying to insert it into my existing table, however the table has few more column which the CSV file is missing thus I am wondering if it's possible to insert the CSV file combined with manual value mapping ?

Below is how my table looks like:

And this is how my CSV file looks like:

As you can see, the CSV file only contains the IdentityId column of the Table (is's called Id in the file) and Email and other fields are missing, therefore I am wondering if I can manually provide them or I need to insert the bulk item first, and then write a command to update all of them?


texts: Pagination while sorting a non unique text column

texts:
What are the ways to paginate a table on a non unique text column?

On a unique timestamp column, one can create an index on it and execute the following to move forward

SELECT * FROM users WHERE created > '<last element created timestamp>' ORDER BY created ASC LIMIT 20;

If it is a numerical primary key

SELECT * FROM users WHERE id > 100 ORDER BY id ASC LIMIT 20;

But what can one do when the table is ordered by non unique text column (first_name for example)?

First thing that comes to mind is the OFFSET method, but there are performance penalties on large tables.

Alternatively, one could use a cursor, but the internet agrees that it not recommended way on a public web application.


texts: How to check Auditing in Azure MSSQL MI?

texts:
I know that auditing is currently enabled in my azure MSSQL server, but how can I access these log files?
Could it be done through SSMS?

I need to know details about user activity in my databaase.


texts:
I understand SQL Server 2005 doesn't support DATE and so casting a datetime as a date is not possible.

What I want to do is list all records with a datetime within an entire day of the current week.

So I need all records created on Monday of current week ignoring the timestamp. Currently I am using the below, which does show Monday-Friday of current week, but includes the timestamp, which means I'll see different output depending on when the query is run:

set datefirst 1

select
    DATEADD(day, 1 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Monday,
    DATEADD(day, 2 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Tuesday,
    DATEADD(day, 3 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as wednesday,
    DATEADD(day, 4 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Thursday,
    DATEADD(day, 5 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Friday

Can anyone assist?

My expected output would be a column for each day of the current week, where the week starts on Monday (i.e.: Datefirst 1). Ultimately I'm going to use it in a case statement like:

CASE
WHEN DateColumn >= DATEADD(day, 1 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) and DateColumn < DATEADD(day, 2 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE()))
THEN IntegerColumn
ELSE 0
END AS Monday

...Repeating for each day of the week. I need to accommodate for IntegerColumn values which are added throughout Monday. If no DateColumn entries occur within the hours of Monday, a zero is displayed.


texts: Unfamiliar syntax - Query with Parameters in Braces at the beggining

texts:
I have run sp_WhoIsActive on one of our servers using the following syntax:

sp_whoisactive @get_plans = 1, @show_sleeping_spids = 0, @get_outer_command = 1, @get_locks = 1

and found a spid with the sql_command (the column shown when @get_outer_command is set to 1) as follows

(@p1 int,@p2 int)
Exec MyDatabase.MyProc @p1 @p2

When I try to run a query with this syntax on my test Adventureworks database:

(@be int)
SELECT  *
FROM    Person.Person
WHERE   BusinessEntityID = @be

I get the error

  Msg 1050, Level 15, State 1, Line 1
  This syntax is only allowed for parameterized queries.
  Msg 137, Level 15, State 2, Line 4
  Must declare the scalar variable "@FN".

so it seems to be something to do with parameterized queries. This makes sense as the variable @be is never set to a value

What is happening here?


texts:
I'm not sure if this is the best SE section to ask my question. If not, I kindly ask you to help me to find out the best one.

Developing a web application in php and MySql I'm in doubt how to store a huge quantity of data from arrays.

Scenario

The application receives text messages from clients around the world. The number of clients might reach several thousands.
Each one sends few times per day a message with a text payload (json).

The json contains few objects to identify the client itself and an array of about 10 objects, in example:

{
    "id_client": "abcdefgh",
    "id_msg": 3245,
    "datetime": "20180308101130",
    "data": [
        { "temp": 20.5 },
        { "humy": 45.1 },
        { "batt": 12.0 },
        { "cnt1": 123456 },
        ...
    ]
}

I need to store this information in the database.

My thoughts

The keys of the dictionary passed in the data array are stored into a separate table, along with their description, units, etc...

The first three fields (id_client, id_msg, datetime) are required to identify exact message received. Hence, when I receive such a message, currently, I create a row in a table for each entry in the dictionary:

+-----------+--------+----------------+------+--------+
| id_client | id_msg |    datetime    | key  | value  |
+-----------+--------+----------------+------+--------+
| abcdefgh  |   3245 | 20180308101130 | temp | 20.5   |
| abcdefgh  |   3245 | 20180308101130 | humy | 40.1   |
| abcdefgh  |   3245 | 20180308101130 | batt | 12.0   |
| abcdefgh  |   3245 | 20180308101130 | cnt1 | 123456 |
+-----------+--------+----------------+------+--------+

I wondering if this is the right approach for such a situation.
I'm afraid about the number of lines: if I have 1000 clients that send a message with 10 entries 2 times per day, I will append:

1000 * 10 * 2 = 20.000 rows per day
20.000 * 30 = 600.000 rows per months
20.000 * 365 = 7.300.000 rows per year

The data should be online at least year-by-year in order to make queries and statistics.

What about this approach? Is there something better for this kind of applications?


texts: What's better/faster? MySql or FileSystem?

texts:
Let's imagine a web site that is a directory of people. For each person there may be a profile photo and a biography.

I'll admit my SQL queries could be better but in general what would be faster and use less processing power.

To check if a file exists and then open it
 or

check against MySql to see if a bio exists and display it.

I'm pretty sure in the above case the filesystem will smoke the mysql database.

What if I make the database a read only delimited txt file?

What's faster in this case?

Is there a certain point where if the txt file has too many records it's better to use MySql?


texts: MySQL high CPU usage (MyISAM table indexes)

texts:
I have a problem with an inherited MySQL database.
From time to time mysqld uses up to 2300% CPU..
The only solution is to service mysql stop and run an myisamchk -r on a table.
After the indexes have been fixed, I start MySQL and everything is ok.

Any ideas for an permanent solution?

Edit (from the comments):

Using 5.5.29-0ubuntu0.12.04.2-log

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 500
#table_cache = 512
#thread_concurrency = 10
query_cache_limit = 1M
query_cache_size = 16M

SELECT SUM(index_length) ndxsize
FROM information_schema.tables
WHERE engine='MyISAM'

returns

+----------+
| ndxsize  |
+----------+
| 59862016 |
+----------+

SELECT SUM(data_length+index_length)/power(1024,2) datndxsize
FROM information_schema.tables
WHERE engine='MyISAM'

returns:

+--------------------+
| datndxsize         |
+--------------------+
| 488.69915199279785 |
+--------------------+

The server has 16GB of RAM, but it is not a DB server...It is running nginx + php-fpm


texts: MySQL query - how to remove variable data

texts:
I wonder if someone can help me please? I recently migrated from PHPBB forum software to MyBB software, but when the conversion finished I was left with lots of weird characters in many posts, such as
, </e>, etc.

I used the below query to remove things like this, and it has worked well and removed lots of these characters:

UPDATE mybb_posts SET message = REPLACE (message, '
', '') WHERE message LIKE '%
%';

My problem now is that the stuff I need to remove varies from post to post - here's a few examples:

">https://www.ebay.co.uk/itm/SUZUKI-DR-75 ... Sw7k9eIFHa

">http://www.hagon-shocks.co.uk/catalog/c ... x?TypeID=F
">https://www.moorespeedracing.co.uk/indi ... gKQjPD_BwE

As you can see, each line that I need to remove starts and ends with the same group of characters.
I modified the above query using the % as the wildcard, so the query became:

UPDATE mybb_posts SET message = REPLACE (message, '">htt%m -->', '') WHERE message LIKE '%">htt%m -->%';

When I click "Simulate Query" I get a message saying 1643 rows affected, but when I click "Go" to run the query it comes back and tells me 0 rows affected.

Is there something wrong with my query? Can anyone suggest how to fix it or suggest another query to achieve the same outcome?
TIA


texts: Table creation along with dynamic partitioning (interval partitioning) in Oracle 10g EE

texts:
I am trying to create partitioning using interval partitioning using the below given query:

CREATE TABLE DATA_HOURLY
(
  tmstamp TIMESTAMP,
  elementName VARCHAR2(255),
) PARTITION BY RANGE (tmstamp)
interval (numtoyminterval(1,'MONTH'))
(
   PARTITION p1 VALUES LESS THAN (to_date('2016-01-31','yyyy-mm-dd'))
);

The error that I am getting in SQL Developer is:

  SQL Error: ORA-00922: missing or invalid option
  00922. 00000 -  "missing or invalid option"
  *Cause:
  *Action:

How can I fix this issue?


texts: Database for efficient range aggregate queries?

texts:
As a simplified example, suppose I have a table like this:

seq | value
----+------
102 | 11954
211 | 43292
278 | 19222
499 |  3843

The table may contain hundreds of millions of records, and I need to frequently do queries like this:

SELECT sum(value) WHERE seq > $a and seq < $b

Even if seq is indexed, a typical database implementation will loop through each row to compute the sum in best case O(n), where n is the size of the range.

Is there any database that can do this efficiently, as in O(log(n)) per query?

I have come across a data structure called a Segment Tree as described here. Also sometimes referred to as a range tree or interval tree, although all these names are often described as a slightly different variation of the data structure.

However, I have not come across any database that implements such a data structure. Implementing it from scratch is easy for an in-memory structure, but becomes tricky if it has to be persisted or is too big to fit into memory. If there is an efficient pattern for implementing this on top of an existing database, that could also help.

Side note: This is not an append-only table, so a solution such as keeping a cumulative sum will not work in this case.


texts: Unable to recover MySQL 5.6 (InnoDB) database

texts:
I've tried multiple different suggestions from other questions on here, with no luck...

This is a MySQL database running version 5.6.41, using the InnoDB engine, and the instance is running out of a Docker image. (https://hub.docker.com/_/mysql?tab=tags&page=1&name=5.6.41) The image has my main database files (ibd and frm files) symlinked outside of the instance.

Here's the error:

InnoDB: Error: trying to access page number 0 in space 842,
InnoDB: space name <DATABASE>/<TABLE>,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
2019-09-12 14:59:26 7f2fe639e700  InnoDB: Assertion failure in thread 139843702744832 in file fil0fil.cc line 5666`

I attempted to use the innodb_force_recovery flag at all values (including 5-6). The error above happens when I use the mysql command line tool, and enter DROP TABLE <DB>.<TABLE>. The main problem is I cannot access the database to even attempt any of these recovery options any more.

This likely started happening following a host (MacOS 10.13.6) system crash which caused an unexpected shutdown of my Docker container during file write.

I believe either my ibdata or ib_logfiles are corrupt, but I can't figure out how to recover the database without wiping everything clean and recovering from a mysqldump (which may not even work).

I don't care about the data in this table, I can recover data from a development/testing MySQL server. I'd just like to not go through the hassle of starting everything from scratch.

Here are a few other stackex q's I've looked at: MySQL can't delete table

Trying to solve "outside tablespace bounds" MySQL error, but recovering table with mysqldump fails

Any suggestions?
(If you need more info, like my variable settings/my.cnf files, or database structure info, please ask)


texts: How do I recover data from the postgres data directory on my hard drive?

texts:
I had an old iMac which stopped working a while ago
I take weekly backups of the database I was updating which contains feed items from several rss feeds
I am still missing the data for the last week
I currently have the hard drive of the old machine which contains the postgres data directory (it was an Intel based iMac)
My new machine is an Apple M1 based mac mini and I want to load all those feed items
I want to recover a particular table called feed_items from a specific database called ch_v3 from the data directory on the hard drive containing old postgres files
How do I recover the 100k+ feed items which are stored in raw format on my old hard drive into my new postgres installation on the new machine?


texts:
Buildings and Facilities have a many-to-many relation with a linking table of ids in between.  I would like to select Buildings with specific Facilities, and also show each Building's other Facilities in a single column.

Using GROUP_CONCAT works if I SELECT based on TABLE Buildings,

SELECT `b`.`building_name`, `b`.`address`,
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`

http://sqlfiddle.com/#!9/ceec20/10

but if I also SELECT based on Facilities, only that Facility is returned in the GROUP_CONCAT.

SELECT `b`.`building_name`, `b`.`address`,
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1 AND `facility_id` = 2
GROUP BY `building_id`

http://sqlfiddle.com/#!9/ceec20/11

How can I return results including all of each Building's facilities?


texts:
I have a server running Windows Server 2008 R2 Enterprise 64Bit, with 256 GB RAM.

I am running SQL Server 2008 R2. With normal usage, SQL Server is using about 232 GB of RAM.
Occasionally it will creep up approaching 100% RAM usage; this has been causing a lot of problems

I know that I can set maximum server memory usage. I don’t know what to set the usage to. I have been going over the SQL Server documentation and TechNet forums and have not been able to find a best practice or even a consensus as to the correct settings.

I guess what I am looking for is a SQL Server 2008 R2 resource management best practice guide would someone be able to point me in the right direction, or offer some advice. Thanks in advance.


texts:
I cannot start MongoDB on Debian(Jessie 8.4 Stable).
I generated the keyfile and added the path to the key file to the configuration and uncommented the "auth=true" but when I try to start the service I get the error :

Job for mongod.service failed. See 'systemctl status mongod.service' and 'journalctl -xn' for details

When I see into the logs I find this :

error getting file /var/lib/mongodb/mongodb-keyfile: Permission denied


texts: ORA-00922 encountered in Oracle 11gR2 installer

texts:
I've just run the installer for Oracle 11gR2 on a Windows Server 2008 (64-bit) OS.

I encountered the "ORA-00922: missing or invalid option" error at the last stage of installation when it was doing something like "cloning the database". I ignored the error and then immediately got a "ORA-28000: the account is locked" error. The installer then completed successfully.

I looked in the logs for the installation ("C:\app\administrator\cfgtoollogs\dbca\orcl") and found one file named "postScripts.log" which contains these two error messages. Here is the contents of that file:

     1
     1
alter user CTXSYS account unlock identified by ******
*
ERROR at line 1:
ORA-00922: missing or invalid option

ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.
dropping default lexer preference...
SP2-0640: Not connected
dropping default wordlist preference...
SP2-0640: Not connected
dropping default stoplist preference...
SP2-0640: Not connected
dropping default policy...
SP2-0640: Not connected
SP2-0640: Not connected
Enter value for lang_abbr: SP2-0310: unable to open file "C:\app\Administrator\product\11.2.0\dbhome_2\ctx\admin\defaults\drdefset.sql"

Has anyone else encountered this error before, or know how to resolve it? Will the database function correctly?

Thanks in advance!


texts: Is it possible to view the maintenance plans created in SQL Server 2000 in SSMS 2005?

texts:
Is it possible to view the maintenance plans created in SQL Server 2000 (also running on the same SQL server 2000) in SQL 2005 Server Manangement Studio? I have the server added in my SSMS, but when I expand the Management folder, I don't see the Maintenance Plans folder like what I'd expect from a 2005 server.

For example, we have an optimization plan scheduled to run every week in SQL Server Agent and the command used to run this is: xp_sqlmaint N'-PlanID F7491D04-CDBA-496F-AE57-C2C5A3E3C8F8 (plus a bunch of options). I have no idea how to view this!


texts: Restructure create and delete events on rows into single table

texts:
Similar to this problem: Using start and end event logs to create a table/view containing spans between the times of each log.

I have table that logs 'create' and 'delete' events for a resource that corresponds to an object.

Every event:

Is it's own row
Has a unique ID
Has a timestamp of when it occurred.

-------------------------------------
| id   | type   |object| timestamp |
-------------------------------------
| 1    | create | A    | 2019-1-1  |
| 2    | create | A    | 2019-1-2  |
| 3    | delete | A    | 2019-1-2  |
| 4    | create | B    | 2019-1-1  |
| 5    | delete | B    | 2019-1-2  |
| 6    | create | A    | 2019-1-3  |
| 7    | create | B    | 2019-1-4  |
| 8    | create | C    | 2019-1-5  |
-------------------------------------

The question I want to answer is how many resources still exist for a given object.

Example:

According to the data in the sample table, Object 'A' still has 2 resources that exist (three where created, one  was deleted.).  Object B and C each have one remaining resource.

Is it possible (as in the linked problem) to restructure this in a view where I have create and delete columns with timestamps?


texts:
in a past few days, I have a problem with my XAMPP (v3.2.2). The problem is that MySQL randomly stops running a query in PHP. Then I need to restart MySQL from XAMPP console and it is working again. There is no error in XAMPP console. I can see the page (i guess that Apache is working) but when I run for example some search query in PHP I won't show result until I restart MySQL in XAMPP.

I check file 'mysql_error.log in XAMPP\mysql\data and it is 2GB big. When I open it I noticed that I am getting the same error:

2018-06-12 11:11:57 4696 [ERROR] InnoDB: Table phpmyadmin/pma_usergroups in the InnoDB data dictionary has tablespace id 7, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

This repeat with other tables also.

I am a beginner at using XAMPP so please can you suggest how to find what is causing the problem.


texts: What is the actual lowest possible positive REAL number

texts:
MSDN says  that the range of REAL numbers is - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. Apparently the true lower limit is much lower. The following script populates a REAL column with 1.401298E-45:

CREATE TABLE a
  (
    r1 REAL NULL ,
    r2 REAL NULL ,
    r3 REAL NULL
  ) ;
GO
INSERT  INTO a
        ( r1, r2 )
VALUES  ( 1.18E-37, 10 ) ;
GO
DECLARE @i INT ;
SET @i = 1 ;

WHILE @i < 20
  BEGIN ;

    UPDATE  a
    SET     r1 = r1 / r2 ;

    SELECT  r1 ,
            r2
    FROM    a ;

    SET @i = @i + 1 ;

  END ;
GO
DROP TABLE a ;

r1            r2
------------- -------------
1.18E-38      10

(snip)

r1            r2
------------- -------------
1.401298E-45  10

Can anyone tell me what is the actual lowest possible positive number?


texts: How to configure Standby database in oracle for read/write

texts: I have configured a physical standby database in Oracle 11gR2, and the standby database it's working fine. All the changes in the principal database are replicating to the standby database, the standby database is in read only mode. I want to configure the standby database for replicating the changes done in the standby database to the principal database, I mean i don't only need to open in read/write mode but also to apply the changes done in the physical standby to the principal. Any ideas of how to configure it? I have searched in the internet but I only find to open in a read/write test environment.


texts: How to find queries that use a given index for mysql?

texts:
I am looking to change indices on my MySQL table. My hunch was that one of the indices, that I am looking to remove, shouldn't be used. But it is being used, based on table_io_waits_summary_by_index_usage data.
I want to find queries that use this index so I can reason what would happen to such queries and if they will fallback to another existing index.

Is there a place where MySQL stores query plans? Or any other way of finding the queries that use a given index?


texts: What privileges do I have to grant to allow a user to select from information_schema.table_privileges?

texts:
I'm using MS SQL Server 2008, and I am trying to get a complete listing of all privileges assigned to all grantees by running this query:

select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES

However, right now I'm only getting back records associated with the "public" grantee. I've tried adding the db_owner role to this user account, and I've also run this query within the database:

GRANT VIEW DEFINITION TO myuser

Neither of these actions has changed the response from the above query - still only getting public grantee records. What is the minimum setting I need to do to grant the user the rights to all the records?

Thanks


texts: SQL Server grant permissions to STANDBY database

texts:
So, first: the setup. We have SQL Server 2012 (ServerA) running in domain A. We have SQL Server 2012 (ServerB) running in domain B, separate subnet, no trusts. These are completely separate domains for security reasons and they cannot be joined/trusted/etc. We need to be able to query the data directly from domain B via Windows Authenticated logins. I was able to use this guide to set up transaction log shipping to get the databases from ServerA to ServerB (summary: create the transaction log shipping config, use WinSCP to copy the logs to the remote server, manually create the secondary using SQL script). So now we have the two databases running in STANDBY/read-only on ServerB.

Now, the problem: we cannot grant access to these databases because they are in read-only so we cannot modify the permissions. How can I grant read-only access to these databases (either at the server level or DB level) to a domain group from DomainB on ServerB? I've found several references to creating a SQL login on the sending side, but I can't find any way to replicate it with a Windows Auth Login.


texts:
Can I speed up somehow the following query? Somehow to stop from creating a tmp table/filesort.
Tried a couple of indexes but no luck.

mysql> explain

SELECT wp_posts.ID
FROM wp_posts LEFT JOIN wp_postmeta
  ON (wp_posts.ID = wp_postmeta.post_id
  AND wp_postmeta.meta_key = '_best_protected')
WHERE 1=1
  AND wp_posts.post_type IN ('post', 'best')
  AND ((wp_posts.post_status = 'publish'))
  AND ( wp_postmeta.post_id IS NULL )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 5
\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wp_posts
         type: range
possible_keys: type_status_date
          key: type_status_date
      key_len: 124
          ref: NULL
         rows: 39466
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: wp_postmeta
         type: ref
possible_keys: post_id,meta_key
          key: meta_key
      key_len: 768
          ref: const
         rows: 1
        Extra: Using where; Not exists
2 rows in set (0.02 sec)

wp_posts structure :

Create Table: CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_category` int(4) NOT NULL DEFAULT '0',
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  `post_password` varchar(20) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=397327 DEFAULT CHARSET=utf8

wp_postmeta structure :

Create Table: CREATE TABLE `wp_postmeta` (
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=78686680 DEFAULT CHARSET=utf8


texts:
I have been trying to write queries so that I can get the BFS & DFS traversal of the tree. I am getting the DFS preorder traversal right but unfortunately, I am not able to get the BFS part right.
CREATE TABLE GenericTree (
    NodeID INT NOT NULL,
    ParentNodeID INT NULL,
    CONSTRAINT PK_GenericTree PRIMARY KEY (NodeID),
    CONSTRAINT FK_GenericTree_Parent FOREIGN KEY (ParentNodeID) REFERENCES GenericTree (NodeID)
);
GO

INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (1, NULL);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (2, 1);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (3, 1);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (4, 1);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (5, 2);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (6, 2);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (7, 3);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (8, 3);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (9, 3);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (10, 6);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (11, 6);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (12, 6);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (13, 6);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (14, 7);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (15, 8);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (16, 8);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (17, 11);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (18, 11);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (19, 16);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (20, 16);
GO

CREATE PROCEDURE usp_DFS_PreOrder
@NodeID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    /*
        EXECUTE usp_DFS_PreOrder;
    */

    DECLARE @v_NodeID INT;
    DECLARE c_CUR CURSOR FAST_FORWARD LOCAL FOR
        SELECT NodeID
            FROM GenericTree
        WHERE ISNULL(ParentNodeID, 0) = ISNULL(@NodeID, 0)
        ORDER BY NodeID;
    OPEN c_CUR;
    FETCH FROM c_CUR INTO @v_NodeID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @v_NodeID;
        EXECUTE usp_DFS_PreOrder @v_NodeID;
        FETCH FROM c_CUR INTO @v_NodeID;
    END;
    CLOSE c_CUR;
    DEALLOCATE c_CUR;
END;
GO

This is my DFS part of the query. I kept everything same except the DFS preorder part of the query. I changed the DFS_Preorder part but I am not getting the correct result for the BFS part. Here is my query for the BFS:-
CREATE PROCEDURE BFS
@NodeID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    /*
        EXECUTE BFS;
    */

    DECLARE @v_ParentNodeID INT;
    DECLARE c_CUR CURSOR FAST_FORWARD LOCAL FOR
        SELECT ParentNodeID
            FROM GenericTree
        WHERE ISNULL(ParentNodeID, 0) = ISNULL(@NodeID, 0)
        ORDER BY ParentNodeID;
    OPEN c_CUR;
    FETCH FROM c_CUR INTO @v_ParentNodeID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @v_ParentNodeID;
        EXECUTE BFS @v_ParentNodeID;
        FETCH FROM c_CUR INTO @v_ParentNodeID;
    END;
    CLOSE c_CUR;
    DEALLOCATE c_CUR;
END;
GO

Any thoughts where I am doing wrong?


texts: How to return specific row number of a Hierachial query - Oracle

texts:
I have this query that works as expected. Within Oracle SQL Developer it returns ~19,500 rows.  Unfortunately our current application limits our result to 10,000.  I know I will need to run this query multiple times and merge the resultsets. But I need to be able to limit query results in blocks of 10,000.

Adding a Where clause before the Start statement only works for the first execution:

Where RowNum Between 1 and 10001 works as expected.

Where RowNum Between 10001 and 20001 returns no rows.

Based on my testing, limiting by Level will not work due to the number of number of children within each level.

Thanks for all your help!

Select WFS.WORKFLOWSTEPID

, WFS.STEPTYPE

, WFS.SUBWORKFLOWBASEID

, WFS.SUBWORKFLOWID

, WFS.Sequence

, WFS.WORKFLOWSTEPNAME

, WFS.Sequence ParentSEQ

, WFB.WorkflowBaseID

, WFB.WORKFLOWNAME

, WFS.WORKFLOWID

, Level

from WorkflowBase WFB

Inner Join Workflow WF on WFB.REVOFRCDID = WF.Workflowid

Inner Join WorkflowStep WFS on WF.WORKFLOWID = WFS.WorkflowID

Start With WFS.WORKFLOWID =  '48810e80000001ca'

Connect by Prior WFS.SubWorkflowBaseID = WFB.WorkflowBaseID

Order Siblings by ParentSeq, Sequence


texts: Advice on scripting a "whitewashed" and downsized development db from main db

texts:
Currently we (an active opensource project) have a large PostgreSQL production database which contains all the site records. Since we can't just allow every developer or contributor access to the database (containing emails, passwords, phone numbers, etc.) but we need to give the developers a somewhat up-to-date SQLite database. We currently do the following:

dump the database
analyze and change it with custom python scripts including truncating the table to make exporting to SQLite more bearable, remove sensitive data like passwords
export this to SQLite

This is very slow and error prone.

Is there a recommended/best practice approach way to do this, both the whitewashing and the table truncation, without breaking object relational mappings between tables?

As I analyze the problem domain I see the main problem with the object related mapping. I can't just pull the first thousand records from all the tables (with LIMIT) since an object in line 900, for example, in one table may map a foreign key to the 1001 line in another table. I guess changing and sanitizing data can be done with views, replacing certain columns for a calculated one (based on the original table column). Then a cron job could just dump the views.

I'll be glad for help/references.


texts: Update rows in table with id from row before in PostgreSQL

texts:
Say I have a table like this:

| id | grp   | time_added | previous_id |
|----|-------|------------|-------------|
| 1  | 1     | 5          | null        |
| 2  | 1     | 8          | null        |
| 3  | 2     | 9          | null        |
| 4  | 1     | 12         | null        |
| 5  | 2     | 15         | null        |

(time_added is actually a date, but numbers are used here for readability)

I want to update the previous_id of each row so that it's equal to the id of the previous row in the same group grp sorted by time_added.

This means that after running the update query, the table above should look like:

| id | grp   | time_added | previous_id |
|----|-------|------------|-------------|
| 1  | 1     | 5          | null        |
| 2  | 1     | 8          | 1           |
| 3  | 2     | 9          | null        |
| 4  | 1     | 12         | 2           |
| 5  | 2     | 15         | 3           |

What I have so far is the following query:

UPDATE the_table r
SET previous_id = sub.id
FROM (
  SELECT id, time_added, grp
  FROM the_table
  ORDER BY time_added DESC
) sub
WHERE (
  r.grp = sub.grp
  AND sub.time_added < r.time_added
);

However, I suspect the ORDER BY of the subquery does not do what I want it to, and the previous_id is set to the id of a random row below it, not the one strictly below it.

See this fiddle for an example of what happens. Row 3 is given a previous_id of 1, when it should be 2.


texts: How to grant user to dbms_crypto ?? Oracle (User which created inside SYSTEM)

texts:
Couldn't find user

I tried this

grant execute on sys.dbms_crypto to "DB_Project";

But error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

Also tried this:

grant execute on sys.dbms_crypto to SYSTEM."DB_Project";

ERROR: 00000 -  "SQL command not properly ended"

How to solve???


texts: Create ORACLE TRIGGER which (INSERT OR UPDATE) in TABLE1 whenever a new INSERT is done in TABLE2

texts:
i'm trying to create a TRIGGER for my Oracle database.
I Have 2 tables in it(table1 , table2)
Table 1 has 3 columns (id, name, value). id can be non unique here
Table 2 has 2 columns ( id, concat) id is unique here
What i want to do, is, every time a new row is inserted in Table 1., the trigger must Update the concat value of Table 2 which is a concate of name and values of all row where table1.id = table2.id
At the moment i think about this trigger, but i dont want to break anything in my db, so any advice would be great before testing :)

CREATE TRIGGER creation
AFTER INSERT  ON table1 ac
FOR EACH ROW
BEGIN
UPDATE table2
SET concat= CONCAT(' ',CONCAT(ac.NAME, CONCAT('=',ac.VALUE)))
WHERE
table1.id = table2.id
END

Thx a lot


texts: Effective SQL Server permissions when user is in several AD groups

texts:
Using Window Authentication via Active Directory Groups, if an Active Directory user exists in two different Active Directory Groups with different  permissions to a database, which Active Directory group does it use to set the permissions?

The most restrictive
The least restrictive
Cumulative of the two
Random

Setting this up in a test environment is proving difficult, because I am not a domain administrator, and we have no test domain. Plus we have seen results both ways. Just wondering if anybody knows the answer.


texts: Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

texts:
https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ignore the functions I created.

Is there a fairly easy way to reset the owner of ALL objects in the database, including the functions? Doing it by hand is highly undesirable.


texts: Should I upgrade compatibility level of MS Report Server database

texts: Recently upgraded from SQL Server 2008 to SQL Server 2012. The MS Report Server databases did not automatically upgrade compatibility level. Should I force the upgrade?


texts:
My query cache settings are the following:

mysql> SHOW GLOBAL STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2270     |
| Qcache_free_memory      | 6580864  |
| Qcache_hits             | 12802676 |
| Qcache_inserts          | 2094054  |
| Qcache_lowmem_prunes    | 111676   |
| Qcache_not_cached       | 137257   |
| Qcache_queries_in_cache | 7559     |
| Qcache_total_blocks     | 18240    |
| Queries                 | 15571718 |
| Questions               | 15571715 |
+-------------------------+----------+

As you can see, I have quite high in the value of Qcache_lowmem_prunes, but I still have quite some free memory (6MB out of total 32MB query cache size)

I am looking to see if anyway can further reduce the Qcache_lowmem_prunes.

Should I use a lower value of query_cache_min_res_unit? (Which is 4096 for now)


texts: Create pivot table from SQL query results

texts:
I need to convert the results from my SCCM 2012 query about user device affinity to show multiple columns per computer name.
Example of query results :-

Desired results format :-

Is it possible to create the desired table as part of the query, or do I need to manipulate the results as a second step?  There are 130,000 lines in my original query output representing approx 90,000 machines.

Hope somebody can help.


texts:
I'm troubleshooting an app that has a lot of sleeping connections to my MySQL (MariaDB) server. I'd like to see just what the queries are on these sleeping connections, but when I run SHOW FULL PROCESSLIST, the Info column holds only NULL.
I suspect theses connections represent long-running queries, but it's a sizeable app, and I'm not sure what parts are creating the offending queries.
Is there a way for me to discover what the queries are?


texts:
We have got a customized quarter list from client which we need to take reference to get data in SQL. Could you please help.

30-Oct  29-Jan AS Quarter1
30-Jan  30-Apr AS Quarter2
01-May  30-Jul AS Quarter3
31-Jul  29-Oct AS Quarter4

If today falls between any of the quarter we need start date in date format
for example if today is Jan/02/2018 then it shall return start date as 30-10-2017 00:00:00 and end date as 29-01-2018 00:00:00
if today is Dec/30/2017 then it shall return start date as 30-10-2017 00:00:00 and end date as 29-01-2018 00:00:00


texts: What is the SQL Azure equivalent of the server "public" role?

texts:
In our on-premise database, we create database users mapped to a login, and then assign the user to specific roles within the database.  Access to the database is strictly governed by which stored procedures each role has EXECUTE permissions on.  The user has no other rights other than by stored procedure grants (e.g. they cannot run arbitrary select statements in SSMS).

I notice that SQL Server (on-premise) provides a default user assignment to the server's "public" role.  When assigning user access to a SQL Azure server however, I do not see the "public" role.  However, I must select a role in order to create the user:

Can anyone please advise what I should do here to honour our existing approach, or whether the on-premise approach is even still possible?

I cannot find any documentation to describe what privileges are granted from the roles listed.


texts: How to set roles and users with correct permissions on Postgres?

texts:
My current database uat has as owner postgres. But now I would like to set roles and users with the correct permissions. Something simple like:

roles: dba, developer, application

users: dba1, dev1, dev2, app1

Where dba can do anything on the database for administration purposes; developer can makes SELECT, UPDATE and INSERT, usual developer operations; app1 the same operations as the developer role, for now.

How can I make this correctly ? And who should be the owner of my production table in this new setup ?

UPDATE:

------------------------
-- INITIAL SETUP
------------------------

-- create uat database
create database uat;

-- dba role
create role dba with superuser createdb createrole nologin replication bypassrls;

-- dev role
create role dev with nosuperuser nocreatedb nocreaterole nologin noreplication nobypassrls;

-- app role
create role app with nosuperuser nocreatedb nocreaterole nologin noreplication nobypassrls;

-- alter postgres password
alter role postgres encrypted password '';

-- create users

create user dev1 login inherit encrypted password '' in role dev;
create user dev2 login inherit encrypted password '' in role dev;
create user dev3 login inherit encrypted password '' in role dev;
create user dev4 login inherit encrypted password '' in role dev;

create user webapp encrypted password '' in role app;

-- grant privileges to dba role
grant all privileges on all tables in schema public to dba;

------------------------
-- RESTORE
------------------------
psql -U postgres -d uat -h localhost -W -p 5432 < uat.sql

But when I logged in as user dev1 for example, I can't do anything:

select * from guru_basket_security;

Listing access for this table:

    \z
                                                              Access privileges
     Schema |                         Name                         |   Type   |     Access privileges     | Column privileges | Policies
    --------+------------------------------------------------------+----------+---------------------------+-------------------+----------
     public | guru_basket_security                                 | table    |                           |                   |
     public | identifier                                           | table    | postgres=arwdDxt/postgres |                   |

I got these errors:

ERROR:  permission denied for relation guru_basket_security
ERROR:  permission denied for relation identifier


texts: What's the difference between equality (=) and inequality () on BIT field in SQL Server?

texts:
Is there difference between WHERE [BitField] = 1 and WHERE [BitField] <> 0?
They seem logically equivalent.
SQL Server, however, disagrees and creates slightly different execution plans. (Which is bad because the second form doesn't use existing indexes very effectively.)
Am I missing something, or it is just the way inequality operators behave?


texts: Fileshare witness for Multisite AvailabilityGroup setup

texts: If I am having a multisite AG with 2 nodes in the primary datacenter and 2 nodes in the DR - Where can I place the quorum for its continuous availability? I am assuming a 3rd site - but my management is not in favour of using Windows Server 2016. As of now, the only option I see is Windows Server 2012R2. If this is the case, what are my options?


texts: Can we move SQL Server installed bit from default location to other drive

texts:
My client gave me command line installer for SQL Server. Since it is command line there is no option to select the installation path! So installer automatically chooses the default location C:\Program Files (x86) drive for installation.

I would like to move all the installed bit from C:\Program Files (x86) drive to to other drive. Can I cut and paste the SQL folders from drive and map the new location in Management Studio?

The below link says that SQL Server is dependent on C: drive.
Installing SQL Server on not the default path

Requirement: I am using Azure VMs and attached extra drives. I want to make sure my SQL Server to be stable in case OS drive get corrupted.


texts: Always On Availability Group Failover Issue

texts:
I'm testing a PowerShell Disaster Recovery script and trying to fail over to an off-site AG DR (asynch) replica with this command

ALTER AVAILABILITY GROUP [MyAG] FORCE_FAILOVER_ALLOW_DATA_LOSS

On several clusters this works fine but on a couple of other ones it usually fails (but not always) with the error:

  Failed to move a Windows Server Failover Clustering (WSFC) group to
  the local node (Error code 5023).  The WSFC service may not be running
  or may not be accessible in its current state, or the specified
  cluster group or node handle is invalid.  For information about this
  error code, see "System Error Codes" in the Windows Development
  documentation.

  Failed to designate the local availability replica of
  availability group 'MyAG' as the primary replica.  The operation
  encountered SQL Server error 41018 and has been terminated.  Check the
  preceding error and the SQL Server error log for more details about
  the error and corrective actions.

We have to force the quorum simulating the situation where the async DR replica can't communicate with the other replicas: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/force-a-wsfc-cluster-to-start-without-a-quorum?view=sql-server-2017

A comparison of the clusters that work vs. those that don't with Get-Cluster and Get-ClusterGroup doesn't reveal any major differences.

This failover has worked successfully on some clusters but fails 99% of the time on two of the clusters. It has worked on these two clusters, but rarely. One of the common issues I found from a Google search is permissions for NT Authority\System (see https://dataginger.com/2014/10/28/sql-server-failed-to-bring-availability-group-availability-group-name-online/), which I confirmed it has.

I ran a trace and see the same errors and no other clues in the trace.

If nothing turns up I may have to open a Microsoft ticket to engage their support.

Any thoughts?

Thanks for your help!


texts: MYSQL query to find records between two date range

texts:
I am working in a web application using PHP and MySQL.

I have two tables

Employee

employee_id     email_id
3             tl1@jasmine.com
4             agent1@jasmine.com
5             agent2@jasmine.com
6             om1@jasmine.com
7             tl2@jasmine.com

Coaching

coaching_id     emp_id  start_date  end_date
 1               4      2014-05-01  2014-10-02
 2               5      2014-12-18  2015-01-22

I want a query to find out the employees who are not enrolled for any coaching program between a specified date range such as Start Date "2015-01-22" and End Date "2015-03-12".

Now I am using query like this,

SELECT employee_id
FROM employee
WHERE sup_id = 3
AND employee_id NOT IN
(
    SELECT emp_id
    FROM (coaching)
    WHERE (start_date NOT BETWEEN "2014-12-26" AND "2015-01-30")
    AND (end_date NOT BETWEEN "2014-12-26" AND "2015-01-30")

)

I have a logic, " If any employee already joined a coaching program at given start_date and end_date, he is not eligible for any coaching program.

For Example : If the start date is '2014-12-26' and end date is '2015-01-30'. Then the employee with id 5 is not eligible for this coaching program as he is now on another coaching activity whose start date is '2014-12-18' and end date is '2015-01-22'. But employee with id 4 is eligible as well as all other employees who are not in any coaching activity

Where is the issue in my query

Thanks


texts: Setting up DRBD on an active MySQL server

texts:
When it comes to setting up DRBD and MySQL, is the following possible?

Set up DRBD on an active MySQL server
Set up DRBD with no downtime allowed


texts: ALTER USER WITH LOGIN throws error "Windows NT user or group not found"

texts:
Attempting to deprecate the procedure sp_change_users_login as recommended by MS. I tried using:

ALTER USER UserName WITH LOGIN = UserName

as demonstrated here.  This results in the error

  Msg 15401, Level 16, State 2, Line 3458
  Windows NT user or group 'UserName' not found. Check the name again.

UserName is both the user and the SQL login.  Surely I am missing something simple but I cannot see what it is.

I had 3 names that failed and one does not have a login, but the other 2 do have logins.  I can see the login and users in the Object Explorer.  However if I open the login properties the user is not assigned to the target database.  If I manually check the db box it works, but the point is that I am trying to clear up any orphaned users in my target database using SQL.


texts:
I was trying to learn querystore. When going through few queries involved in query store one of the query is below

DELETE TOP (1000000)
FROM [sys].[memory_optimized_history_table_1179151246]
 WITH (SNAPSHOT)

Full query:

(@rowcount INT OUTPUT) DELETE TOP (1000000) FROM [sys].[memory_optimized_history_table_1179151246] WITH (SNAPSHOT)
OUTPUT
DELETED.[ColdRoomTemperatureID], DELETED.[ColdRoomSensorNumber], DELETED.[RecordedWhen], DELETED.[Temperature], DELETED.[ValidFrom], DELETED.[ValidTo]
 INTO [Warehouse].[ColdRoomTemperatures_Archive]
 WHERE [ValidTo] < GetHkOldestTxTemporalTs()

I searched a lot to see what the WITH (SNAPSHOT) option means, but I was not able to find anything.

Can you please help me in understanding what does WITH (SNAPSHOT) mean?

Let me know ,if you need any further info.


texts: SQL Server: How to add all dates of the month?

texts:
I need help. I am trying to add all dates for the month. I have this information:

Month | Year
=============
  1   | 2012
  2   | 2012
  3   | 2012
  4   | 2012

I need to add all dates of the month (for example: 01/01/2012 to 31/01/2012).

I do not have any idea how to add all dates. Please, any help is very useful.

Edited

I resolved my problem using a cursor, the cursor runs the records in the table "MontsOfYear" and with each register obtains the last day of the month (Example: Month = 1 and Year = 2015) that obtain the last day to January (31), next is iterate between the first day to the last day of the month.

This is the code:

DECLARE db_Cursor CURSOR FOR SELECT Years,Months FROM MontsOfYear;
DECLARE @Years INT, @Months INT, @LasDate date, @DatePart date, @Flag int;

OPEN db_Cursor;

FETCH NEXT FROM db_Cursor INTO @Ano, @Mes
WHILE @@fetch_status = 0
BEGIN

SET @LasDate = (SELECT (DATEADD(MONTH, DATEDIFF(MONTH, 0,       DATEFROMPARTS(@Years,@Months,1)) + 1, 0) - 1));
SET @DatePart = DATEFROMPARTS(@Ano,@Mes,1);
SET @Flag = 1;

    WHILE @DatePart < @LasDate
        BEGIN
            SET @DatePart = (DATEFROMPARTS(@Ano,@Mes,@Flag));
            INSERT INTO Days VALUES(@Ano,@Mes,@DatePart);
            SET @Flag = @Flag + 1;

        END;
FETCH NEXT FROM db_cursor INTO @Ano, @Mes;
END;
CLOSE db_Cursor;
DEALLOCATE db_Cursor;


texts: MySql is not optimizing the query properly

texts:
I have a table structure as follows :

CREATE TABLE `sale_product_inventories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `size` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tier_number` int(11) NOT NULL DEFAULT '1',
  `sale_product_pool_id` int(11) DEFAULT NULL,
  `inventory` int(11) NOT NULL,
  `in_cart_units` int(11) DEFAULT '0',
  `size_display_order` tinyint(4) NOT NULL DEFAULT '0',
  `last_updated_by` int(11) DEFAULT '0',
  `created_by` int(11) DEFAULT '0',
  `status` enum('active','inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE` (`sale_id`,`product_id`,`tier_number`,`size`,`sale_product_pool_id`)
) ENGINE=InnoDB AUTO_INCREMENT=92872 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

NOTE : I have an Index UNIQUE = sale_id,product_id,tier_number,size,sale_product_pool_id

When I run this query :

select * from sale_product_inventories
where
sale_id in (502,504)  and
(sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) )

MySql Uses the index Unique and the execution time is 0.7 millisecond

BUT

when I run this query

select * from sale_product_inventories
where
(sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) )

MySql does not use the UNIQUE index and the execution time is 76 millisecond.

Mysql : 5.5.27 InnoDB Version : 1.1.8

My Question is Why is mysql behaving in such a way. Can some one please help me with this.

EDIT :
I came across this so thought it might be useful to add
MySQL generally can’t use indexes on columns unless the columns are isolated in the query. “Isolating” the column means it should not be part of an expression or be inside a function in the query.


texts: Looking for a recipe to merge multiple rows to single row

texts:
I have a aggregation table that is written by multiple processors (around 20).
Each row has a composite key - location-id, campaign-id,... and aggregate stats about it.

I need to merge the aggregation rows to single line per key. Now it has 10 lines per key because each processor write its own data in order to avoid locking in the database.

I dont care to write it to new table or to merge it in this table. i prefer to merge it in this table so i will not need to read from multiple tables.

Some info abut the table:
Events are written to table all the time in heigh rate.
We write 1 million row per day (merge them will decrease it to 50K rows per day).

Options:

Trigger to a new table - can slowdown the insert rate to the report table and we cant slow down the insert rate.
An hour procedure that will update an aggregation table - how to do the merge ? read aggregation of all day ? and delete this day from the aggregation table ? long read while this data still updated in the report table will create lots of versioning on the report rows.
Materialized view.
Any other option ... ?


texts: Is it more efficient to union from sub-query or combine two outer queries?

texts:
I searched for this but couldn't find the response I needed.  So my apologies if this has already been asked.

I'm asking in regards to DB2 but I suppose this would apply, generally speaking, to just about any database.

Anyway, say I have two tables in the same database:

select ID, EMAIL from TBL1
select ID, EMAIL from TBL2

What I then want to do is search for a field within those two tables.  The field could be in either table.

So, I have tried the following:

select ID, EMAIL from TBL1 where EMAIL = 'blah@aol.com'
union
select ID, EMAIL from TBL2 where EMAIL = 'blah@aol.com'

This works.  But is redundant and ugly.

However, is it more efficient than:

select T.* from
(
    select ID, EMAIL from TBL1
    union
    select ID, EMAIL from TBL2
) T where T.EMAIL = 'blah@aol.com'

Both seem to work.  Unfortunately, I don't have any real profiling tools for DB2 (along with restricted access) so I can't seem to figure out which is better.  They both execute at roughly the same speed in the console but that isn't a good indicator.

Any tips on this (or even how to maybe profile it myself) would be appreciated.

Of course, this is a highly simplified example.  The real queries are quite massive and span 9-18 tables, databases, etc.

Thanks

EDIT

The DB2 is on iSeries and I have no idea what version it is.  Everything I try to use gives an error.  I want to say it's version 8 but I really don't know.

If anyone wants to clue me in on how to give more information I will.  I don't know much about DB2.


texts: How do I write portable SQL that refers to a linked server?

texts:
I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following:

INSERT INTO [TableName]
(...Columns...)
SELECT ...Columns...
FROM [ServerName\InstanceName].[Catalogue].[dbo].[TableName]
WHERE TableNameID = @TableNameID

This procedure exists in my Development environment, Test environment and Live environment.

The problem is that each copy of the procedure is subtly different because the server names are different for each environment. This makes managing deployment of script updates troublesome.

Is there a way to make the procedure portable so that each environment can run identical versions of it?

If not, is there anything I can do to make script deployment less prone to mistakes/errors?


texts: Is it wise to drop fixed-length columns to free up space?

texts:
I have a huge table in SQL Server 2005. (The table is about 40gb)  I am looking for the safest and most pain-free way to either free up space, or delay its growth.

The table has three fixed length (char(10)) columns that are a) completely unneeded and b) almost totally filled with NULL.

If I drop these columns will SQL Server start using the freed space within the data file when new records are imported?  Or does their fragmented nature (I assume) because of being spread out over many records mean that SQL Server will ignore the space for performance reasons?

If I do drop them, can I run shrink database to free up the space?

I'm the only developer who utilizes this database, and I know these columns are surplus to requirements.  They were added along with every other column in a flat file 15 years ago when different lazy developers created this database.


texts:
I am new to database learning and came across this \ symbol. I googled but couldn't find what it meant.

It was used in the context Q = R\S, where Q, R, and S are relations.

(The = sign here means "is equivalent to").


texts: Always On HAG Primary Database Log Files

texts: I tried to search the group but can't find similar answer,  I have a Primary Database that has a Large Transaction Log, I tried to shrink the file in the primary but can't set the default size to a smaller size, I believed I need to change the recovery model to simple to shrink the file, and now I'm not sure what will happen because the database is part of HAG/Always on and it is the primary node. I tried the readable secondary set = no, nothing changes, I can't managed to resize the default initial size to a lower size.


texts: Index not used in a remote query when values are supplied from a PL/SQL collection

texts:
I have a table with primary key column on a remote database:
create table tab (
  id primary key,
  pad
) nologging as
select level id, lpad('x', 800) pad
from dual
connect by level <= 100000;

If I query a few rows from remote table by id, over db link:
create type id_list is table of number;

declare
    type tab_list is table of u.tab@dblink%rowtype;

    v_result tab_list;
    v_id_list id_list := id_list(10003, 10004, 10005);
begin
    select *
    bulk collect into v_result
    from u.tab@dblink
    where id in (select value(i) from table(v_id_list) i);
end;

This simple query takes around 3 seconds to complete. This time is linearly dependent on the number of values in v_id_list collection. Each additional id increases query time by about ~1s.
Query plan on local database is:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |      1 |        |      3 |00:00:01.68 |       |       |          |
|   1 |  NESTED LOOPS                       |      |      1 |      1 |      3 |00:00:01.68 |       |       |          |
|   2 |   SORT UNIQUE                       |      |      1 |      3 |      3 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
|   3 |    COLLECTION ITERATOR PICKLER FETCH|      |      1 |      3 |      3 |00:00:00.01 |       |       |          |
|*  4 |   FILTER                            |      |      3 |      1 |      3 |00:00:02.69 |       |       |          |
|   5 |    REMOTE                           | TAB  |      3 |        |    300K|00:00:03.45 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

"   4 - filter(""ID""=VALUE(KOKBF$))"

The remote query was executed 3 times for a total of 300k rows (3 full table scans). Similarly, querying v$sql on
remote database reveals that the following query was executed 3 times:
SELECT /*+ */ "ID","PAD" FROM "U"."TAB" "TAB"
Why is the index not used in this case?
I verified, that the index is used correctly when values are supplied in pure SQL, even from another table:
create table ids nologging as
select 10003 id from dual union all
select 10004 from dual union all
select 10005 from dual;

select *
from u.tab@dblink
where id in (select id from ids);

I also checked that:

statistics were refreshed,
driving_site hint does not help.

I managed to get Oracle to use index eventually by extracting collection to subquery and adding materialize hint:
with ids AS (
  select /*+ materialize */ value(i)
  from table(v_id_list) i
)
select *
bulk collect into v_result
from u.tab@dblink
where id in (select i.id from ids i);

Is there a better way to tell optimizer to use index, without using explicit hints?
Tested on Oracle 12.1.0.2


texts:
I've done some reading on this, and I keep seeing people say it's a bad idea, but hear me out!
I have a stored procedure which needs to find a series of timeslots over a number of days. Due to the fact that the timeslot count is the same for every date possible, they aren't duplicated for every date. Instead, it is recorded which timeslots are taken on what date. Those timeslots are then excluded from the query.
Due to the way the data is stored, I'm having to use a while loop to start on a given date, and query for a given number of days after. However, this is causing the return of multiple result sets. This would be OK, but the procedure will be called by Entity Framework, which does not support multiple result sets returning.
I've thought about storing it all in a temp table, but that option is out of the window due to a people problem: my dba is on holiday and I don't have an account with sufficient privileges to drop temp tables (or so it appears), and this needs doing before he'll get back.
Is there any way I can amalgamate all these result sets in to one? Every result set has the same structure.
EDIT TO ADD TABLE STRUCTURE (PKs in italics, becasue I can't find how to underline)
TimeSlotGroup
UKTNN, TimeSlotGroupID, TimeSlotGroupDesc
TimeSlotGroupTimeSlots
UKTNN, TimeSlotID, TimeSlotGroupID, TimeSlotDesc
BookedDates
UKTNN, TimeSlotGroupID, TimeSlotID, Date
I'm returning currently:
ResultSet
UKTNN, TimeSlotGroupID, TimeSlotID, TimeSlotGroupDesc, TimeSlotDesc, Date
It has duplicated data in it, but I'm OK with that right now
My existing query:
WHILE(@LoopDate <= DATEADD(DAY, @NumDays, @ArrivalDate))
    BEGIN
    SELECT TSG.TimeSlotGroupID,
           TSG.TimeSlotGroupDesc,
           TS.TimeSlotID,
           TS.TimeSlotDescription,
           TS.AdditionalCost,
           @LoopDate Date,
           @UKTNN UKTNN
    FROM FittingCentreBranchTimeSlotGroups TSG, FittingCentreTimeSlotGroupTimeSlots TS
    WHERE TSG.UKTNN = @UKTNN AND
          TS.UKTNN = @UKTNN AND
          TS.TimeSlotGroupID = TSG.TimeSlotGroupID AND
          NOT EXISTS(
                SELECT *
                FROM BookedFittingDates BFD
                WHERE BFD.TimeSlotGroupID = TS.TimeSlotGroupID AND
                      BFD.TimeSlotID = TS.TimeSlotID AND
                      BFD.UKTNN = @UKTNN AND
                      BFD.Date = @LoopDate)
    ORDER BY TimeSlotGroupID ASC, TS.TimeSlotID ASC

    SET @LoopDate = DATEADD(DAY, 1, @LoopDate)
END


texts: Identify Duplicates in MySQL across columns

texts:
I have a table with name, contact1, contact2 and contact3 columns. Is there a way to find out of a given number exists in either of the 3 contact columns?

I am looking identify if a number exists more than once across contact1, contact2 and contact3 columns.

Please help.


texts: How to name table-variable function unique constraint?

texts:
I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as follows:

CREATE FUNCTION [dbo].[fn_name] (...)
RETURNS @Result
TABLE
(
    ID BIGINT PRIMARY KEY,
    ...
    RowNum BIGINT UNIQUE
)
BEGIN
    ...
    RETURN
END
GO

I have try to name it like this, but is not working:

CREATE FUNCTION [dbo].[fn_name] (...)
RETURNS @Result
TABLE
(
    ID BIGINT PRIMARY KEY,
    ...
    RowNum BIGINT
    ,CONSTRAINT UC_fn_name_RowNum UNIQUE([RowNum])
)
BEGIN
    ...
    RETURN
END
GO

Is it possible to set the name of a unique constraint when it is part of table variable function definition?


texts:
We're setting up streaming replication with one master and one (read-only) slave. We're now looking for a way to monitor the replication; mostly to verify that the slave server is still up-to-date.

Is it possible to do this — preferably from the slave? If so, how?

The documentation on streaming replication mentions the following:

  On systems that support the keepalive socket option, setting tcp_keepalives_idle, tcp_keepalives_interval and tcp_keepalives_count helps the primary promptly notice a broken connection.

What does "promptly notice" mean in this context?  Is there some error log where a broken connection gets reported?


texts: Filter predicates executing before Access predicates

texts:
This is oracle 11g. When I try to do a select query on a table, in the explain plan option I see that the filter predicates of the query(predicates on column without indexes) are executed before the Index access predicate. I don't understand why the query optimizer is picking this plan?

I tried the --+ ordered_predicates option, but it ignored that.

Filter predicates are just as horrible as full table scans right?

Edit Query and the plan:

select column1, column2, column3 from table where column1 >= :cutoff and trunc(column2) = trunc(sysdate-1) and column3 = :column_value

The index is defined on column 1.

This is the query plan:

Plan hash value: 4098448107

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                       |  1619K|    49M|  4433K  (1)| 00:02:54 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_NAME            |  1619K|    49M|  4433K  (1)| 00:02:54 |
|*  2 |   INDEX RANGE SCAN                  | NAME_OF_THE_INDEX     |    87M|       |   648K  (1)| 00:00:26 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TABLE_NAME"."column3"=:column_value AND TRUNC(INTERNAL_FUNCTION("TABLE_NAME"."column2"))=TRUNC(SYSDA
              TE@!-1))
   2 - access("TABLE_NAME"."column1">=TO_NUMBER(:CUTOFF) AND "TABLE_NAME"."column1" IS NOT NULL)


texts: How to find out the content differences between 2 SQL tables and produce sync SQL

texts:
How do I find out the differences in data between the two tables that have exact schema, and how to produce synchronisation SQL to get the union results (without duplicates) ?

These are the 2 tables:

SOURCE01.dbo.Customers (31,022 rows)

TARGET01.dbo.Customers (29,300 rows)

The schema of each table is :

[CustomerId] : nvarchar(255)
[CustomerSerializedProfile]: nvarchar(max)
[CreatedDatetime] : DateTime


texts: ERROR 1031 (HY000) at line X: Table storage engine for 'TBL' doesn't have this option

texts:
I am trying to import mysqldump into following system:

# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
# rpm -qa | grep ^mysql
mysql-community-libs-compat-5.7.11-1.el7.x86_64
mysql-community-libs-5.7.11-1.el7.x86_64
mysql57-community-release-el7-7.noarch
mysql-community-client-5.7.11-1.el7.x86_64
mysqltuner-1.6.0-1.el7.noarch
mysql-community-server-5.7.11-1.el7.x86_64
mysql-community-common-5.7.11-1.el7.x86_64
#

yet, I getting following error:

# mysql X < ~/X.mysqldump
ERROR 1031 (HY000) at line 13639: Table storage engine for 'Y' doesn't have this option
#

the Y table from mysqldump: (I replaced sensitive information with Zs):

DROP TABLE IF EXISTS `Y`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Y` (
  `Z` int(10) NOT NULL AUTO_INCREMENT,
  `Z` int(10) NOT NULL DEFAULT '0',
  `Z` int(10) NOT NULL DEFAULT '0',
  `Z` tinyint(1) NOT NULL DEFAULT '0',
  `Z` tinyint(1) NOT NULL DEFAULT '1',
  `Z` int(10) NOT NULL DEFAULT '0',
  `Z` datetime DEFAULT NULL,
  `Z` datetime DEFAULT NULL,
  PRIMARY KEY (`Z`),
  KEY `Z` (`Z`),
  KEY `Z` (`Z`),
  KEY `Z` (`Z`),
  KEY `Z` (`Z`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED COMMENT='InnoDB free: 8192 kB; (`Z`) REFER `Z/';
/*!40101 SET character_set_client = @saved_cs_client */;

/etc/my.cnf is all defaults, I did however added following line(s):

# tail -1 /etc/my.cnf
innodb_buffer_pool_size = 64G
#

Also, here is full mysql> SHOW VARIABLES LIKE 'innodb%'; stack.

I'm going over MySQL :: MySQL 5.7 Reference Manual :: 14.4 InnoDB Configuration, but obviously I'm missing something...

Please advise.

Thanks!


texts: How to properly monitor the number of PostgreSQL database connections?

texts:
I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 minutes.

SELECT sum(numbackends) FROM pg_stat_database;

Still, this seems to miss a huge number of short-lived connections, so the statistics are far from the reality.

I tried to run the script manually and I observed big changes even between two connections made few seconds away one from another.

How could I get this information in a reliable way? like max(connectios) happened during a time interval.


texts: Can I alter ENUM value from lowercase to studlyCase without reindex?

texts:
I have a several big tables, and enum in each of them (example):

ENUM ('webmasters', 'stackexchange', 'stackoverflow')

Documenation says, that return value is in lettercase of ENUM definition, so I have a problem now, because I need return values in studlyCase:

ENUM ('webMasters', 'stackExchange', 'stackOverflow')

Actually I need to change only one value from big list of defined enum values, also there is yet no data in the tables with this value. And if it is matter - the value is almost in the end of enum definition.

The tables are very big - tens millions of data. So here is my question - Can I alter the tables so the index wil not be recalculated?