texts:
Hello all I'm having 1 Question with following query syntax

 ALTER TABLE table_name
 ADD column_name column-definition;

Question is can we use parameters to column_name field like @column_name? Because I'm creating columns which Column name is supplied by front end as a parameter. for example

ALTER TABLE table_name
ADD @column_name varchar(250);

this is giving error so any alternate for this?


texts:
I am newbie with MongoDB and even more in administration of databases. But my boss passed me an issue to solve.

SCENARIO

I have two servers using MongoDB and ReplicaSet, when I run the command

db.adminCommand( { replSetGetStatus : 1 } )

on the primary server I get the result below:

{
    ...
    "date" : ISODate("2018-12-17T10:39:00.247Z"),
    "myState" : 1,
    ...
}

and on the secondary server I get the result below:

{
    ...
    "date" : ISODate("2018-12-17T10:39:00.247Z"),
    "myState" : 2,
    ...
}

as expected.

QUESTIONS

If I stop the network interface (eth0) of the primary server to activate ReplicaSet, should the myStatus attribute of the secondary server change to 1?
If the status is to change, I have already tested it and it is not changing. How do I know that the secondary has become primary?


texts: Can I speed up this slow query which should be limited by MIN/MAX values?

texts:
The intention here is to look at the user retention over a period of time.

My current solution employs a temporary table, since I do this over several ranges.

CREATE TEMPORARY TABLE user_retention_temp (first DATE, last DATE, amount INT);

INSERT INTO user_retention_temp
(SELECT MIN(ymd) AS first, MAX(ymd) AS last, player_id FROM day_item GROUP BY player_id);

Here I would have liked to limit this query to only match player_id's that has a first/last that's in a specific period of time.

Instead I'm force to do that for the consequent query:

SELECT LEAST(last - first, 7) AS diff, count(*) AS amount
FROM user_retention_temp
WHERE first >= ? AND last < ? GROUP BY diff

This is pretty awful since I'm filling up the temporary table with lots of data I don't need.

Is there a faster way? Assume at least 2-3 million entries in day_item and that everything we need is indexed.


texts: Study material for Advanced Databases module inc SQL, Relational Model, ERM & TM

texts:
For my next module in my masters, I am planning on studying Advanced Databases. (Long story short, the masters is designed for people with NON-CS degrees, so I have never studied databases, therefore I have literally 0 knowledge).

The lecturer mentioned that I must have an understanding on the following concepts:

The relational Model;
Relational Algebra (and maybe Relational Calculus);
SQL;
Entity-Relationship Modelling (ERM) and Enhanced ERM (we are using UML notations);
Transactions Management;

I'm aware that questions have been asked about study resources on the subjects, particularly SQL, but, does anyone some good resource that covers a few of the subjects mentioned? Preferably online sources (so they will keep up to date).


texts: How fast can I pull data through a SQL Server client connection?

texts:
What is the highest throughput possible? Has anyone seen anything above 25MB/sec?

I've been trying to figure out what is even possible.

I know there are tuning parameters like packet size, and rows per batch and so one, plus there is overhead involved if going over TCP.
I can copy a file at sustained 250MB/sec.
I haven't found any way to pull > 16MB/sec or so through a query.

I got bcp to do about 25MB/sec by saving to a flat file. But no luck on a query that is simply selecting from a large table (53GB).
I've been experimenting on Google Compute instances with various versions of SQL Server, using maxed out machines and SSDs etc. (32 cores, 200GB mem, and so on)

Is there some theoretical maximum SQL Server can handle on one connection? 25MB/sec seems stupidly slow.


texts: Run SQL Job monthly at day 16. If it's a weekend, run it in the next business day ( dont care if is a holiday)

texts:
I need to run a job every day 16. But if it is "saturday" or "sunday" it must run in the next business day.
I don't care if it is holidays or not. I just need to run it.
I could do something like this and separete it on steps, and only pass to the next step if it runs with no error:
--Step 1 - Verify if it is 16
IF  day(getdate()) <> 16
    RAISERROR ('Job runs only day 16.',16,1)

--Step 2 - verify if its saturday of sunday
IF  DATEPART(weekday,getdate()) IN ( 7,1 )
    RAISERROR ('Job will run on the next business day.',16,1)

I would create 2 steps with these queries, but this is not going to work since, lets say the next business day is 18, the first step will raise an error.
Is there a way to schedule something like this?


texts: Update existing rows with squence number/char or any unique data

texts:
I added a new column in the Table X

This column "cn" has to be unique and mandatory, but old data don't have any value.

How to update the existing records with sequecely or random unique data?

Thank you.


texts: Password column from sys.user$ is null in 18c, why?

texts:
Why the password column from sys.user$ is null? My version is 18c. I've made a test.
SQL> alter user harry identified by 12345678;

User altered.

SQL> select name,password from sys.user$ where name = 'HARRY';

NAME                           PASSWORD
------------------------------ ----------------------------------------
HARRY

SQL>

I'd like to use the password from harry in another user with
alter user some_user identified by values 'hash_here'


texts: Persistence of a PostgreSQL visibility map through RDS snapshot and restore

texts:
I have a pretty huge (1,2 TB) DB on a big RDS PostgreSQL 9.6.1 instance (db.m4.4xlarge). The DB is static (no on-line transactions) and we are testing performance of scripts (mainly analytical queries).

The DB was built from a plaintext SQL scripts backup (CREATE TABLE + COPY), so it has no dead tuples. After the restore, I ran just a complete ANALYZE (not a VACUUM).

After the ANALYZE, I saved the DB in a snapshot and deleted the instance.
I would like to start often from that snapshot as a known and stable test bed.

I noticed that the first execution of scripts (e.g. select count(*) from table) after the restore from that snapshot is way too long (i.e. 50min for count(*) of a 116 mln rows table). The subsequent execution of the same query lasts around 15 sec.

It seems to me that 50 minutes can't be explained by the reading of table from SSD instead of RAM cache.

I discovered that visibility map of all tables after the restore+analyze+snapshot wasn't compiled  (SELECT ... relpages, relallvisible FROM pg_class...), and the first execution of the query had to do the job of compiling it.

So I ran a complete VACUUM ANALYZE. I saw the performance improvement in the query and took a new snapshot, aiming to save a better test bed (I haven't found documentation about persistence of visibility map in PostgreSQL backups or in RDS snapshots).

Unfortunately I discovered that, after taking snapshot and restoring from it, first execution is still long (no improvement). Visibility map seems to be lost, despite the (SELECT ... relpages, relallvisible FROM pg_class...) showing good values for relallvisible.

Are my findings wrong?

There is a way to have persistence of the visibility map through a RDS snapshot and restore?

There is a documentation to study useful for this aim?


texts: Net Configuration Assistant doesn't open, created Listener in Net Manager but it isn't recognized when creating database Win 7

texts:
I have installed Oracle 11g on my Windows 7 64-bit system. The Net Configuration Assistant does not open for me to create a listener. I was able to add a listener through the Net Manger and it says it is configured correctly. But when I create a database it doesn't recognize the listener so I have to uncheck "Configure Enterprise Manager" in order to complete the creation of my database.

Is there another way to access the Net Configuration Assistant other than through the start menu so that the listener will be recognized by the database?


texts: When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

texts:
The manual for Postgres 12 says:

  The following operations are always parallel restricted.

  Scans of common table expressions (CTEs).
  Scans of temporary tables.
  ...

Further down on the same manual page:

  [...] Similarly, functions must be marked PARALLEL RESTRICTED if they access
  temporary tables, client connection state, cursors, prepared
  statements, or miscellaneous backend-local state which the system
  cannot synchronize across workers. For example, setseed and random are
  parallel restricted for this last reason.

No mention of CTEs. Now I am unsure whether I can use PARALLEL SAFE for functions containing a CTE. It would make sense to me for those to be only PARALLEL RESTRICTED.

Context: I have to determine the best label for existing user-defined functions. The setting is new since Postgres 9.6, and can have a massive impact on performance as operations involving functions that are not PARALLEL SAFE will not be performed by parallel workers, PARALLEL RESTRICTED only by the leader. (And PARALLEL USAFE disables parallelism altogether.)

I posted a related question on pgsql-general.


texts: create_index_cost used_bytes vs alloc_bytes

texts:
I'm reading a lot about create_index_cost, but I can't seem to find out why there is such a discrepancy between the size of used_bytes and alloc_bytes. I know what each parameter means, but why is alloc_bytes so much bigger?

Thank you!
robotron


texts: Is there a way to make SQL Server's Database Tuning Advisor use more than one core?

texts:
I'm using SQL Server's Database Engine Tuning Advisor to generate recommendations for performance improvements on my database server.

For the "consuming workload" step, it appears to use all available cores. However, for the much more time consuming "performing analysis" step, only one core appears to be utilized.

Is there a way to get it to use multiple cores for this step?


texts:
I'm trying to create a relationship between two tables where one column in TABLE A is the foreign key for a column in TABLE B. However, there is one row in TABLE B that is currently null in that column and it's giving me an error. However, from Googling the issue, the general consensus seems to be that null is an acceptable value in a foreign key field. But I'm still unable to create this relationship in SSMS due to the error message. How can I bypass this?

Error:

Unable to create relationship 'FK_RELATIONSHIP'.
The ALTER TABLE statement conflicted with the FOREIGN KEY
constraint "FK_RELATIONSHIP". The conflict occurred in database "DATABASE",
table "TABLE A", column 'COLUMN 1'.

SQL Code as requested:

USE [DATABASE]
GO
/****** Object:  Table [dbo].[TABLEA]    Script Date: 10/02/2012 17:44:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TABLEA](
[COLUMN1] [nvarchar](4) NOT NULL,
CONSTRAINT [PK_TABLEA] PRIMARY KEY CLUSTERED
(
[COLUMN1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TABLEB]    Script Date: 10/02/2012 17:44:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TABLEB](
[COLUMN1] [nvarchar](15) NOT NULL,
[COLUMN2] [nvarchar](4) NULL,
 CONSTRAINT [PK_TABLEB] PRIMARY KEY CLUSTERED
(
[COLUMN1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,   ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Thanks


texts: SQL Server 2016 Agent jobs start, but hang indefinitely on first run after a server bounce

texts:
I have multiple SQL Server Agent jobs that hang indefinitely upon first run after the server bounces. The jobs execute as scheduled, but never perform any action and stay in "Executing" status indefinitely. Rebooting the Agent from the SQL Server Configuration GUI resolves the issue.

NOTES:

Neither the Engine logs nor the Agent logs seem to show anything of value.
I have tried setting the Agent Service to "delayed start" in Windows Services.
The Agent is currently using an MSA.


texts: How do I check if a constraint exists on Firebird?

texts:
I'm about to publish a script which will update a lot of Firebird databases all at once. Some will not have this constraint, so I would like to check for the existence of a constraint before I try to drop it.

ALTER TABLE PROCESS_CATEGORY DROP CONSTRAINT INTEG_669;


texts:
We are planning on installing SSIS 2016 on a new dedicated ETL server.

The packages will be called from SQL Server Agent (on a separate server), or from an application (again on a separate server).

From reading the Microsoft docs, it seems that we need to install Database Engine along with Integration Services on the dedicated SSIS server. Is Database Engine required on the SSIS server for our needs?

I'm confused by this: "If the ETL server does not have an instance of the Database Engine, you have to schedule or run packages from a server that does have an instance of the Database Engine". Does this also mean that the SSIS server is then redundant if run from a server that does have DB Engine?


texts: Problem structuring database for an assessment rubric app

texts:
Hello everyone, I'm really troubled about how to design a database and I could use some help. I've done my research and found nothing about it.

I'm trying to build a simple app for my class that creates academic assessment rubrics so they are available to use them on my students and finally get grades.

I thought of using mysql for my database. The student part is done. But I can not think of a way to make the rubrics thing work.

I need to be able to create rubrics and inside that rubric create criterias, sub-criterias, sub-sub-criterias, levels and points. Here's and example of a rubric.

Could anyone give me some suggestion about how to model this database or any idea about it? Is a relational database a good approach for this problem? I'm kinda burnt with this and would appreciate
any recommendation.

Sorry for my English, not my native.
Regards.


texts:
I'm using Tsql script to restore several databases.

RESTORE DATABASE XXX_M
   FROM DISK = 'C:\XXX.bak'
   WITH FILE = 1, replace
GO

Sometimes the .bak file comes corrupted (not data corrupted ).

I want to know if there is a way to check if the file is not corrupted then restore else don't.
I've tried to use @@rowcount after restore VERIFYONLY but it's not good.

RESTORE VERIFYONLY
   FROM DISK = 'C:\xxx.bak'
if @@ROWCOUNT !=0
print 'false'
else
print 'good'

Any other ideas?

Thanks.


texts:
I'm trying to count multiple columns in a select statement for a class I'm in.

Everything is coming from the same table. I want to know how many times that person paid something and how many times they received something.

There is an ID for each time something is given, but that same ID would be for when someone receives something. I was trying to do the following, but I keep getting Scalar Subquery produced more than one element.

Select Distinct A, Receiverid, Giver, C, D, sum (received amount)
(Select Count(paidid) FROM Table WHERE A ='12345' GROUP BY Giver) as NumberGiven
(Select Count(Receiverid) FROM Table WHERE A ='12345' GROUP BY Receiverid) as NumberReceived
FROM Table
WHERE A = '12345'
Group By A, ReceiverID, Giver, C, D


texts: postgresql different configuration for different tablespace

texts:
I'm configuring new server with both SSD/HDD disk with ubuntu 18.04. I've installed system in SSD and created two different tablespaces one in ssd and one in hdd. I would like for configuration purposes put:

HDD

random_page_cost 4
effective_io_concurrency 2

SDD

random_page_cost 1.1
effective_io_concurrency 200

for each correct tablespace. Is it possible or are there some suggestions?


texts:
I made a mistake and set the max_connections column for root to '1', so now every time I try to connect to change it I get:

  ERROR 1226 (42000): User 'root' has exceeded the 'max_connections_per_hour' resource (current value: 1)

It was my personal server, so I don't have any other user.

I've tried:

Resetting the server
Resetting the mysql process

But nothing works. Does anyone know how to change it?


texts: enable the data access for the current server - is this operation logged somewhere?

texts:
I have a similar question related to the same subject:

security concerns over enabling data access for the current server

BEGIN TRY
    PRINT  @@SERVERNAME
    PRINT  DB_NAME()
    PRINT 'enable the data access for the current server'
    EXEC sp_serveroption @server = @@servername
                        ,@optname =  'data access'
                        ,@optvalue =  'TRUE'
END TRY
BEGIN CATCH
        PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
              'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13)

        PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'ERROR MESSAGE: '
        PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)

END CATCH
GO

Before anyone says anything about my NTEXT above, I would like to say I am not using this anymore I use this procedure from Solomon Rutzky (with thanks) instead.


texts: How to implement Ola Hallengren backup plan on the server for some databases nightly full backups and for other weekly full backup only

texts:
I recently implemented Ola Hallengren Maintenance solution on all of my production servers(SQL server 2005,2008,2008R2 and 2012).  It works great.
My problem is how to implement Ola Hallengren backup plan on the server for some databases nightly full backups and for other weekly full backup only.
To be specific I have 20 databases on the server.  It is required 15 databases have nightly full backup plan.  The remaining five databases should have weekly database full backup.

I scheduled the backup job for 15 databases nightly full backup. It is working great. But I don’t know how to take care the remaining five databases weekly full backup. (SQL Server 2008R2)
On native maintenance plan I used to create two plans
1.  Nightly full backup for the 15 databases and
2.  Weekly full backups for the remaining  five databases
I don’t know how to do that on ola.
Do I need to install ola Backup solution again for the five databases?  Or is there another way to do it?

I copy and past the script from ola User database job substitute my databases on the place of user databases and create backup job as follows

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d MASTER -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'DB1,DB2,DB3,DB3,DB4,Db5', @Directory = N'C:\Backup2', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 72, @CheckSum = 'Y', @LogToTable = 'Y'" -b

I run a test backup . It worked but I am not sure that is the right way doing it or not? could you please somebody tell me whether it is ok or not.
Thank you your help in advance.

@KASQLDBA I have attached the error screen shot


texts: Proper way to preserve data for mariadb docker container

texts:
I am running a single mariadb container as a backend for a webapp. I restarted the container, and the only data remaining was from a long time ago. None of the recent transactions were available. They are seemingly all gone.

I thought that I had prevented this from happening by using volumes for the database files, but after talking to one of my DBA colleagues, I went back and checked, and sure enough, the last modification date for those files was far in the past.

My question is that if shutting down a mariadb docker container wipes all of the transactions from memory without writing them to disk, how am I supposed to preserve data during a database restart?

FWIW, I am using the official mariadb docker container image, which kills the process with a SIGTERM, and my data volume is linked to /var/lib/mysql, which is where I understood all of the data to be stored.


texts: How are SQLServer Timestamp/RowVersion Values Created?

texts:
I am relatively fresh to SQLServer's use of ROWVERSION/TIMESTAMP data, and normally just query for required rows based on modified dates.

How is the ROWVERSION assigned to a new row in a database? Does every new row start with the same rowversion, or is it a unique value? In other words, will a new row in the database have its rowversion set to currentMaxRowVersion+1?

The use case for this is to query for all rows whose data has changed since the last time the database was queried, EG:

SELECT * FROM MyTable WHERE RowVersion > previousHighestRowVersion


texts: Does "CREATE INDEX WITH DROP_EXISTING" use the bufferpool for the recreated index?

texts:
We have recreate our 4TB clustered index in ouor SQL Server. Even thought we have 1.5TB of memory we still run into the following error message:

"Msg 802, Level 17, State 2, Line 30
There is insufficient memory available in the buffer pool"

Nothing else was done on the SQL Server as it is dedicated to this database, which was run in Single User mode.

Does anyone has a good idea, what might have filled up the buffer pool - as far as I know, a recreated index will not be kept in memory, but rather on disk.

Some further information:

  max degree of parallism: 4
  MaxMemory: a little below 1,5TB
  Lock Pages in Memory is been activated
  the index was created with drop existing and MAXDOP-Option set to 1

The purpose was to stop a wild running UNIQUIFIER, which almost reached 2^31-1 due to a limited number of values for the clustered key (0,1,2,3,4,5,6,7,8 and 9 to be precise) - thus the clustered index key was simply on the partitionKey TINYINT and the internal column UNIQUIFIER.

To repeat a question: Who has some clue as to what might has filled up the buffer pool? Or does an index indeed store the complete recreate in the buffer pool?

One idea was, the LPiM-setting might has caused SQL Server not to page at all - I haven't evaluated that idea yet.

#

Here the database schema:

CREATE PARTITION SCHEME [PCBScheme]
    AS PARTITION [PBCKey]
    TO ( [P0], [P1], [P2], [P3], [P4], [P5], [P6], [P7], [P8], [P9], [PRIMARY] )
    -- each Partition holds approximatly 177 million entries, PRIMMARY is empty and doesn't really belongs in here

CREATE PARTITION FUNCTION [PBCKey](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);

CREATE TABLE [dbo].[Table](
      [TableID]      BIGINT    NOT NULL   IDENTITY(1,1)
    , [Other_Columns_1]    INT       NULL
    , [Other_Columns_2]    DATETIME2(7)    NULL
    , [Other_Columns_3]    BINARY(32)      NULL
    , [Other_Columns_4]    VARBINARY(max)  NULL -- regularly 1,5KB in size
    , [...]
    , [PartitionKey] AS (CONVERT(TINYINT,[TableID]%(10),0)) PERSISTED
)  ON [PCBScheme] ([PartitionKey]);

CREATE CLUSTERED INDEX [CL_Table__PartitionKey]
    ON [dbo].[Table] (
       [PartitionKey]  -- This is a dumb key to use for a clustered, when the values are only 0-9
    )
    WITH (
         FILLFACTOR              = 100
       , ALLOW_PAGE_LOCKS        = ON
       , ALLOW_ROW_LOCKS         = ON
       , PAD_INDEX               = OFF
       , DROP_EXISTING           = OFF
       , IGNORE_DUP_KEY          = OFF
    ) ON [PCBScheme] ([PartitionKey]); -- this is the CL as it is in Prod right now

And here is our recreate-command:

CREATE CLUSTERED INDEX [CL_Table__PartitionKey]
    ON [dbo].[Table] (
       [PartitionKey]
    )
    WITH (
         FILLFACTOR              = 100
       , ALLOW_PAGE_LOCKS        = ON
       , ALLOW_ROW_LOCKS         = ON
       , PAD_INDEX               = OFF
       , SORT_IN_TEMPDB          = OFF   -- our TempDB isn't big enough to support this (limited due to disk size)
       , DROP_EXISTING           = ON
       , IGNORE_DUP_KEY          = OFF
       , STATISTICS_NORECOMPUTE  = OFF   -- we will set this to ON next time
       , ONLINE                  = OFF   -- OFF is our only option due to the other columsn in the table
       , MAXDOP                  = 1     -- Default is 4, we will try either that or 20 or 19, which will be cores/NUMA node or one less
    ) ON [PCBScheme] ([PartitionKey]);


texts: MongoDB - real disk usage is LOWER than dbStats.storageFile

texts:
I'm a bit surprised by what I observe in my mongo server, so I would like to know if anybody understand how this is possible.

I have one collection of around 15K documents. For each of them, I store in GridFS 3 files, with an average size of 1MB per file. So the expected storageSize should be something near 60GB (if considering the size of the document itself to be 1MB too). I can't test all my data by hand, but I really want to be sure everything is here and that there is no missing data for some of the documents.

Here is what I get :

> db.stats();
{
    "db" : "myBase",
    "collections" : 6,
    "objects" : 348822,
    "avgObjSize" : 195475.27135329766,
    "dataSize" : 68186075104,
    "storageSize" : 69329817136,
    "numExtents" : 73,
    "indexes" : 14,
    "indexSize" : 42171808,
    "fileSize" : 67108864,
    "nsSizeMB" : 16,
    "dataFileVersion" : {
        "major" : 4,
        "minor" : 5
    },
    "ok" : 1
}

so storageFile ~ 65GB, this is acceptable.

But when I look at the file system

$ ps -edf | grep mongo
user 20728     1  0  2014 ?        05:31:03 /opt/tools/bin/mongod --dbpath=/local/mongo-data

$ cd /local/mongo-data
$ du -h
 4.0K   ./_tmp
 721M   ./journal
 8.8G   .
$ du -h *
 721M   journal
 65M    myBase.0
 2.0G   myBase.37
 2.0G   myBase.5
 2.0G   myBase.7
 2.0G   myBase.8
 17M    myBase.ns
 4.0K   _tmp

How come the data on disk takes LESS space that what it says in mongo ?
Why doesn't appear anywhere a stat with a value of 8GB ?

I add stats from files and chunks if it can help understand what is happening

> db.fs.files.stats();
{
    "ns" : "myBase.fs.files",
    "count" : 48911,
    "size" : 14316208,
    "avgObjSize" : 292.69914743104823,
    "storageSize" : 18898944,
    "numExtents" : 7,
    "nindexes" : 2,
    "lastExtentSize" : 7647232,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 0,
    "totalIndexSize" : 7873488,
    "indexSizes" : {
        "_id_" : 2076704,
        "filename_1_uploadDate_1" : 5796784
    },
    "ok" : 1
}

> db.fs.chunks.stats();
{
    "ns" : "myBase.fs.chunks",
    "count" : 283537,
    "size" : 67787872056,
    "avgObjSize" : 239079.45719958947,
    "storageSize" : 68819078704,
    "numExtents" : 48,
    "nindexes" : 2,
    "lastExtentSize" : 2146426864,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 0,
    "totalIndexSize" : 27324192,
    "indexSizes" : {
        "_id_" : 11781616,
        "files_id_1_n_1" : 15542576
    },
    "ok" : 1
}

Edit : I add here the result of the command asked by Antonis

> db.fs.chunks.find({},{_id:1}).itcount()
283537

$ du -ah .
17M ./myBase.ns
2.0G    ./myBase.37
2.0G    ./myBase.7
2.0G    ./myBase.5
4.0K    ./_tmp
65M ./myBase.0
4.0K    ./journal/lsn
724M    ./journal/j._19
724M    ./journal
2.0G    ./myBase.8
8.8G    .


texts:
Today, between 3:00pm-3:30pm, my Oracle 10g database on Windows hanged twice. Error messages were related to ORA-00604. I'm pretty sure it was some query or queries that some user run as owner of some schema.

I'm checking listener.log, archived redo logs from that time (about 20 million records) and alert.instance-name.log on C:\oracle\product\10.2.0\admin\instance-name\bdump, but I don't find any anormal related to ORA-00604.

Would you suggest how or what else to search?


texts: LEFT vs CHARINDEX for matching the start of a string in T-SQL

texts:
I want to match the beginning of a string in a WHERE clause. While I recognise that so often database-performance is dependant on the underlying data structure, is there a best-practice for doing this? Is there one option which always outperforms the rest?

I want to avoid LIKE as I assume it will be less efficient than LEFT or CHARINDEX. As far as I can tell, my choices are below. Other suggestions welcome:

DECLARE @MyField as varchar(10)
SET @MyField = 'HELLOWORLD'
SELECT 1 WHERE @MyField LIKE 'HELLO%'
SELECT 2 WHERE LEFT(@MyField, 5) = 'HELLO'
SELECT 3 WHERE CHARINDEX('HELLO', @MyField) = 1

Kind Regards,

Jase.


texts: How to deal with async calls to a stored procedure that updates data

texts:
I have asynchronous calls from Entity Framework to a stored procedure.

The stored procedure selects data to a temp table and later on, it updates the real table. This works fine for sync calls.

The problem starts when the stored procedure is called 2-3 times asynchronous. Sometimes it returns wrong data.

Here is a flow of what happens (it's not accurate, it's just to demonstrate the problem):

First thread enters the stored procedure.
Second thread enters the stored procedure.
First thread selects a table and inserts it to a temporary table.
Second thread selects a table and inserts it to a temporary table.
First thread updates the real table from the temporary table [the table that was selected in to the temporary table (section 3) is also updated].
Second thread updates the real table from the temporary table [the table that was selected in to the temporary table (section 4) is also updated].

The problem is that thread 2 doesn't use the new data that thread 1 updated. It uses the old data and therefore the update is not working.

My question:

What is the best solution for dealing with it? So that the code will be asynchronous as possible, and more important will work perfectly.

I thought maybe to lock the temp table, so the second thread will wait until the first thread finishes with that table. Or to use transactions. Also, I can call it synchronous but then it hurts the performance.

Thanks.


texts: crosstab query : count a number occurence in a row

texts:
I have a crosstab query that shows 1 and 0 in the value area.

          Function 1 Function 2 ..Function n
project 1    1          0             1
project 2    0          0             1
..
project n    1          0             0

I need to add a column before Function 1 e.g ( Confirmed) which will count the values of 1's in every row.
I tried the case and if

SELECT
  ((CASE WHEN Function1 IS NULL THEN 1 ELSE 0 END)
  + (CASE WHEN Function2 IS NULL THEN 1 ELSE 0 END)
  + (CASE WHEN Function3 IS NULL THEN 1 ELSE 0 END)) AS sum_of_nulls
FROM (query name)

but did not work.
Can you please help on that ?


texts: Unresponsive database, sleeping processes


texts: SQL 2016 Clustered Install - SlipStream - Updates not Shown

texts:
Attempting to install SQL 2016 with the latest SP and CU.

Running the following (from the directory with the Install exe):

setup.exe /Action= InstallFailoverCluster /UpdateEnabled=TRUE /UpdateSource= "D:\Software\SQLServer2016_Updates"

The GUI starts and the Product Updates step is shown but nothing is displayed.

Note that when attempting to do this as a standalone install ie Action=Install, the SP and CU are shown in the GUI.

Any thoughts (short of creating a full config file)?


texts: How to Export SQL Server Database To A File For Later Import To Different Computer

texts:
I'm using the Sql Server 2012 Import and Export data (64) i'm not sure what options i should be picking to do what i want. I just want to be able to export all the data in the database to a file which i can then send to my other machine and then import the database.

The databases just contains tables.


texts: Why is this query stuck if there are no waits?

texts:
I am working with a vendor's activity logging system. One of the tables has 70+ million records and is constantly increasing at several hundred records per minute.

They have a utility to delete old records from this table, in batches of 500 records. When this utility is run, the deletes seem to get stuck. There is blocking going on, but the lead blocker (86) never seems to finish. There is no current wait for the lead blocking query (according to sp_WhoIsActive), yet it never finishes. What could be going on?

The database is in SIMPLE recovery mode.


texts: redundant foreign keys in PostgreSQL - are there any indexing benefits?

texts:
Note: this is not the same as this SO question which is about adding redundant information in the tables themselves.

Does the final statement in the below series of statements buy me anything in terms of indexing / query optimization or is it totally useless and an anti-pattern? From a referential integrity viewpoint it is useless but might there be some benefit in indexing, or query processing speed for instance, e.g. in a JOIN only between the "child" and "grandfather" tables?

CREATE TABLE grandfather (i INTEGER);
ALTER TABLE  grandfather ADD PRIMARY KEY (i);

CREATE TABLE father  (i, j INTEGER);
ALTER TABLE  father ADD PRIMARY KEY (i, j);
ALTER TABLE  father ADD CONSTRAINT father_2_grandfather FOREIGN KEY (i) REFERENCES grandfather(i);

CREATE TABLE child (i, j, k INTEGER);
ALTER TABLE  child ADD PRIMARY KEY (i, j, k);
ALTER TABLE  child ADD CONSTRAINT child_2_father      FOREIGN KEY (i, j) REFERENCES father(i, j);
-- is the below statement totally useless ?
ALTER TABLE  child ADD CONSTRAINT child_2_grandfather FOREIGN KEY (i)    REFERENCES grandfather(i);


texts: Why was a "default trace stopped due to not enough space on the disk" Alert email not sent until 3 days after the event?

texts:
SQL Server 2008 R2 instance sent a Severity 016 Alert: "Trace ID '1' was stopped because of an error. Cause: 0x80070070(There is not enough space on the disk.). Restart the trace after correcting the problem." This poorly configure server (which I inherited) has only a single 500 GB C: drive, which PRTG shows had about 260 GB free at the time of the Alert.

SELECT * from sys.traces produced no result, so the trace had indeed been stopped. I restarted it without difficulty, after which SELECT * from sys.traces showed the new file. The disk contains the four older 20 MB files and the one new file.

The alert email was sent at 2017-05-07 9:30. Digging a bit deeper, I see that the trace data shows that the last trace entry prior to my restarting the trace was at 2017-05-04 05:51. At that time we did have a disk space issue that had to be resolved, and which was not resolved for approximately two hours (about 2017-05-04 7:45).

The Job Agent Alert is defined as:

EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',
        @message_id=0,
        @severity=16,
        @enabled=1,
        @delay_between_responses=7200,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

Checking msdb.dbo.sysmail_mailitems, I see no entries between a job failure alert on 2017-05-04 07:44 (the last job failed due to the lack of disk space before that issue was resolved) and the alert email at 2017-05-07 09:30. Nothing was done with database mail between when the disk space dropped to zero until now.

What could cause this alert to not send an email until over three days after the triggering event?


texts: cron command running postgres at high cpu

texts: I can see that a cron command is running under a postgres user and killing the CPU but I have no idea how it's being executed or how to stop it !


texts: IMPORT FOREIGN SCHEMA fails with Password error for 127.0.0.1 but not for remote IP (of same server)

texts:
I have a Postgres server in Google SQL Cloud with multiple databases. I want to create a FOREIGN DATA WRAPPER between them.  If I use the server's remote IP it works:
CREATE SERVER "some_db_fdw" FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '34.94.1.23', dbname 'some_db');
CREATE USER MAPPING FOR some_user SERVER "some_db_fdw" OPTIONS (user 'some_user', password 'some_password');
IMPORT FOREIGN SCHEMA public LIMIT TO (my_table) FROM SERVER "some_db_fdw" INTO public;

...but if I use the local IP (127.0.0.1) in place of 34.94.1.23 (sample IP only) it gives me the error:
ERROR: password is required
DETAIL: Non-superuser cannot connect if the server does not request a password.
HINT: Target server’s authentication method must be changed.

Why? I'm providing the password in the user mapping.  In fact if I omit the password there then the error changes to:
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the user mapping.

So it seems to know that it should be getting the password from the user mapping.
The reason I can't just use the remote IP is that we require certs so if I enable "Allow only SSL connections" using the remote IP fails because I don't have certs within psql (I have them on the machine I connect with or I wouldn't be able to connect at all, but since this server is in SQL Cloud, I don't have any filesystem access and can't provide the DB itself with certs to connect to the other DB - thus my attempt to use 127.0.0.1 to avoid this issue).


texts: Is there a way to replicate master/slave without disturbing master for dump like mysqldump or cold copy of data files? I'm ok to bring down the instance only once for master to enable log-bin. But there is no feasibility for dump or even cold copy. As I have 400G of data and 1min down time only.


texts: SQL Server 2008 - combined index + other separate index on one column

texts:
Lately I've been refactoring the SQL Server indexes of the products that I have developed for my employer. One of these products is an online dashboard containing multiple user levels.

Each user (on admin level) can create projects that they can then link to users of the user level below. Each project has its own Name field and also the field AdministratorUserId to keep track of the project admin. I created a combined non-clustered unique index on these two fields, meaning that project names have to be unique per administrator.

Now here's the catch: I am noticing that I often do a select on AdministratorUserId only, and not on Name. According the indexing guidelines, and please correct me if I'm wrong, I am to create a non-clustered index on the field in order to optimize my queries.

My question being - does my existing index on Name + AdministratorUserId already improve my queries or is it advisable to create a separate index besides it? Would there be any downside to AdministratorUserId being present in two separate indexes?


texts: Oracle 11.2 restore DB to a specific point

texts:
I should execute following tasks:
1. test system and during test change Oracle data and Create New/Remove Old datafiles and tablespace.
2. at the end of test rollback to behviour before of point 1.

I'm working on Oracle 11.2 on two different sites: on single instance configuration and another in DataGuard environment.

To reach the scope I think following possibilities:
1) a- execute dump before starting.
   b- recreate manually datafiles/tablespaces and import data from dump at the end.

2) a- backup via RMAN all the DB.
   b- restore via RMAN the backup at the end.

3) a- create recovery-point.
   b- flashback at the end ....???? (I'm not sure about this chance with change on Datafiles and tablespace.

What your opinion on it?
Thanks,
 Ste


texts: sp_dts_getfolder isn't in my MSDB database, any ideas why?

texts:
We're currently moving our applications from a SQL 2005 to a SQL 2008 R2 database and everything has gone well(ish) but now one of our applications calls a stored proc called sp_dts_getfolder which is meant to be in the MSDB database.

But it doesn't seem to be there at all, did we not install something or is this no longer a valid stored procedure in 2008 R2? What replaced it?


texts: How can I interpret the results of these DMVs to help me evaluate our partitioning strategy?

texts:
Version: SQL Server 2008 R2 Enterprise Edtn. (10.50.4000)

In attempt to evaluate our partitioning strategy, I wrote this query to get the access methods against indexes on partitions (in the broadest sense of the term, though I'm eliminating heaps). As I narrow my focus to partitioned tables, I believe I need to be looking at range_scan_count and singleton_lookup_count but am having a hard time conceptualizing.

SELECT
    t.name AS table_name,
    i.name AS index_name,
    ios.partition_number,
    leaf_insert_count,
    leaf_delete_count,
    leaf_update_count,
    leaf_ghost_count,
    range_scan_count,
    singleton_lookup_count,
    page_latch_wait_count ,
    page_latch_wait_in_ms,
    row_lock_count ,
    page_lock_count,
    row_lock_wait_in_ms ,
    page_lock_wait_in_ms,
    page_io_latch_wait_count ,
    page_io_latch_wait_in_ms
FROM sys.dm_db_partition_stats ps
    JOIN sys.tables t
        ON ps.object_id = t.object_id
    JOIN sys.schemas s
        ON t.schema_id = s.schema_id
    JOIN sys.indexes i
        ON t.object_id = i.object_id
    AND ps.index_id = i.index_id
OUTER APPLY sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
WHERE
    ps.object_id = ios.object_id
    AND ps.index_id = ios.index_id
    AND ps.partition_number = ios.partition_number
    and ps.index_id = ios.index_id
    and ps.partition_number = ios.partition_number
    and s.name <> 'sys'
    and ps.index_id <> 0 ;

Relevant output (given the gap in SO's formatting of tables, this is a sample of the first 9 columns from the query above with last two columns being range_scan_count and singleton_lookup_count, respectively):

╔════════╦═════════════════╦════╦═══╦═══╦═══╦═══╦════════╦══════════╗
║ datetb ║ idx_datetb_col  ║  1 ║ 0 ║ 0 ║ 0 ║ 0 ║ 205740 ║  3486408 ║
║ datetb ║ idx_datetb_col  ║  2 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  1079649 ║
║ datetb ║ idx_datetb_col  ║  3 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  1174547 ║
║ datetb ║ idx_datetb_col  ║  4 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  2952991 ║
║ datetb ║ idx_datetb_col  ║  5 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3974886 ║
║ datetb ║ idx_datetb_col  ║  6 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  2931450 ║
║ datetb ║ idx_datetb_col  ║  7 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3316960 ║
║ datetb ║ idx_datetb_col  ║  8 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3393439 ║
║ datetb ║ idx_datetb_col  ║  9 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3735495 ║
║ datetb ║ idx_datetb_col  ║ 10 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  4803804 ║
║ datetb ║ idx_datetb_col  ║ 11 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  7655091 ║
║ datetb ║ idx_datetb_col  ║ 12 ║ 1 ║ 0 ║ 0 ║ 0 ║ 174326 ║ 47377226 ║
╚════════╩═════════════════╩════╩═══╩═══╩═══╩═══╩════════╩══════════╝

I see a few different possibility but I need some direction on how to think about this (of course I'm couching this in "may" because I know that "it depends", but I'm also looking for conceptual comprehension):

Similar values for all partitions of range_scan_count may indicate that we're not getting good partition elimination because we're scanning all partitions roughly the same number of times.
Varying values for all partitions of singleton_lookup_count accompanied with significantly lower values for range_scan_count may indicate good frequent partition elimination because we're scanning less than we're seeking.
?

Those are my thoughts so far. I was hoping to have someone weigh in on how I could use this, or another set of information, to determine which tables would most likely benefit from dropping partitioning in favor of indexes altogether.

EDIT

Here's a clipped DDL:

CREATE TABLE [dbo].[date_table](
    [date_id] [int] NOT NULL,
    [calendar_date] [datetime] NULL,
    [valdate] [datetime] NULL,
        CONSTRAINT [PK_datedb] PRIMARY KEY CLUSTERED
        (
            [date_id] ASC
        ) ON [partschm]([date_id]);

CREATE UNIQUE NONCLUSTERED INDEX [idx_datetb_col] ON [dbo].[date_table]
(
    [calendar_date] DESC,
    [date_id] ASC
) ON [partschm]([date_id])
GO


texts: What are definer and invoker rights in database, Oracle and MySQL? I just don't get it.


texts: Where MySQL is taking time either in disk read or computation?

texts:
From SQl case study

System configuration is

Software: Percona 5.6.15-63.0. Hardware: Supermicro; X8DTG-D; 48G of RAM; 24xIntel(R) Xeon(R) CPU L5639 @ 2.13GHz, 1xSSD drive (250G)

Query is select yeard, count(*) from ontime group by yeard where yeard is indexed column

The query is simple, however, it will have to scan 150M rows. Here is the results of the query (cached):
The query took 54 seconds and utilized only 1 CPU core

My understanding :- Per my calculation it should have finished in much less time based on above system shared configuration and data to scan.
I know I am wrong and missing something but what is that ?

Here is my calculation for seconds

For HDD,  average time taken to read 100MB of data from disk is around 1 sec. For SSD is 5 to 10X faster. But still conservatively if I assume the speed of
100MB per seconds , time to  read the data will be size of data in MB/100 seconds . Based on this size of data is 150 * 10^6 * 4/10^6= 600 MB
assuming each year is of 4 byte long. So total time to read the data from disk should be 600/100 = 6 secs
Now it 2.13GHz CPU which means that 2 billion cycles per seconds which on average means 2 billion instruction per second per core. Now  actual
time taken to execute the query was 54 seconds which means time taken to compute the instruction was approx 54-6 = 48 seconds. Does it mean to  it had to execute  around 48 * 2 = 96 billion instructions just to calculate
the count and group by year or am I missing something ?

May be more time in disk read or count looks simple but internally it involves number of instructions ?


texts:
I need to upload daily CSV dumps to a blackbox of my choice, and it should be possible to run queries or create filters over the uploaded data. Daily dumps weight around 1TB and are basically id, datetime, latitude and longitude columns.

It's desirable to be able to store 1 year of info, so we're talking about 365TB here.

I believe that, provided enough bandwidth, I could upload the CSV to a cloud storage and import it to a Big data engine. For example: upload to Google Cloud Storage, then use it as source to import to Google Big Query, or upload to Amazon S3 and use it as source to import to Amazon Redshift.

At the beggining I thought hadoop was a third solution, but then I saw that hadoop can use Google BigQuery as its persistence layer, and that in front of hadoop there is yet another layer to pick (Apache Spark, Cloudera Impala).

So, at this point, I'm lost. In what use case should I go for hadoop? Is it safe to assume that for the most basic queries/filters I can rely on BigQuery alone, and leave open a further connection with hadoop only in case I need more elaborate Mapping/Reductions?


texts: Aggregating Multiple COTS Databases into a Data Warehouse or similar for Reporting

texts:
I am sure this is a very basic question, so I apologize, but I've read lots of articles and done a lot of google searches and have not found any leads for this particular challenge I am looking to address.  I appreciate any guidance that can be provided.

I have an enterprise environment where we need to have multiple instances of a COTS application spread geographically around the world.  These applications are using SQL Server Enterprise Editions 2008, 2012, and 2014 (different versions of the COTS software) and have to remain in their geographic isolation.  I have created an SSRS server to facilitate being able to give the customers access to their specific geographically isolated COTS database data.  However, the demand has now grown where we want the users to be hitting a data warehouse for the SSRS data so we don't risk impacting our production systems.  Additionally, my team would like to be able to aggregate the data for some enterprise reporting.
 I have read a bunch on data warehousing but I can't seem to find any solutions to bringing 14 different instances of a single COTS application into a single SQL server for reporting against.  Ideally we'd like to be able to track the changes as well.  The 3 databases per COTS instance are all named the same regardless of the version.  Two of the databases are relatively static (maybe 200 updates per day each), but one is very active (70k+ writes per day).  The solution doesn't have to be real time, but does need to at least offer a daily load.

Database 1 ~ 600 MB and 200 writes/day

Database 2 ~ 1.4 GB and 200 writes/day

Database 3 ~ 35-120 GB (depending on location) and 70k writes/day (all application events and audit) [old entries are never modified so would only need to send the delta]

So how can I bring in 42 Vendor Databases into a single data warehouse?  Is there an approach better than the data warehouse to pursue?  Thank you very much in advance, and if there is anything else that is needed to offer a solution, please let me know.


texts:
I have a field that may start and end with quotes. I only want to remove the quotes if the string starts and ends with quotes. If quotes exists inside the string then I wish to retain them.

I have used REVERSE and stuff which works if the field does start and end with quotes but if it does not it just returns null.

How can I ensure I still get the field result if it did not start with quotes?

What I have:

REVERSE(STUFF(REVERSE(STUFF(fieldname, CHARINDEX('"', fieldname), LEN('"'), '')), CHARINDEX('"', REVERSE(STUFF(fieldname, CHARINDEX('"', fieldname), LEN('"'), '')), 1), 1, ''))


texts: Getting SELECT to return a constant value even if zero rows match

texts:
Consider this select statement:

SELECT *,
       1 AS query_id
FROM players
WHERE username='foobar';

It returns the column query_id with value 1 along with a player's other columns.

How would one make the above SQL return at least the query_id of 1 even if the select finds no rows that match?

BTW, it's PostgreSQL 8.4.


texts: incorrect result when filtering by association

texts:
I have a docket, which has many entries. I want only dockets, where none of the associating entries contains the text "PENDING". For example, if docket A has 10 entries, and one of those entries' docket_type varchar field contains the word "PENDING" anywhere, then I don't want that docket.

This is my solution:

SELECT * FROM dockets
INNER JOIN entries
ON entries.docket_id = docket.id
WHERE (docket_type NOT LIKE '%PENDING%')

The problem is, while it does not retrieve entries that contain that text, it still retrieves the associating dockets, when those dockets contain other entries that do not contain that text. I don't want to retrieve the docket if one of its entries contains this text in docket_type.

How could I fix this?


texts: Slow postgres query that uses quick sort instead of hash-aggregate

texts:
I'm on postgres 9.5 and working with a web-analytics database that records visitor traffic.

I'm trying to optimize a very slow query gives me a count of the unique people that visited a given page grouped by their session device type.

The query looks like so:

select   groupname,  count(person_id) as thecount
from (
  select distinct S.first_device_type as groupname, A.person_id
  from event_page as O
  join alias as A
    on (O.person_alias = A.alias)
  left outer join session as S
    on (O.session_id = S.session_id)
  join alias as A1
    on ( A1.alias = S.person_alias and A.person_id = A1.person_id)
  where O.timestamp_ between timestamp '2017-02-11 23:22:20.146' and timestamp '2017-03-13 23:22:20.146'
    and O.location_host = 'www.foo.bar.ca'
    and S.first_seen between timestamp '2017-02-11 23:22:20.146' and timestamp '2017-03-13 23:22:20.146'
) as alias_120134400
group by groupname

The above SQL runs in over 2.5 minutes and the output looks like this.

device type        count
--------------------------
Computer       |  163304
Game console   |      41
Mobile         |   33519
Tablet         |   10465
Unknown        |       5

There are couple peculiarities about the schema and above query I need to point out:

The event_page table is a union of monthly tables
Joining the alias table twice is necessary because an alias may change during a session. aliases to personIDs is many to one. Hence the page view alias and session alias may be different but point to the same personID.
All statistics are up to date (everything vacuum analyzed)!

As the explain below shows it appears that the Quick Sort is the bulk of my problem:

https://explain.depesz.com/s/6JjQ

In addition to that there is seq scan occurring (line 23 in the explain) on the session table yet that condition should be easily handled by the index.

I've run the same query on a different (albeit smaller) dastaset and the query plan seems more sensible. This one executes in only 3 seconds. Here is the explain:

https://explain.depesz.com/s/4k8t

Any ideas as to why the former explain chooses quick sort and the latter uses HashAaggregate to achieve the DISTINCT?

What can I do optimize the query and avoid quick sort?

Why (in both cases) is the planner choosing a seq scan of session when that filter is covered by an index?

Thanks in advance for any help.


texts: Oracle startup error: "end-of-file on communication channel"

texts:
The Oracle instance I am working with is not coming up after issuing a startup. All I get is the following:

SQL> startup
ORACLE instance started.

Total System Global Area 1.2300E+10 bytes
Fixed Size                  2263088 bytes
Variable Size            1.002E+10 bytes
Database Buffers         3018334336 bytes
Redo Buffers               48056256 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5038
Session ID: 221 Serial number: 3

For ORA-03113: there shouldn't be any network issues, as this session is logged as sysdba on the machine. And this instance has been working for over two months with no problems.

The oracle service is also running and was able to restart.

Environment: Windows 2008 Enterprise SP1, Oracle 11g R2.

I'd really appreciate any direction and help you could offer to troubleshoot and resolve this issue.

Here are the details from alerts.log:

KCF: read, write or open error, block=0xf0412 online=1
        file=25 'C:\ORADATAFILES\ORAPROD03\USERS03.DBF'
        error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 665) The requested operation could not be completed due to a file system limitation'
Errors in file C:\ORACLE\diag\rdbms\ORAPROD03\ORAPROD03\trace\ORAPROD03_dbw1_224.trc:
Errors in file C:\ORACLE\diag\rdbms\ORAPROD03\ORAPROD03\trace\ORAPROD03_dbw1_224.trc:
ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 25 (block # 984082)
ORA-01110: data file 25: 'C:\ORADATAFILES\ORAPROD03\USERS03.DBF'
ORA-27072: File I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 665) The requested operation could not be completed due to a file system limitation
Sun Aug 19 10:26:42 2012
KCF: read, write or open error, block=0xf04e2 online=1
        file=25 'C:\ORADATAFILES\ORAPROD03\USERS03.DBF'
        error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 665) The requested operation could not be completed due to a file system limitation'
Errors in file C:\ORACLE\diag\rdbms\ORAPROD03\ORAPROD03\trace\ORAPROD03_dbw0_5624.trc:
DBW1 (ospiC: 224): terminating the instance due to error 63999

trace file has this error message:

ORA-00449: background process 'DBW1' unexpectedly terminated with error 63999

Now, I understand that the USER03.DBF data file is
somehow inaccessable. But the permissions on the file are "Full Modify" and nothing else is accessing this specific file, like a backup software or antivirus; this is a dedicated oracle box.

Given the the file is not being able to be read or write from, what are my options?
The file size is 27GB, but size on disk is 4GB; I am not sure if this is of any significance. And disk is definatly not out of space.

Autoextend is set at 100MB. And database is about 150GB. Within alerts.log, there were over 50 autoextend notes before the corrupt block error showed up.


texts: ignore mysql schema on replication before moving from mysql 5.6 to mysql 5.7

texts:
I am planning to move from mysql 5.6 to mysql 5.7, so I going to upgrade one of my slaves instances to 5.7, and later on to switch it to a master.

Should I need to ignore mysql db before upgrading mysql 5.6 to 5.7 on this slave ?

replicate-ignore-db = mysql
replicate-wild-ignore-table=mysql.%

If so to what should I pay attention after upgrading or before switching my master to 5.7 (beside testing mysql 5.7 with my application)?

Any suggestions would be appreciated.

Here is my.cnf:

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld_safe]
pid-file=/var/run/mysqld/mysqld.pid

[mysqld]
read_only
max_connections=2500
log-slave-updates=true
port=3306
socket=/var/lib/mysql/mysql.sock
datadir=/data/mysql
innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_support_xa=0
innodb_io_capacity=2500
innodb_adaptive_hash_index=1
innodb_doublewrite=0
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_max_dirty_pages_pct = 90
table_open_cache=2500
innodb_open_files=10000
open_files_limit=10000
optimizer_search_depth=0
innodb_file_per_table
max_connect_errors=10000000000
max_connections=2500
long_query_time = 1000
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 500M
sort_buffer_size = 8M
join_buffer_size = 8M
query_cache_size = 0
query_cache_limit = 0
query_cache_type=0
tmp_table_size = 1200M
max_tmp_tables=250
long_query_time = 2
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 42G
innodb_log_buffer_size = 8M
innodb_log_file_size = 400M
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 100
server-id = 102
expire_logs_days        = 12
max_binlog_size         = 100M
relay-log=/data/binlogs/linuxdb1023306_r.-relay-bin
relay-log-index=/data/binlogs/linuxdb1023306-relay-bin.index
log-bin= /data/binlogs/linuxdb1023306-bin
binlog_format=row
innodb_locks_unsafe_for_binlog=1
max_connections=2500


texts: How to get a list of restricted_components with it's related information?

texts:
I have the following tables structure (DDL):

CREATE TABLE restricted_components
(
    component_id INT(11) PRIMARY KEY NOT NULL,
    resources_id INT(11) NOT NULL,
    component_dom_id VARCHAR(50) NOT NULL,
    component_dom_class VARCHAR(50),
    component_dom_data_name VARCHAR(50),
    visible TINYINT(1) DEFAULT '0' NOT NULL
);

CREATE TABLE resources
(
    resources_id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    resources_name VARCHAR(64) NOT NULL,
    menu_href VARCHAR(128),
    menu_text VARCHAR(64) NOT NULL
);

CREATE TABLE acl_groups_to_resources
(
    groups_id INT(10) unsigned NOT NULL,
    resources_id INT(10) unsigned NOT NULL,
    bitmask SMALLINT(6) DEFAULT '0' NOT NULL,
    CONSTRAINT `PRIMARY` PRIMARY KEY (groups_id, resources_id)
);

CREATE TABLE groups
(
    groups_id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    groups_name VARCHAR(50) NOT NULL
);

I am trying to get a list of restricted_components with it's related information from the rest of the tables meaning the resources_name and groups_name.

I have made this query:

SELECT
  `rc`.*,
  `rs`.resources_name,
  `gr`.`groups_name`
FROM `restricted_components` AS `rc`
  LEFT JOIN `resources` AS `rs` ON rc.resources_id = rs.resources_id
  LEFT JOIN `acl_groups_to_resources` AS `acl_gr` ON rs.resources_id = acl_gr.resources_id
  LEFT JOIN `groups` AS `gr` ON acl_gr.groups_id = gr.groups_id

But is wrong since I have unwanted results as the image below shown:

I am sure the problem is on the LEFT JOIN but I can't find where exactly and how to fix this.

Update #1: Using JOIN instead doesn't work either.

I believe the problem is in the data but I am not sure at all, hopefully someone will have a time to check this.

Can I get any help?

  Note: I have leave a set of data here so you can play with it if needed but is not accurate, you'll need to remove some columns and it's values.


texts: Connect to Oracle using SQLDeveloper using OS Authentication on a remote machine

texts:
I reach my database server from my MacBook via SSH. You can login into this database as by using OS authentication.

MacBook:~ mangeles$ ssh oracle@10.10.1.64
oracle@10.10.1.64's password:
Last login: Thu May 21 11:45:14 2015 from 172.31.12.51
[oracle@bd ~]$ . oraenv
ORACLE_SID = [MF] ? PROD
[oracle@bd ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 21 11:46:17 2015

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

I'm able to connect to the database using SQLDeveloper but I have to provide a username and a password. I can connect to all the databases as a SYS (through SSH) but I don't have the SYS password of all the databases and changing the password is not an option.

I want to know how I can use the OS Authentication in my SQLDev to login as I do in my terminal, without entering a password.


texts: Convert Rows to columns using 'Pivot' in SQL Server when columns are string data type

texts:
I need to know whether 'pivot' in SQL Server can be used for converting rows to columns if there is no aggregate function to be used. I saw lot of examples with aggregate function only. My columns are string data type and I need to convert this row data to column data. This is why I wrote this question. I just did it with case. Can anyone help me......Thanks in advance.

Sample format

empid    wagecode    amount
  1      basic       1000
  1      TA           500
  1      DA           500
  2      Basic       1500
  2      TA           750
  2      DA           750

Desired output:

empid   basic    TA    DA
  1     1000     500   500
  2     1500     750   750


texts:
I got a quite easy question for you.

I have a table

 products [ID,Name,Category,Price]

and a table

product_client[ID,product_ID,client_ID]

The products table contains all the products of course, but what the product_client does is, it is storing some products that are exclusively for a certain client.

So I wrote a small query for this one

SELECT *
FROM product
LEFT JOIN product_client ON product.ID = product_client.product_id
WHERE product_client.client_id =1

Everything works fine, returns me the products exclusively for this client. But what I want is more - the products table is like a dictionary where on top of that client has his own products. So I need to get the whole products table, plus the query output above.

Any tips? Is it a good idea to have this set up this way?

Consider a case where:

In the database we have ids 1.2.3.4.5
Product ids 1.2 are assigned to client 1
The product ids 3.5 are assigned to client 2.

We want to query for client 1. So we expect to get the following ids: 1.2.5,  since 3 belong to clients 2, and number 4 is a global product (unassigned). Any unassigned is a global product.

Products Table

+-----+-------------+-----------+-------+
| ID  |    Name     | Category  | Price |
+-----+-------------+-----------+-------+
|  1  | Paper       | Material  |    10 |
|  2  | Wool        | Material  |     4 |
|  3  | Cleaning    | Service   |    10 |
|  4  | Something   | Service   |     1 |
|  5  | Something2  | Service   |     2 |
+-----+-------------+-----------+-------+

Client Table

+-----+---------+-------------------+
| ID  |  Name   | {anyextra fields} |
+-----+---------+-------------------+
|  1  | Client1 |                   |
|  2  | Client2 |                   |
+-----+---------+-------------------+

product_client Table

+-----+-----------------+-----------+
| ID  | product_client  | client_ID |
+-----+-----------------+-----------+
| 1   |              1  |         1 |
| 2   |              2  |         1 |
| 3   |              3  |         2 |
| 4   |              5  |         1 |
+-----+-----------------+-----------+

Desired output for client_ID = 1

      Query return table
+-----------+----------+-------+
|  Product  | Category | Price |
+-----------+----------+-------+
| Paper     | Material |    10 |
| Something | Service  |     1 |
| Wool      | Material |     4 |
+-----------+----------+-------+


texts: Restoring an SQL Server 2012 MDF to SQL Server 2008

texts:
Possible Duplicate:
  Restore SQL Server 2012 backup to a SQL Server 2008 database?

I have created a database on SQL server 2012 with compatibility mode for SQL server 2008 (100).
When I do a backup and try to restore it on an SQL 2008 I get an error. Same goes for attach/detach.

Is there a way to move/restore/migrate it to SQL Server 2008?

Thanks

Tomer


texts:
I'm figuring out a way to structure a new PostgreSQL database, and I'm curious if I could use a JSONB column instead of the EAV model for storing properties of an entity. This seems like a flexible way to store these kinds of things.

In the EAV case, I would have 3 tables:

materials (mat_id, mat_name, mat_descr)
material_properties (prop_id, prop_name, prop_desc)
material_property_values (mat_id, prop_id, val)

Each material can have a different set of properties.

Are there any reasons why I shouldn't just add a column mat_props JSONB to my materials table, and store all properties there as {"name":"value"}? This way, querying properties as columns would be as easy as:

SELECT mat_name, mat_props->'name' as propval FROM materials

instead of joins or subselects.

Also, could I add this column in the materials table or would it be much more performant to keep it in a separate table as:

material_properties (mat_id, properties JSONB)


texts:
If I wanted to backup to the FRA and another location, how would I configure the channels? Also, would I need to set parallelism?

Thanks


texts: How to plan bring down the space used by databases to as low as possible

texts:
Per our security breaks and guidelines when a database grow too big that it cant meet the requirements where it cannot be restored under a given RTO we need to plan on achieving same:-
Based on your experiences i would need some guidance and help here:-
Some insights on the database in question -

Total space --> Right click database-properties -> size = 10TB
What seems free in above -->  Right click database-properties -> space
Available = 5 TB

This database is an OLTP and critical one as it goes through lot of DUI operations throughout with heavy usage of TempDB

I have listed out TOP 10 tables with space and index usage:-
All top 5 as pulled are

partitioned table with billion and million rows over 700 partitions
in single table

Above tables have PAGE compression enabled

Above tables have less than 3 indexes in most of them but compression
at index level shows NONE

For backup we do use compression with third party tool like idera to reduce size yet it crosses the threshold. Backup size is approx 1.9 TB for full backups. We do have diff and tran log backups as well.
I am little hesitant to go that shrinking route as it may impact performance but please help me with any ideas but else can be done to bring the space down?
Can we split few large table to their own separate database? If yes what is the catch?
Much appreciate all your help


texts: SQL Server Replication Stored Procedures sp_MSdel_db, sp_MSupd_db, sp_MSins_db

texts:
I have 2 production servers and 2 development servers. All SQL Server 2008 r2.

In production, I had one of our DBAs create a replication publisher and distributor on ProdServer1 and Subscriber on ProdServer2.  This has been running for a few months with out issue.

Now, I'm working on a new project and asked for replication to be set up in our dev environment, identical to production.  So publisher and distributor on DevServer1 and Subscriber on DevServer2.

Now that this is complete, I'm looking at my databases and in DEV I have 3 stored procedures for each replicated table.  They appear to be simple crud procedures (Insert, Update and Delete).

In production, I do not have these stored procedures.  DBA number 2 is saying these are required for replication.  However, Production is still working and these stored procs aren't there.

The DBA who set up the original replication in production isn't available so I can't ask what was done differently there.

Can anyone tell me what would cause these stored procs to be created in one instance yet not in another? This is very confusing to me, and has me concerned because my development environment is so much different from production.


texts: Query Processing at backend (I/O on disk)

texts:
I have a table with 100k rows created on PostgreSQL 9.3

create table demo_bbb
(
  id numeric NOT NULL,
  code_bbb character varying,
  column_02 character varying,
  column_03 character varying,
  column_04 character varying,
  column_05 character varying,
  column_06 character varying,
  column_07 character varying,
  column_08 character varying,
  column_09 character varying,
  column_10 character varying,
  CONSTRAINT demo_bbb_pk PRIMARY KEY (id)
);

Here's my test (each query is run 3 times):

(1) select id from demo_bbb b ;         -- database query time: ~26ms
(2) select column_10 from demo_bbb b ;  -- database query time: 46ms, 48ms, 51ms
(3) select column_05 from demo_bbb b ;  -- database query time: 37ms, 38 ms, 45ms
(4) select * from demo_bbb b ;          -- database query time: 28ms, 32ms, 37ms

Result: Average time of (4) < time of (2), (3)  (32ms < 45 ms)

Explain analyze:

(1) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=6) (actual time=0.008..22.088 rows=100000 loops=1)"
(2) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=24) (actual time=0.008..38.702 rows=100000 loops=1)"
(3) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=24) (actual time=0.008..32.098 rows=100000 loops=1)"
(4) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=232) (actual time=0.007..17.702 rows=100000 loops=1)"

I am curious to know what happened but I can not understand. Could you give me some explanations of how these queries did ?

Note: I traced these queries by using SQL Profiler (download from Enterprisedb)


texts: Is SQL*Net message from dblink > 30% a performance issue?

texts:
I have a Java web-application running under Glassfish. App accesses Oracle using JPA (eclipselink).

Last 2 days I'm observing some perfomance issues: some messages about long running queries (10 to 100 sec) appears in Glassfish logs.

I asked Oracle DBA to provide Oracle AWR-log for 2 hours. There's SQL*Net message from dblink in top 5 Timed foreground events. It's seems suspicious for me, am I right, that in normal case there shouldn't be SQL*Net message from dblink in top 5 Timed foreground events? Is it a perfomance issue with database on the dblink other side?


texts: Can I create a constraint to a subset of data?

texts:
Here's a simplified view of my database

|---------------|     |-----------|
| ORGANIZATION  |     | CONTACT   |
|---------------|     |-----------|
| OrgId         |     | ContactId |
| OrgName       |     | OrgId     |
| MainContactId |     | FullName  |
|---------------|     |-----------|

Is there a way to ensure that MainContactId only belongs to the subset of the CONTACT table where the OrgId matches?

I did some looking and found that I can use a user defined function, but that appears to have performance issues. Do I have any other options here?

EDIT:
I played around, and got the following working. Is there a problem with this approach?

    CREATE FUNCTION dbo.CheckOrgMainContact(@OrgId int, @ContactId int)
    RETURNS int
    AS
    BEGIN
        DECLARE @Result int

        IF @ContactId IS NULL --if the contact ID is not set, don't check it
        BEGIN
           SET @Result = 1
        END
        ELSE
        BEGIN
            IF EXISTS (SELECT *
                       FROM dbo.Contact
                       WHERE OrgId = @OrgId and
                             ContactId = @ContactId)
               SET @Result = 1
            ELSE
               SET @Result = 0
        END
        RETURN @Result
    END
    GO

    CREATE TABLE dbo.Organization
    (
       OrgId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
       OrgName varchar(50) NOT NULL,
       MainContactId int NULL,
       CONSTRAINT [CheckOrgMainContact_Constraint] CHECK (dbo.CheckOrgMainContact(OrgId, MainContactId)=1)
    )
    GO

    CREATE TABLE dbo.Contact
    (
       ContactId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
       OrgId int NOT NULL FOREIGN KEY REFERENCES dbo.Organization(OrgId),
       FullName varchar(50) NOT NULL
    )
    GO
    ALTER TABLE Organization
    ADD CONSTRAINT FK_MainContact FOREIGN KEY (MainContactId)
        REFERENCES dbo.Contact (ContactId)
    GO

    --let's get some date inot the tables
    SET IDENTITY_INSERT Organization ON
    INSERT INTO Organization (OrgId, OrgName)
    VALUES(1, 'Google'),
          (2, 'Microsoft'),
          (3, 'Leidos');
    SET IDENTITY_INSERT Organization OFF

    SET IDENTITY_INSERT Contact ON
    INSERT INTO Contact (ContactId, OrgId, FullName)
    VALUES (1, 1, 'Homer'),
           (2, 1, 'Marge'),
           (3, 1, 'Bart'),
           (4, 2, 'Bob'),
           (5, 2, 'Linda'),
           (6, 2, 'Gene'),
           (7, 3, 'Frank'),
           (8, 3, 'Dee'),
           (9, 3, 'Dennis')
    SET IDENTITY_INSERT Contact OFF

    UPDATE Organization SET MainContactId = 1 WHERE OrgName = 'Google' --set this to a valid contact

    UPDATE Organization SET MainContactId = 4 WHERE OrgName = 'Google' --try setting this to an invalid contact

    SELECT o.OrgName, c.FullName FROM Organization o INNER JOIN Contact c on o.OrgId = c.OrgId --get all the contacts
    SELECT o.OrgName, c.FullName as MainContact FROM Organization o LEFT JOIN Contact c ON o.MainContactId = c.ContactId


texts: Copy billion Records from one table to another oracle

texts:
Hello I have Oracle database 12 with tablespace with bigfile Yes and now I have limited to 120Gb file data so I need change my tablespace to bigfile no and create multi datafile for my tablespace , now I need to export or copy data from old to new table but my problem is I have over 1 billion records And I don't know what is the fastest way to copy this table to another table.

CREATE TABLE C##USER.READ
(
  DEVICEID NUMBER(38, 0)
, THETIME VARCHAR2(20 BYTE)
, THEH VARCHAR2(10 BYTE)
, ENERGYEXPORT NUMBER(10, 0)
, LOADPROFILE NUMBER(10, 0)
, DEMANDREGISTER NUMBER(10, 0)
, AREA NUMBER(38, 0)
, STATUSM NUMBER(2, 0)
)
TABLESPACE TABLE_SPACE
PCTFREE 10
INITRANS 1
STORAGE
(
  BUFFER_POOL DEFAULT
)
NOCOMPRESS
NOPARALLEL
PARTITION BY LIST (AREA)
SUBPARTITION BY RANGE (THETIME)
(
  PARTITION AREAPART VALUES (DEFAULT)
  TABLESPACE TABLE_SPACE
  PCTFREE 10
  INITRANS 1
  STORAGE
  (
    BUFFER_POOL DEFAULT
  )
  NOCOMPRESS NO INMEMORY
  (
    SUBPARTITION AREAPART_THETIME VALUES LESS THAN (MAXVALUE)
    NOCOMPRESS NO INMEMORY
  )
);

CREATE INDEX C##USER.READ_INDEX1 ON C##USER.READ (DEVICEID ASC)
LOGGING
TABLESPACE TABLE_SPACE
PCTFREE 10
INITRANS 2
STORAGE
(
  INITIAL 65536
  NEXT 1048576
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
NOPARALLEL;

The tablespace Sql :
CREATE BIGFILE TABLESPACE TABLE_SPACE
    DATAFILE
        '~\TABLE_SPACE.DBF' SIZE 127301910528 AUTOEXTEND ON NEXT 8192 MAXSIZE 35184372064256
    BLOCKSIZE 8192
    DEFAULT NOCOMPRESS NO INMEMORY
    ONLINE
    SEGMENT SPACE MANAGEMENT AUTO
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;


texts: Query to send users message within a crm

texts:
I need help understanding the logic of a query I`m trying to write. I want the employees to be able to message other employees. I need the employee to see the list of all employees he can send messages to.

notifications table

id | time | fromUser_id | fromEmployee_id | fromEmployee_fname | fromEmployee_lname |to_Userid | message | employee_id

users table

id | username | email | password | employee |

employees table

id | employee_id | notifications_id | users_id | first_name | last_name | username

So far this is what I have in the select tag:

<select  id='toEmployee' class='form-control formBlock' name='toUser_id' value='' type='text' class='form-control formBlock'>
                 <option value="">To...</option>
                   <?php
    $employeesQuery = $db->query("SELECT * FROM employees");
    while ($employee = mysqli_fetch_assoc( $employeesQuery)) {
        ?>
  <option value="<?php ;?>"><?php echo $employee['first_name'] ;?> <?php echo $employee['last_name'] ;?></option>
    <?php } ;?>
</select>

I know it's not the correct query but I have hard time understanding the logic to make it work.


texts: What is the maximum number of rows in a clustered index on datetime column?

texts: I would like know what the max no of rows is in a clustered index (non-unique) on a datetime column table in SQL Server 2008R2.


texts:
I need to count days between dates. I have this query, that gets the date when an issue is resolved. The subquery selects the date when a issue is created.

The problem is that the subquery the dates of all opened issues instead of getting the one that has the same INCIDENCIA.ID_INCIDENCIA:

SELECT
  INCIDENCIA.ID_INCIDENCIA AS Incidencia,
  INCIDENCIA.DESCRIPCION,
  SITUACION.FECHA,
  SITUACION.FECHA - Inicio.Fecha AS Dias
FROM
  INCIDENCIA,
  SITUACION,
  (SELECT SITUACION.FECHA AS Fecha
    FROM SITUACION, INCIDENCIA
    WHERE SITUACION.ID_ESTADO = '1' AND INCIDENCIA.ID_INCIDENCIA = SITUACION.ID_INCIDENCIA) Inicio
WHERE
  SITUACION.ID_ESTADO = '3' AND
  INCIDENCIA.ID_INCIDENCIA = SITUACION.ID_INCIDENCIA
ORDER BY
  INCIDENCIA.ID_INCIDENCIA ASC

The expected result is:

╔═══╦══════════╦═══════════════════════╦═════╗
║ 1 ║ 13/10/16 ║ Averia en router      ║  30 ║
║ 2 ║ 16/11/16 ║ Fallo red             ║  28 ║
║ 3 ║ 22/10/16 ║ Solicitud de sala     ║  37 ║
║ 4 ║ 19/10/16 ║ Averia ordenador      ║   0 ║
║ 5 ║ 29/10/16 ║ Averia sin determinar ║   1 ║
║ 6 ║ 10/01/17 ║ Averia conexion       ║ 136 ║
╚═══╩══════════╩═══════════════════════╩═════╝

But instead I got:

╔═══╦═══════════════════════╦══════════╦═════╗
║ 1 ║ Averia en router      ║ 13/10/16 ║  30 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  -6 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  28 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  -6 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║ -15 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  47 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  64 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  28 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  62 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  28 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  19 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  81 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  39 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║   3 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  37 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║   3 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  -6 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  56 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  36 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║   0 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  34 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║   0 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  -9 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  53 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  46 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  10 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  44 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  10 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║   1 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  63 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║ 119 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║  83 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║ 117 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║  83 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║  74 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║ 136 ║
╚═══╩═══════════════════════╩══════════╩═════╝


texts: mysql database, 2400 records for 1500 rows

texts:
I need to create a new database, recording data for around 1500 sensors.

I need to store hourly data, and keep a rolling database for the past 100 days.

My first thought was to create a database with one row for each sensor (so around 1500 rows), and one column per hour - so 2400 columns.

Reading this other SO question (Too many columns in MySQL), this is clearly a bad idea!!

So how can I best organise and set up my table, to store 2400 data points for each of 1500 sensors, while creating a manageable table that is efficient?


texts: Remove/distinct duplicates on listagg - oracle

texts:
I'm using the oracle-xe 11g version and I have the below sql code that I aggregation the course code, but the output has duplicates.

SELECT
    b.alu_cpf            AS login,
    b.alu_nomeco         AS nome_completo,
    b.alu_emailpessoal   AS email,
    LISTAGG(d.cur1codigo, ', ') WITHIN GROUP (ORDER BY bdetcom.somentenumerosalfa(d.cur1codigo)) AS cod_curso,
    LISTAGG(d.cur1nomeco, ', ') WITHIN GROUP (ORDER BY bdetcom.somentenumerosalfa(d.cur1nomeco)) AS nome_curso,
    TO_CHAR(b.alu_sexo, '9') AS sexo,
    a.alu_codigo AS matricula,
    b.alu_dtnascimento,  AS data_nascimento
FROM
    matricula_curr_dis              a
    INNER JOIN aluno                b ON b.alu_codigo = a.alu_codigo
    INNER JOIN dis01                c ON a.dis1codigo = c.dis1codigo
    INNER JOIN CUR01                d ON a.cur1codigo = d.cur1codigo
GROUP BY
    b.alu_cpf,
    b.alu_nomeco,
    b.alu_emailpessoal,
    a.alu_codigo,
    b.alu_sexo,
    b.alu_dtnascimento
ORDER BY
    nome_completo

Data:

login | nome | email  |      cod_curso
12345  fulano  xxxx     cod1, cod1, cod1, cod2

How I can distinct the cod_curso so it can look like this?

login | nome | email  |  cod_curso
12345  fulano  xxxx     cod1, cod2


texts:
How can I stop a truncate of a MySql innodb?

When I restart the database it tries to continue the truncating which leads to the problem of the log group capacity exceeding. To increase this limit it is necessary to shut down the db clean which is not possible because it always tries to start the truncation again.

As a last resort I can go into recovery mode 6 but want to avoid it cause of the high possibility of corrupted data.

mysql error log:

2019-11-21T08:11:36.399109Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 22770004328925
2019-11-21T08:11:36.401772Z 0 [Note] InnoDB: Database was not shutdown normally!
2019-11-21T08:11:36.401778Z 0 [Note] InnoDB: Starting crash recovery.
2019-11-21T08:11:36.405507Z 0 [ERROR] InnoDB: The age of the last checkpoint is 90601444, which exceeds the log group capacity 90593280.
2019-11-21T08:11:36.524771Z 0 [Note] InnoDB: 106 transaction(s) which must be rolled back or cleaned up in total 106 row operations to undo
2019-11-21T08:11:36.524799Z 0 [Note] InnoDB: Trx id counter is 31970042624
2019-11-21T08:11:36.524806Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2019-11-21T08:11:41.069233Z 0 [Note] InnoDB: Apply batch completed
2019-11-21T08:11:41.069274Z 0 [Note] InnoDB: Last MySQL binlog file position 0 848320890, file name mysql-bin.006993
2019-11-21T08:11:41.200977Z 0 [Note] InnoDB: Completing truncate for table with id (1988) residing in file-per-table tablespace with id (2019)
2019-11-21T08:11:58.338120Z 0 [ERROR] InnoDB: The age of the last checkpoint is 90622848, which exceeds the log group capacity 90593280.


texts:
Today I discovered something weird on PostgreSQL 9.5. (I have no idea whether this is because of beta or not.) When I want to fetch data, I get old and deleted data from query. I then do VACUUM FULL and then I get proper data (which is empty).

Am I missing something here? What might be the reason that PostgreSQL returns old data?

Note: Autovacuum is ON.


texts:
CREATE DATABASE `menu`;

USE menu;

DROP TABLE IF EXISTS `fish`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’,
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
LOCK TABLES `fish` WRITE;

INSERT INTO `fish` VALUES
(1,’catfish’,’8.50’),(2,’catfish’,’8.50’),(3,’tuna’,’8.00’),
(4,’catfish’,’5.00’),(5,’bass’,’6.75’),(6,’haddock’,’6.50’),
(7,’salmon’,’9.50’),(8,’trout’,’6.00’),(9,’tuna’,’7.50’),
(10,’yellowfin tuna’,’12.00’),(11,’yellowfin tuna’,’13.00’),(12,’tuna’,’7.50’);

UNLOCK TABLES;

I'm currently reading this book on how to use MySQL with python and would like to know why this query from the book is not currently working.( MySQL version 5.6 and workbench 6.2 in Windows 7)
Things I have tried

Typing in the code by hand
Copying and pasting from the pdf into notepad++, workbench and notepad
I copied the file into notepad and saved it in different encoding such as UTF-9

It is probably something simple I am missing, but I am new to this.
The error message get when running the query.

11:25:50  CREATE TABLE fish ( ID int(11) NOT NULL auto_increment, NAME varchar(30) NOT NULL default ‘’, PRICE decimal(5,2) NOT NULL default ‘0.00’, PRIMARY KEY (ID) ) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘’, PRICE decimal(5,2) NOT NULL default ‘0.00’, PRIMARY KEY (ID) ) E' at line 3  0.000 sec


texts: Using X at the end of a SELECT in SQL Server Script?

texts:
I'm relatively new to T-SQL and looking through my predecessors scripts, I found a line I can't quite parse. It's a hard thing to look up on my own. Hoping for advice.

This line occurs inside a WHILE loop, but I don't think that matters. I believe functionally, that this is gathering the values from a column across a few rows that share an ID and concatenating them with commas into 1 field. But I don't know what this is called or how to look it up to use it myself.

What is this 'x' doing at the end? Can anyone point me to information on this?

(select @invtid = coalesce(@invtid + ',',' ')+rtrim(RoleID)
FROM (
    Select distinct roleid
    from UserAccounts
    WHERE CustID = @custID
     )
 x)

Thanks.


texts: Which hosting technology is suitable for large data with limited index

texts:
I have a scenario where I have to host very large amount of data in database type solution. The amount of data is very very large. When I try to host it in mysql database the size increases as mysql adds pre calculated information to the data. Which solution will be suitable for this type of data. Keep in mind

I do not need any complicated functions such as average, sum, distinct etc.
I only need 2 columns to run where clause to pull the data. I plan to do the rest in the GUI
The amount users are very limited (less than 100) nationwide
Most of the data is integers. Some of them are int64. It is in a tabular form. Only two column one being dataTime type.


texts: DBI1011I Error when using db2iupdt to enable Text Search

texts:
I'm trying to enable Text Search in my DB2 instance. I'm running this command:

# /opt/ibm/db2/V10.5/instance/db2iupdt db2inst1 -j "TEXT_SEARCH,db2j_db2inst1,55000"

I get this error:

DBI1011I  An invalid argument was entered for the db2iupdt command.

Explanation:

You can use the db2iupdt command to update a DB2 instance to a higher
level within a release, convert a DB2 instance to a DB2 pureScale
instance, or manage DB2 members or cluster caching facilities (CFs) of a
DB2 pureScale instance.

This message is returned when the db2iupdt command is invoked with
incorrect or missing parameters.

User response:

Enter the command again using valid parameters.

   Related information:
   db2iupdt - Update instances command
   Updating an instance to a higher level within a release using the
   db2iupdt command
   Updating an instance to a DB2 pureScale instance with the db2iupdt
   command
   Dropping a member or cluster caching facility
   Adding new members or an additional cluster caching facility

DBI1011I documentation just says there's something wrong with the parameters. I'm not seeing anything obviously wrong with them when looking at the documentation for db2iupdt.

I'm running DB2 10.5:

$ db2licm -l
Product name:                     "DB2 Advanced Workgroup Server Edition"
License type:                     "CPU Option"
Expiry date:                      "Permanent"
Product identifier:               "db2awse"
Version information:              "10.5"
Max amount of memory (GB):        "128"
Enforcement policy:               "Soft Stop"

How do I successfully enable Text Search in my DB2 instance?


texts: Guid vs INT - Which is better as a primary key?

texts:
I've being reading around reasons to use or not Guid and int.

int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and int and why?

From what I've seen, int has no flaws except by the number limit, which in many cases are irrelevant.

Why exactly was Guid created? I actually think it has a purpose other than serving as primary key of a simple table. (Any example of a real application using Guid for something?)

( Guid = UniqueIdentifier ) type on SQL Server


texts: Entity-Relationship Diagram for a scenario involving Books, Authors and Countries

texts:
Certainly a noobish question, but I'll ask it anyway. Perhaps someone else will be thankful. ^^

I am getting warmed up with MySQL and I try to understand the concepts of creating well structured databases. Currently I am reading about Entity-Relationship Diagrams (ERD) and I asked myself:

If I have three tables which are linked by a forth one, for example: A book written by one ore more authors, and each authors and was published in one ore more countries.

authors          books            countries
id | name        id | book        id | publishedInCountry
 1 | ...          1 | ...          1 | ...
 1 | ...          2 | ...          2 | ...
 1 | ...          2 | ...          3 | ...
 4 | ...          1 | ...          3 | ...

linkTable
idOfAuthor | idOfBook   | idOfCountry
 1         | 1          | 1
...

How would an ERD look like for these ones?

Additional information

I thought of:

[authors] -- < is author of > -- [books] -- < was published in > [countries]

But how do I fit in the linkTable?


texts:
I'm running SQL Server 2008 Express and hitting the 4GB size wall.  I'd like to make some space to give me time before i migrate to a larger DB.

I don't need my transaction log.  So, can i delete my log to increase space?

Otherwise, i'll have to create a duplicate DB structure, and then copy the data into the new DB and then delete everything from the original DB so i can start over.

In this case, is there an "easy way" to copy the data from the old tables into new tables?


texts:
I'm working now on the implementation of the following feature:
Say I have a collection in mongodb with millions of entries (very big say 100M), each entry is of type

{"uniqId", "properties"}

for example:

 {"uniqId" : "u1" , "properties" : ["p1", "p2"]}

I have a runtime component that should read the data by uniqId, and some batch process that should insert the data into this collection.
There is huge amount of reads and very rare updates (once in a week)

The problem is that i'll get always the full file (that could be very big milions of lines) that i should insert.

The solution i think about is as follwing:

There will be unique index on "uniqId" field.
I'll have a timestamp field on each document
I can use upsert operation to override the existed entries
Each operation will be a bulk of some valuable amount (say 1000 documents)
There will be a batch process that will remove the documents with 'old' timestamp

There is another solution:

Each time create new collection and then swap between the new and the old one
(but if i'll use sharding i saw that mongo does not support rename in that case)

Is this design seems to be OK?
How it can influence on the mongo performance?

I have the mongo with replica set configuration, and i did not used sharding yet.

Appreciate any valuable ideas,


texts: I have a Java system with Postgres 8.3 and Glassfish application server, from time to time, the application is very slow at certain times - when it has more hits; by the top command, we can see the increase of cpu consumption by the user postgres, in relation to memory the consumption is normal, I've done tunning, I'm logging the queries that take longer to be executed and optimizing, but nothing solved, so I am here asking for your help, who knows and can give me any tips, I thank you in advance.


texts: Is a SQL query still called a "query" when doing a deletion, update or insertion?

texts:
I'm not sure what to call a query that... isn't really a query; is "statement" a better word to use in place? Or how about "script"?

Or would something else be more appropriate?

Note: I've seen someone call it a "DELETE query" once


texts: Error moving SQL Server 2000 to SQL Server 2005 NOLOCK Error

texts:
I need to move a database from the  SQL Server 2000 (32 bit) to SQL Server 2005.But,When I attacted my database to SQL Server 2005,it's gives me error.(NOLOCK error)

Also , After I running dbcc check db command for database errors,I am receiving the following error.

Server: Msg 8929, Level 16, State 1, Line 1

Object ID 2: Errors found in text ID 727515136
owned by data record identified by RID    =   (1:391:14) id = 491864819 and indid = 9.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 727646208
owned by data record identified by RID = (1:391:16) id = 491864819 and indid = 11.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 768802816
owned by data record identified by RID = (1:2243:15) id = 1796253504 and indid = 54.
Server: Msg 8929, Level 16, State 1, Line 1

CHECKDB found 0 allocation errors and 16 consistency errors in database 'XXXXX'.
DBCC execution completed. If DBCC printed error messages,
contact your system    administrator.


texts: "The server principal is not able to access the database under the current security context" - Availability Group

texts:
We are experiencing this error intermittently:

  The server principal is not able to access the database under the current security context

The database is part of an Availability Group.

Context : A trigger is fired on "Operations" database, which records entry in ChangeHistory database whenever a record is changed in Operations.

We literally have thousands of records working every minute with no issues. It happens so intermittently that it is very difficult to pinpoint.
E.g. it could happen for a 20 minute period now - and then again only in 2 day's time and for only 4 minutes.

I believe that although the error message is what it is, that there is a different issue giving the same error message.

I wonder if it could be that the sys.fn_hadr_backup_is_preferred_replica for
Operations and ChangeHistory are out of sync?
E.g.

DBInstance1 Operations is_preferred_replica=1,ChangeHistory preferred_replica=0.
DBInstance2 Operations is_preferred_replica=0,ChangeHistory preferred_replica=1.

The DBInstance1 which is active , would then try to write to its own ChangeHistory database, which is now not the preferred instance and would generate some sort of error - even though it does not match.

Would this be possible - different DBs with different preferred replica statuses on one server?

If it is, would this be the error that is displayed?


texts: Food Delivery Service Database Design Questions

texts:
For a school Food Delivery application I am developing, I’m facing some problems during the ER model design stage. I have to fulfil many requirements but my main issues I’m having are the following:

A)

Each order’s food items must be from a single restaurant, and each order is assigned to a delivery rider who will collect and deliver the ordered items to the customer.

I’ve learnt about ternary relationships and my current design doesn’t seem to be enforcing the constraint in (A). I’m thinking if a ternary constraint connecting Restaurant to “Ordered In” will be a better design but as I’m not familiar with refining schemas, I don’t know if it’s a good design decision.

B)

Each full-time rider maintains a monthly work schedule (MWS) while each part-time rider maintains a weekly work schedule (WWS).
In the FDS application, a week is defined to be a duration consisting of seven consecutive days, and a month is defined to be a duration consisting of four consecutive weeks.
Each WWS specifies the hour intervals that the rider is available for work each day.
For example,  a rider’s WWS might specify the following work hours for six work days: for Monday to Thursday, the rider works for the hours 10am to 1pm, 4pm to 6pm, and 7pm to 10pm; and for Saturday and Sunday, the rider works for the hours 10am to 1pm and 5pm to 8pm.

What would be a good way to differentiate between a part-time weekly work schedule and full-time monthly work schedule, which is a duplicate of 4 identical weekly work schedules, the exact wording of my requirements are all shown in (B).

I follow my notation rather similarly to those quoted in R. Ramakrishnan & J. Gehrke, Introduction to database design, Database Management Systems, chapter 2. McGraw Hill, third edition, 2003. Do feel free to ask for more clarification