texts:
I know there are similar question to this one, but I couldn't find one dealing with Index Maintenance...
I have some PK Clustered Indexes [int (auto increment).] I always notice that these Indexes frequently have two issues:

Fragmentation is quickly over 10%
Fragmentation is sometimes over 90%

Do I need to apply the same logic (REBUILD/REORGANIZE) to these indexes?
This is how I'm retrieving the fragmentation:
SELECT dbschemas.[name] as 'Schema',
                    dbtables.[name] as 'TableName',
                    dbindexes.[name] as 'IndexName',
                    indexstats.avg_fragmentation_in_percent as 'AvgFragmentationInPct',
                    indexstats.page_count
                    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
                    INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
                    INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
                    INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                    AND indexstats.index_id = dbindexes.index_id
                    WHERE indexstats.database_id = DB_ID()
                      AND indexstats.avg_fragmentation_in_percent >= 8
                      AND indexstats.page_count >= 100
                    ORDER BY indexstats.avg_fragmentation_in_percent desc


texts: data guard / network failure: deadline before global crash?

texts:
In a Maximum Availability scenario where there's one primary and one physical standby database, how long can we expect the system to run without problem when the standby site becomes out of reach (example: network error, disaster on the B site etc.)

The log archive dest state setting must be set to DEFER. But does it simply mean everything will roll fine until my local hard drive (primary database) explodes the +REDO area?

Is a simple anticipation enough to calculate the "autonomy" of the primary database or is there a way to guarantee it?


texts: AlwaysOn Availability Group: Users on Secondary Server

texts:
We have an AlwaysOn Availability Group setup (DB1+DB2) and also a web server (Web1) outside of the domain that we need to connect to the AG using Windows Authentication.

The following Windows Users/SQL logins have been set up:

Web1
Windows: Web1\User

DB1 [AG Primary]
Windows: DB1\User
SQL Login: DB1\User
SQL User: DB1\User (from DB1\User login)

DB2 [AG Secondary]
Windows: DB2\User
SQL Login: DB2\User

Because the secondary is read-only I cannot add the corresponding DB2 SQL User until we failover to it, and this is impractical due to the frequency we add users.

Is there a way on the DB1 primary to add DB2\User pointing to DB2\User login? Or a better way of doing this while still using Windows Authentication.


texts:
I have around 100 SQL Server Reporting Services subscriptions that I need to change the schedule on.  The schedules are all standalone, not shared.

Is there a way that I can easily bulk change the report schedules?

Moving them all to handful of common shared schedules would also be acceptable, but again I'd rather not have to do it manually.

I've been experimenting with Powershell to talk to the Reporting Services web service, but it seems far from straightforward to edit schedules, so I'm hoping that there's an easier way.


texts: MySQL silently replaces UTF chars with literal question marks

texts:
I'm experiencing a situation similar to this SO question i.e. I'm working with a legacy database which has UTF8 content in latin1 tables (pretty ugly I know).

Now I'm getting new data from a new application which is completely utf8 and works with its database. To support other legacy system the application also writes a copy of its utf8 data in legacy tables. As far as I know it should be possible to write utf8 stuff in latin1 tables as long as you read it back and show it those data as UTF8. There are a lot of tutorials explaining how to fix this situation for the long term but I'd prefer to not apply them unless absolutely necessary (legacy system will be dismissed soon and I don't want to have downtimes to fix this if possible)

Here is a minimal SQL script which reproduces my issue:

CREATE TABLE `articles` (
  `content` mediumtext NOT NULL,
  FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET NAMES utf8;
SET CHARACTER SET utf8;
-- Turkish word for Croatia, second char is \xC4\xB1
INSERT INTO `articles` (`content`) VALUES ('Hırvatistan');

In my system I get no errors from MySQL but after the INSERT statement second char of the word is silently dropped and replaced by a literal ? ('\x3F').

mysql> SELECT content, HEX(content), HEX('Hırvatistan') FROM articles;
+-------------+------------------------+--------------------------+
| content     | HEX(content)           | HEX('Hırvatistan')       |
+-------------+------------------------+--------------------------+
| H?rvatistan | 483F72766174697374616E | 48C4B172766174697374616E |
+-------------+------------------------+--------------------------+

However if I paste the same script on http://sqlfiddle.com/ I get an error when I press 'build schema' which states:

Incorrect string value: '\xC4\xB1rvat...' for column 'content' at row 1

Why on my system the invalid utf8 char is simply dropped and I get no errors? Is there any mysql config value to enable in order to avoid this?

Which is the simplest way to allow any kind of char inside my current latin1 (with utf8 content) table? I have a lot of content and I'd prefer to avoid solutions like dump the content and reimport with other charset


texts: Changing the use of GETDATE() in the entire database

texts:
I need to migrate an on-premises SQL Server 2017 database to an Azure SQL database, and I'm facing some challenges since there's quite a bit of limitations to go through.

In particular, since an Azure SQL database works only in UTC time (no time zones) and we need the local time, we have to change the use of GETDATE() everywhere in the database, which has proven to be more work than I anticipated.

I created a user defined function to get the local time that works correctly for my time zone:

CREATE FUNCTION [dbo].[getlocaldate]()
RETURNS datetime
AS
BEGIN
    DECLARE @D datetimeoffset;
    SET @D = CONVERT(datetimeoffset, SYSDATETIMEOFFSET()) AT TIME ZONE 'Pacific SA Standard Time';
    RETURN(CONVERT(datetime,@D));
END

The issue I'm having trouble with is to actually change GETDATE() with this function in every view, stored procedure, computed columns, default values, other constraints, etc.

What would be the best way to implement this change?

We are in the public preview of Managed Instances. It still has the same issue with GETDATE(), so it doesn't help with this problem. Moving to Azure is a requirement. This database is used (and will be used) always in this time zone.


texts: Optimize MySQL insertion - insert multiple records with condition (per record)

texts:
although not new to SQL, up until now I used the basic stuff - basic crud, joining tables etc. I have a special case now where I'm expecting a lot more insertions that goes something like this (pseudocode):
foreach(id_of_student in students){
    {class} = students[id_of_student].class;
    {grade} = students[id_of_student].grade;
    INSERT INTO grades (id_of_student,class,grade) VALUES({id_of_student},{class},{grade})
    WHERE grade.notSetManually=1 ON DUPLICATE KEY UPDATE
}

Of course I would like to not run it as a loop and access the db 50 times for each grade sheet (my app checks up to 50 grade sheets per minute). Can this be done in one big query, in a more optimized way? I saw that there is LOOP in mysql, but is it just syntactic sugar when it comes to the burden on the DB?

Another thing - this ON DUPLICATE KEY UPDATE means that as long as my key is {id_of_student},{class}, this will update and not add new line, right?


texts: Can I Rebuild indexes in a DB except for one table?

texts:
I have a table in my database that is constantly monitored by an application server for changes. Unfortunately rebuilding the indexes connected with this table breaks the app server's connection. This causes print jobs and various other processes to fail silently on the application server.

I have created a small monitoring SQL job that lets me know when this happens, but I would prefer to prevent it from happening in the first place.

I need to rebuild indexes weekly for performance reasons, but I would be fine with skipping the indexes associated with that table.

How can I rebuild indexes for the rest of the database without touching that table?

Edit: I am currently using an SSIS job (the canned maintenance plan) to rebuild all indexes for all tables in the DB. After that it updates stats and checks integrity.


texts: Trying to find out what a specific running statement is (Oracle)

texts:
I'm trying to look into what's running on my Oracle instance at the moemnt.  I have a large INSERT statement going that's dumping data into a table, and I can see this is running when I execute the SQL script referenced in this post:

How do you show sql executing on an oracle database

It's showing my INSERT statement fine, but it's also showing a second statement that looks like a trigger or a stored procedure that's not referenced in the insert statement: mp_manual_pay. This is an abbreviation of the table I'm inserting data into.

I can't find this label referenced in a trigger or a stored procedure, so I'm wondering what else this might be?  I've searched sys.all_objects and cannot see any reference to this label / name.

Any ideas on where else to search to see what exactly this is?

I expect it's the cause of a rather slow insert, and I would like to temporarily disable this during the bulk insert.

I have also executed the script referenced in this post:

How to see the actual Oracle SQL statement that is being executed

And this certainly seems to indicate that something else is going on, but I can't for the life of me figure out where it's coming from.


texts: How to understand the PostgreSQL discrepancy between connection pool recommendation ((2 * n_cores) + n_disks) and support for 100s of connections?

texts:
From PostgreSQL docs:

Number of Database Connections -> How to Find the Optimal Database Connection Pool Size

  for optimal throughput the number of active connections should be
  somewhere near ((core_count * 2) + effective_spindle_count)

Tuning Your PostgreSQL Server -> max_connections

  Generally, PostgreSQL on good hardware can support a few hundred
  connections.

For me - not an experienced DBA -  there's a discrepancy somewhere in here, especially looking at the offerings of some DB-as-a-Service providers.

For example,  at this time Amazon RDS's largest machine (db.r3.8xlarge) has 32 vCPUs, which according to the first formula would perhaps manage to run optimally with 100 connections in the pool, given many disks. Wouldn't it though run very badly with the "few hundred connections" from the second formula?

Even more extreme is the discrepancy for another DBaaS provider, who proposes a 2 core server with 500 concurrent connections. How could this possibly work well?

If I'm misunderstanding something, please let me know. Many thanks!


texts:
Thanks in advance for your answer. I am trying to tune my db skills.

I have 3 tables:

Brand, Model and Item.

All models belongs to a brand, but an item can have a brand but not a model.

I have 2 questions:

From a programming point of view I just add brandId as foreign key and ModelId just as a field to Item table.But from database design perspective, I guess this is the wrong approach. How I should manage it?
In the case that an Item always have a model and brand. I usually only add modelId and from it I can get brandId, but I have been seeing a growing wave where both fields would be added to item table, again, what is the correct approach?


texts: Does MySQL updated index if index key is not updated?

texts:
Say I have a table with 3 columns: ID (PK), Name, Age.

Say ID has an index on it, name has an index on it, and there is another index which combines id and name. Lets say I now have an update statement which looks like this:

UPDATE table SET AGE = 69 WHERE id = 'some_id';

I am only updating the AGE column, so will MySQL still update the indexes even though non of the indexed columns were modified or will it leave the indexes alone?


texts: What is log sequence number ? How it is used in MySQL?

texts: Can anybody give an example or scenario of log sequence number(LSN) used in MySQL


texts: Converting an SVN repository to SQL database

texts: I have downloaded the svn repository of Atunes using TortoiseSVN. I would like to transfer the logs of activity of the developers to a SQL database, preferably an Oracle one. Is there any software that can help me do this?


texts: how to sync local mysql database and live server database?

texts:
My plan is to store data in my local MySQL database and after a time interval it will update my live MySQL database remotely. Is it possible?

I'm planing a inventory management script in PHP MySQL.  The real problem is in our country net connection drops suddenly. So I want to first write to the local MySQL database and then it will update the real server.  In any situation like internet connection dropped the script will work locally to gather data and retrieve data. When the internet connection comes back it will update the data to the real server. I want to protect against internet connection failures.

Nothing will update the real server in the mean time because the users are only in the office where the internet connection will drop.  No one from the outside will able to update the database. Only the information can be retrieved from the live server to the shop's owner.


texts: Is bulk-logged recovery model ever a good choice if point-in-time restore is not needed?

texts: Is the Bulk-Logged recovery model in SQL Server ever preferable to Simple if point-in-time restore is never needed? I have read many articles about transaction log models, but cannot find a clear answer to this. The database in question has a lot of activity during nightly loading, but the data doesn't change after that. I am doing a full backup daily. Wouldn't Simple be the best choice here, while also potentially using less storage?


texts: SQL Server Audit File Metrics Information

texts:
I'm currently working in an environment that requires we enable SQL Server Audit, enterprise-wide.  We generate .sqlaudit files as we have retention and access guidelines, and I'm finding that we need to compile trending information about the files generated, such as number created per hour, total volume of audit information, etc.  Currently, I'm only aware of DMVs/DMFs that provide audit definition information (the list can be found here), but nothing related to the output of the SQL Server Audit process.

I'm currently running PowerShell scripts to collect this information from the metadata of the .sqlaudit files, but I'd prefer not to rely on an external process, if possible.  I've looked into extended stored procedures such as xp_dirtree, but that only returns the number of files and not size information, so it doesn't provide as complete a picture as I need here.  xp_cmdshell, while viable, is not allowed for obvious security reasons and because NIST compliance explicitly prohibits its use.
 Are there any approaches within the database engine (I'm asking about things I can hit with TSQL) where I can get this information, such as an Extended Event or hidden DMV I've not yet come across?  I feel like I've done some extensive digging, but I'm never surprised if I miss something.


texts:
This is strictly a test system.
Command I'm executing is:

expdp XXXXX/XXXXX schemas=someSchema directory=DUMPDIR dumpfile=dump.dmp logfile=explog.txt

Environment settings:

ORACLE_SID=ORCL
ORACLE_HOME=/.../dbhome

Error seen is:

UDE-12546: operation generated ORACLE error 12546
ORA-12546: TNS:permission denied

Mystifying since I was testing it with the root user on the OS and the system user on the Database. Clearly there is something very basic wrong somewhere.

This is running in a docker container.


texts:
I have a foreach container which is looking in a share directory for files to then send on to another location.  When I ran this as part of a job the package was running successfully however the files were not being sent.  The reason was because the agent account needed access to the share.

My question is how can I get the package to fail should it not have permissions to access the directory?

Thanks


texts: How do triggers and check constraints relate to maitaining database rules

texts:
I was looking at a tutorial on triggers for SQLite. The tutorial said that a use of triggers could be for data validation.

The following code was given as an example:

CREATE TRIGGER validate_email_before_insert_leads BEFORE INSERT ON leads
BEGIN
    SELECT
    CASE
        WHEN NEW.email NOT LIKE '%_@__%.__%' THEN
        RAISE (ABORT, 'Invalid email address')
    END;
END;

My first inclination would be to use a check constraint for validation instead. I did a Google search for situations where one should use a check constraint and when a trigger would be preferable.

Question
My question is, what specific situations should one use a check constraint and what situations should one opt to use a trigger?


texts: Restoring a managed SQL instance in Azure using PowerShell

texts:
I am trying to figure out how to restore a database from one managed SQL instance to another. I'm following the tutorials, but I keep running into inscrutable error messages.

Here's my command:

Restore-AzSqlInstanceDatabase `
    -Name "SomeDatabase" `
    -InstanceName "our-oltp-dev" `
    -ResourceGroupName "dev-managedsqlinstances" `
    -PointInTime "4/7/2020 12:00:00" `
    -TargetInstanceDatabaseName "SomeDatabase_FROM_DEV" `
    -TargetInstanceName "our-oltp-sandbox" `
    -TargetResourceGroupName "sandbox-managedsqlinstances"

Here's the output:

PS C:\WINDOWS\system32> Restore-AzSqlInstanceDatabase `
    -Name "SomeDatabase" `
    -InstanceName "our-oltp-dev" `
    -ResourceGroupName "dev-managedsqlinstances" `
    -PointInTime "4/7/2020 12:00:00" `
    -TargetInstanceDatabaseName "SomeDatabase_FROM_DEV" `
    -TargetInstanceName "our-oltp-sandbox" `
    -TargetResourceGroupName "sandbox-managedsqlinstances"
Restore-AzSqlInstanceDatabase : Parameter set cannot be resolved using the specified named parameters.
At line:1 char:1
+ Restore-AzSqlInstanceDatabase `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Restore-AzSqlInstanceDatabase], ParameterBindingException
    + FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.Azure.Commands.Sql.ManagedDatabase.Cmdlet.RestoreAzureRmSqlManagedDatabase

It's a copy-and-paste from the Azure docs; so I'm not sure what I'm doing wrong. Any help would be appreciated.


texts: Failover Cluster Installation in SQL Server 2017 Developer Edition

texts:
Can we install SQL Server failover cluster in SQL Server 2017 Developer Edition without WSFC?

I want to practice on Always-on and how it works, which is why I want to install SQL Server failover cluster, however I have not created the WSFC.

Is it possible?


texts: How to check if restored database is the same as original

texts: Is there a way to check if restored backup of Oracle database is the same as the original database? Is there a simple way to check it if I have the original database?


texts: Granting permissions only on a set list of objects

texts:
I have a SQL Server 2005 database with a large number of tables in the dbo schema. I now created a new schema (call it myschema) that only has three table-valued functions and two stored procedures in it. All of that code has access to the tables in dbo.

The code in myschema will ultimately be called from a web service and I am struggling to get the permissions right for the user I created for the web service.

At first, I created the user with no roles except public and then gave it specific permissions on the securables in myschema. But then I could log on using that user and select from (and ever update) anything in dbo.

So I gave the user the denydatareader and denydatawriter roles, which effectively restricted the access to the objects in dbo.

The result of this is that I can execute the two stored procedures just fine.

But if I try to use the table-valued functions, I get this error:

  The SELECT permission was denied on the object 'MyFunction', database
  'MyDB', schema 'myschema'.

This is despite my use of:

grant select on myschema.MyFunction to MyUser

I'm guessing that's because of my brilliant use of denydatareader.

So what is the correct way to give a user access only to a list of specific stored procedures and table-valued functions and not to anything else?

Update:
Based on Nico's answer below, here's what I did. For the sake of brevity, just pretend you see the exists checks.

create table dbo.testtable(id int)
go
insert into dbo.testtable (id) values (1)
insert into dbo.testtable (id) values (2)
insert into dbo.testtable (id) values (3)
go
create schema myschema
go
create login mylogin with password = 'SomePassword', check_policy = off, default_database=[MyDatabase], default_language=[us_english];
create user myuser for login mylogin with default_schema = [myschema];
create role myschemarole;

execute sp_addrolemember N'myschemarole', N'myuser';

Then logged in with mylogin:

-- Error: The SELECT permission was denied...
select * from dbo.testtable

-- Error: The SELECT permission was denied...
update dbo.testtable set id = 4 where id = 2

-- Success: (3 row(s) affected)
update dbo.testtable set id = 4

-- Success: (1 row(s) affected)
insert into dbo.testtable (id) values (5)

As long as I don't have a where clause, I can insert and update to my heart's content. And on top of that, "use master" also works and the sys schema is also fair game.

Does this mean I still need denydatawriter?


texts: Select with if statement return 'column does not exist'

texts:
Database, application.
PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1),
Dbeaver 6.1.1.

What I need?
Return right values.

What I do.
Here is part of code.

select
if(available_count < 5, "neni skladem", "na sklade") as availability
from
...

Return this.

SQL Error [42703]: ERROR: column "neni skladem" does not exist

Where is the catch?

Thanks.


texts: Is MariaDB easier and better suited for a WordPress website?

texts:
I've been having problems with MySQL 8.0.20 on Ubuntu 18.04, and because I don't have a lot of knowledge on databases, I was thinking on switching to MariaDB as I heard it has better settings and performance out of the box.

So:

Is MariaDB a better solution for someone who is still learning database?
Can I just bring the MySQL database to MariaDB without any conversion or anything else (just drag and drop)?
Can I run mariadb with apache 2.4 and php 7.4?


texts: Alternate to use LIMIT in subquery in mysql?

texts:
I have 2 tables, Resource and views and I'm trying to get the list of all the items from Resource table whose ran_resId don't match with the ran_resId of last 2 records of views table. I'm trying to use LIMIT in my sub-query which is giving this error:

  This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'`

Here's my query -

select r.res_id, r.res_name
from Resource AS r
where r.ran_resId NOT IN
      ( select v.ran_resId
        from views AS v
        order by v.viewsid
        limit 2
      )
group by r.ran_resId

I've google it and found out that LIMIT can't be use in SubQuery in MySql, And as an alternate I could use JOIN however I'm not able to create the query to get the desired results.
A help is highly appreciated.
PS - I'm using MySql 5.6


texts: Database architecture for interactions between users (8B rows, 1TB of data)

texts:
I'm saving an interactions table between two users. I have two interaction types, and each interaction can happen more than once. The easiest reference would be Facebook Likes and comments on posts: User1 did two likes, and 1 comment on User2.

I would also like basic query abilities, like all users that interacted with User1, and users User1 has interacted with sorted by how many interactions.

I'm currently saving this in MySQL, and things have been going well, but as the table is getting big (7.5B rows, 1TB total disk space divided equally between data and indexes), INSERTS have become very slow. Can take up to a minute for 1k rows.

My Current Table Structure:

 CREATE TABLE `interactions` (
  `user_id_to` bigint(11) unsigned NOT NULL,
  `user_id_from` bigint(11) unsigned NOT NULL,
  `type2counter` tinyint(11) unsigned NOT NULL DEFAULT '0',
  `counter` smallint(11) unsigned NOT NULL,
  `updated` date NOT NULL,
  PRIMARY KEY (`user_id_to`,`user_id_from`),
  KEY `uid_from` (`user_id_from`),
  KEY `uid_to_counter` (`user_id_to`,`counter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Edit: Insert look like this:

INSERT INTO interactions
     (user_id_to, user_id_from, counter, updated)
   VALUES
     ('2266931634','3180832729','3',NOW() ),
     ('2266931634','3133897242','1',NOW() ),
     ('2266931634','3207247957','1',NOW() ),
     ('2266931634','3520390476','1',NOW() ),
     ('2266931634','3631954079','1',NOW() ),
     ('2266931634','3687475949','1',NOW() ),
     ('2266931634','3627629761','1',NOW() ),
     ('2266931634','3779990751','1',NOW() ),
     ('2266931634','4071753134','1',NOW() ),
     ('2266931634','4290900946','3',NOW() )
    ON DUPLICATE KEY UPDATE
             counter=counter+VALUES(counter),
             updated=NOW()

MySQL version: 5.6.19


texts: What's the correct syntax for this Postgres query in MySQL?

texts:
I have a  query which works well in our PostgreSQL db, but we now need to run our app on a MySQL DB as well.

The query takes an array of IDs and positions, and updates each record with given ID with a new value for the respective position.

  UPDATE items
    SET position = i_vals.position
    FROM (VALUES (1,12), (2, 13), (3,11)) AS i_vals(id, position)
    WHERE i_vals.id = items.id;

How can I rewrite this query to be MySQL and SQLite3 friendly? As a bonus, I'd like to be able to also run in PostgreSQL with the same query, but appreciate that might not be possible


texts: Data not reflecting after pg_upgrade in expected database

texts:
My Postgres v10.11 has data_directory at '/post10/data' location and has a tablespace at location /xyz for my 'db1' database.
I have tried upgrade of Postgresql database (version 10.11) to Postgresql (version 12.4).
And for Postgres v12.4 I am using default tablespace (pg_default).
Command used to upgrade :
$PGHOME_12/bin/pg_upgrade -b old_bin_10 -B new_bin_12 -d old_data_dir_10 -D new_data_dir_12 -k -v

After successful upgradation using pg_upgrade utility, 'db1' database is available but there is no relation in 'db1' , rather all the tables of 'db1' are in 'postgres' database. Look here the size of postgres database:-
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 db1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8345 kB | fastspace  |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 43 MB   | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8217 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+| 8345 kB | pg_default | default template for new databases
           |          |          |             |             | =c/postgres           |         |            |
(4 rows)

Though this is not production server but after successful testing here I have to do the same on production server. I want all data to be reflected under 'db1' in pg_default tablespace. I am stuck now how to do this.
Problem statement : Application will connect to 'db1'. But db1 has no relation in upgraded postgresql. Earlier in v10, db1 had many relations. All those relation are now in 'postgres' database not in db1.


texts: Parsing Typed XML with a default namespace and inconsistent root node

texts:
I have a structured, typed xml file.

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
 <NewLeads>
  <OutputSchema>
  <root xmlns="" type="array">
   <item type="object">
   <SaleProperty type="object">
    <Type type="string">Freehold</Type>
    <PostDistrict type="string">xxx</PostDistrict>
    <Address type="string">address</Address>
     <Value type="number">17.0</Value>
   </SaleProperty>
   <Transaction type="string">SaleOnly</Transaction>
   <Quote type="object">
    <Sale type="object">
     <Fees type="number">450.0</Fees>
     <Disbursements type="number">0.0</Disbursements>
     <StampDuty type="number">0.0</StampDuty>
     <Total type="number">450.0</Total>
    </Sale>
   <Discount type="number">0.0</Discount>
   <VAT type="number">90.0</VAT>
   <Total type="number">540.0</Total>
  </Quote>
  <MoverId type="number">12345678</MoverId>
  <Name type="string">Mr AS</Name>
  <Email type="string">as@yahoo.ppp</Email>
  <Telephone type="string">0123456789</Telephone>
  <Comments type="string">Joint ownership</Comments>
  <EstimatedMoveDate type="string">2015-11-25T05:57:00</EstimatedMoveDate>
  <Charge type="number">4.99</Charge>
  <ChargeStatus type="string">Chargeable</ChargeStatus>
 </item>
</root>
</OutputSchema></NewLeads></WebService>

Additionally, object SaleProperty may be missing, instead may be object PurchaseProperty. Similarly, object Quote can contain Sale, Purchase, Remortgage objects or sale and Purchase simultaneously
I can't find any tips on inserting it into one table via xquery on SQL Server 2012. Basic problem is that every time I'm building query to extract even one value (i.e. Transaction) only thing except errors I get is empty string.

my sample query (based on MSDN examples):

SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/root/item/transaction') as t(c)

I would like to have an example how to insert this xml into table (where every possible element has it's own column.

I assume that problem is with namespaces and strong typing of the xml. However, this is what I get from web service. I work on xml both missing and containing second xmlns declaration in root element, as well as with structure trimmed down to just root.


texts:
I am attempting to use Cross Apply to get a SUM() for each month and create a rolling trend report.  My issue is that with my query I keep getting an error of

  Msg 207, Level 16, State 1, Line 6
  Invalid column name 'TotalCost'.

Which is obviously an error in my syntax, but I just can not seem to find it.  Here is the query that I attempted (and have been toying with for 3 hours now)

SELECT
a.[iDate]
,TotalCost=SUM(ISNULL([value1],0)+ISNULL([value2],0)+ISNULL([value3],0)+ISNULL([value4],0)+ISNULL([value5],0))
,Rolling12Months=CASE
                    WHEN ROW_NUMBER() OVER (ORDER BY a.[iDate]) < 12 THEN NULL
                    ELSE TotalCost+Value
             END
FROM numbergame a
CROSS APPLY
(
  SELECT
  VALUE
  FROM
  (
      SELECT
      b.[iDate]
        ,VALUE=SUM(ISNULL([value1],0)+ISNULL([value2],0)+ISNULL([value3],0)+ISNULL([value4],0)+ISNULL([value5],0))
    FROM numbergame b
    WHERE b.[iDate] < a.[iDate]
    GROUP BY [iDate]
  ) b
) b
GROUP BY [iDate]
ORDER BY a.[iDate]

and in all transparency it is Post # 4 taken from Simple Talk.  Now here is my sample DDL in a mm/dd/yyyy format with only one day for each month, but it should get the point across.

Create Table numbergame
(
  iDate date
  ,value1 float
  ,value2 float
  ,value3 float
  ,value4 float
  ,value5 float
)

INSERT INTO numbergame VALUES
('01/01/2015', 10, 20, 30, 40, 50)
,('02/01/2015', 1, 2, 3, 4, 5)
,('03/01/2015', 5, 4, 3, 2, 1)
,('04/01/2015', 9, 8, 7, 6, 5)
,('05/01/2015', 5, 6, 7, 8, 9)
,('06/01/2015', 10, 8, 6, 4, 2)
,('07/01/2015', 11, 12, 13, 14, 15)
,('08/01/2015', 15, 14, 12, 13, 11)
,('09/01/2015', 3, 3, 3, 3, 3)
,('10/01/2015', 4, 4, 4, 4, 4)
,('11/01/2015', 2, 2, 2, 2, 2)
,('12/01/2015', 4, 4, 4, 4, 4)

How should my query (or if there is a better/quicker way to do this) be altered so that I can display my rolling trend?


texts: Use insert trigger to copy a modified NEW record to another table, with unknown columns

texts:
I use a Postgres extension (promscale) that dynamically creates tables with similar schemas: the tables all have a 'time' and a 'value' columns, then a number of labels columns.
Upon insert in any of those tables I need to make store a copy of the new row into a sibling table, after recalculating the value.
This question almost has the answer, but I can't explicitly access sample.value on the anyelement variable.
Here is what I tried:
create or replace function increase_trigger() returns trigger as $update_increase$
declare
    tbl_out varchar;
begin
    tbl_out := TG_TABLE_NAME || ":increase";
    select calc_increase(new, tbl_out);
end;
$update_increase$ language plpgsql;

create or replace procedure calc_increase(sample anyelement, tbl_out regclass) as $$
begin
    sample.value = sample.value + 1.0;
    execute format('insert into %s select $1.*', tbl_out) using sample;
end; $$ language plpgsql;

Of course the parser complains that "sample.value" is not a known variable.
I know the output table name and thus its composite type name, but I don't know how to use it in the function. I found could cast a my input sample (not tested):
execute format("select ROW($1.*)::%I from $1.*", tbl_out) using sample;

... but I can't put everything together to get something that works.
Any help appreciated!
Thanks!
[Edit2] The solution is indeed to use RECORD as a type instead of anyelement:
The complete code is as follows:
create table t1 (
    c1 varchar,
    c2 double precision
);

create table t2 (
    c1 varchar,
    c2 double precision
);

create or replace procedure calc_increase(sample record, tbl_out regclass) as $$
begin
    -- test
    c2.value = c2.value + 1.0;
    execute format('insert into %s select $1.*', tbl_out) using sample;
end; $$ language plpgsql;

create or replace function update_increase() returns trigger as $update_increase$
declare
    increase record;
begin
    call calc_increase(new, 't2');
    return new;
end;
$update_increase$ language plpgsql;

create trigger t_increase before insert on t1 for each row
    execute function update_increase();

insert into t1(c1,c2) values('foo', 10.0);


texts: SQL Server Files in Secondary file group updates much slower from linked server

texts:
My SQL server fact tables are incrementally being updated with reference to a linked server. The queries run routinely on a regular basis and have been executing successfully for a long time.  The execution time for all queries combined is below 5 minutes.  Recently the updates failed because the primary file group reached its limit, even though I set file growth to 'unlimited'.

I thought it would be helpful to move my fact tables to the secondary file in the same file group of my database to allow for growth. I also shrank the database which reduced file sizes (especially the log file) substantially.

I found that my queries were now running extremely slow, some taking up to 2 hours where they had before run in 5 minutes.

I have now moved them back to the database's primary file and completion time is now back to normal.  I do not however understand what went wrong with the distribution of the tables among the database files.

My queries are running because the log file is smaller, but I need to allow for future growth and would like to place some tables in the secondary file if I run out of space again.


texts:
we have this relation
Store: [SID, City, EID]
Employee: [EID, Name, Salary, SID]
Article: [AID, Name, Producer, Price]
Inventory: [AID, SID, Count]
Invoice: [IID, SID, Customer]
Item: [IID, SID, AID, Count]

Store.EID is a foreign key referencing Employee.EID and designates the store manager. Employee.SID
is a foreign key referencing Store.SID and describes in which store this employee is employed. The
inventory table stores (with foreign keys) how many articles (AID) are in stock at which store (SID). An
invoice is generated at a store (SID) and consists of multiple items, where each item is composed of an
article (AID) and a count.

The Employee table contains 20 000 employees, of which exactly two tuples t into one page. Each
employee has a (uniformly distributed) salary between 20 000 and 100 000. There are 10 stores, each
with the same amount of employees. The Employee table has two secondary composite-key indices on
(Salary,EID) and (EID,Salary). Both indices are B+ trees with a height of h and each leaf can store
5 references to pages.

The question is
Which index requires less page accesses for the query select * from Employee where EID = 5
and Salary > 60000?

So I know that the (EID, Salary) is better to use here, since the EID is unique and it will go to the EID = 5 and then find his salary, however, I find it a little bit confusing because actually, we have one access to the EID 5 then the condition will be checked and we will either get this employee or not.
So how can I calculate the costs of each in order to compare between them?


texts: This database has two foreign keys going to a single field, is it in 3NF?

texts:
Firstly, here is a picture of the database diagram generated by SQL Power Architect.

I needed to create a user_id pair in order to match friend requests to users, but this has given me two foreign keys on the request table which link back to a single primary key on the user table. I stuck with this because PostgreSQL never flagged it as an issue.

As well as this, I have somehow ended up with a non-identifying relationship on the session to log tables.

Do I have a problem with the normalization of my database? Not all of the data depends on the tablename_id primary keys so do I need to remove them?


texts: myisampack running really slow for large DBs

texts:
I was going through compressing a bunch of archival myisam tables using myisampack

It was making decent progress until it got to the most recent tables.  The overall database is a set of archives for a single production table.  Each archive version represents 1 quarter of a year going back several years.

Nothing has drastically changed in the data being stored or the average row length.  The individual archives have been growing with time as there simply are more on more rows each quarter over the previous one.

Up until the most recent 4 quarters the rate of compressing/rebuilding the index (which is only a single int PK) was around 14-16k rows/seconds.

The last ones were crawling along only doing about 1k rows/second.  Even right from the start; it wasn't like it started of as fast as the others and then suddenly slowed down after getting so far into it.

There wasn't any spike in general system load or i/o contention from other processes when it started slowing down.

Has anyone experienced this?  Is there something myisampack where it's overall rate of progress it should be expected to tank when compressing a table over a certain size?

Edit:  Since these are myisam tables I should point out they are all in the same datadir and all stored on the same physical array.


texts:
We regularly get this error on a SQL Server 2016 SP2 instance.

(Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49   Copyright (c) Microsoft Corporation  Enterprise
Edition: Core-based Licensing (64-bit) on Windows Server 2016
Datacenter 10.0  (Build 14393: ) (Hypervisor))

Error [36, 17, 145] occurred while attempting to drop allocation unit
ID ##### belonging to worktable with partition ID #####.

There is not much to find about this error therefore I added extended events to get extra information
I seems to be happening on several SELECT statements from an application that are really fast forward cursors.
In the plan there are a Clustered Index Insert and a clustered index seek on the CWT_PrimaryKey
I was wondering if the worktable in the error message is related to the CWT Cursor Work Table in tempdb and if so why it is happening sometimes ?
I could find this on the Internet seems somewhere related but much older version :
When a query execution plan is cached, the tempdb work tables required by the plan, if any, are often cached.
We had this exact problem when we were on SQL Server 2005 SP2 early builds when there was high tempdb activity + a mistimed job that updates statistics on a relatively large table in the middle of the day.
After talking to Bob Ward at MSFT, it was identified that a resource monitor was trying to free up a cached plan that has a cursor, where it was trying to deallocate a worktable but gets stuck on a latch for an IAM page in tempdb.
The solution that worked for us was to change the schedule for the update stats job + reduced the load on tempdb.
I would suggest you to look at the VirtualFileStats DMV and see the amount of IO you are doing against tempdb when compared to other databases.
For us at the time of this issue, our tempdb IO activity was 90% of the total activity on the box. After few optimizations, we got it down to 50-60%.
I am NOT sure if this really helps you but wanted to share some notes.
If you search for the unit ID belonging to worktable with partition ID it cannot be found anymore so I don’t what it is and if this error is really an issue.
So maybe performance of the tempdb database (IO) or not such an ideal fast forward cursor (plan) but I really want to understand what is happening maybe you can give me a clue.


texts: Why doesn't first execution plan use RowCount Spool?

texts:
Here is my repro that was tested on SQL Server 2008 R2, 2012, 2016.

The second and the third queries do use RowCount Spool, why the first does not?

create table dbo.t (id int identity primary key, v int);
--create statistics ST_t__v on dbo.t(v) with norecompute;

insert into dbo.t (v)
select top (10000) rand(checksum(newid())) * 5
from master.dbo.spt_values a cross join
     master.dbo.spt_values b;
go

declare @v int;

set statistics xml, io on;
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4);
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4 having count(*) > 0);
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 40);
set statistics xml, io off;
go

drop table dbo.t;
go

As the result,
IO statistics are:

Table 't'. Scan count 2, ***logical reads 20024***, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 't'. Scan count 2, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 't'. Scan count 2, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


texts:
I had SQL server 2005 installed on my windows server 2008 R2.

Recently i installed SQL server 2008  and SQL server management studio 2008 r2 (SSMS) on my virtual server and i configured the instance with SSMS.

But SSMS is using SQL server 2005 instead of 2008 and i am facing some problems with datatypes like Date.

Please not both SQL server 2005 and 2008 are express editions.

can anyone please suggest how to make use 2008 edition.

Thanks in advace.


texts: Why is my MERGE query deadlocking while in a serialized isolation level transaction?

texts:
I'm trying to avoid deadlock in with my MERGE query, which may be called by different threads and possibly overlap in execution with the same parameters.  My experience with this query is very similar to the scenario described in this question, and I've listed the query below for reference.

CREATE PROCEDURE MergeIt
    @dataToMerge MyTableType READONLY
AS
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION

        MERGE INTO TargetTable WITH(HOLDLOCK) AS [target]
        USING @dataToMerge AS [source]
        ON [source].KeyPart_1 = [target].KeyPart_1 AND
           [source].KeyPart_2 = [target].KeyPart_2
        WHEN NOT MATCHED THEN
            INSERT(Data, KeyPart_1, KeyPart_2)
            VALUES([source].Data, [source].KeyPart_1, [source].KeyPart_2)
        WHEN MATCHED THEN
            UPDATE SET [target].Data = [source].Data,
                       [target].KeyPart_1 = [source].KeyPart_1,
                       [target].KeyPart_2 = [source].KeyPart_2;

    COMMIT TRANSACTION
RETURN 0

TargetTable has an identity column which serves as the primary key, and furthermore has a uniqueness constraint on the [KeyPart_1, KeyPart_2] column-tuple.  MyTableType has a schema similar to TargetTable and also defines a primary key on [KeyPart_1, KeyPart_2] column-tuple.

I am trying to ensure that only one process be allowed to run this MERGE query at any given time, and I thought that the SERIALIZABLE isolation level enforces this.  However, this doesn't appear to be the case.  I've captured these XML log events that show which resources and locks are in play during the deadlock.  One query has an exclusive lock (X), and the other an update lock (U).  As I'm typing this, I see that it is not necessary to update the [KeyPart_1, KeyPart_2] column-tuple in the UPDATE clause, which might very well be contributing to the deadlock since that tuple will trigger an index update.

Are there any other suggestions as to how I can resolve this?  I suppose I can blindly try to use TABLOCKX as the table hint, but I'd like to understand how the SERIALIZABLE isolation level failed here.

Thanks!


texts: MySQL 5.5 Innodb_buffer_pool_bytes_data status variable missing

texts:
We're running a MySQL 5.5.29 server in Production. If I do:

SHOW GLOBAL STATUS;

I'm not getting the status variable 'Innodb_buffer_pool_bytes_data' which according to the official documentation for this version should be there. What am I missing here?


texts:
I have to create a table Product with elements P, PType, PName, Price and Origin.
I need to have the constraint that all products with PType "Meat" must have "Europe" as Origin. I wanted to add the constraint in an alter table but I saw that using an alter table means modifying the original table after its creation, but I need to have this condition at the creation of the table. I don't know how to write this constraint, can someone give me a hint?


texts: Mongodb data sync between primary and secondary node even when port is closed

texts:
I have configured mongodb replicaset in aws using 3 ec2's with one primary and 2 secondary nodes. When I insert data on primary node it is getting synced to the secondary nodes which it should. But when I close the port via security group still the data is syncing to the secondary node whose port is closed even though the state in rs.status() in the primary node is stateStr" : "(not reachable/healthy)" -> for the secondary node(whose port is closed).

I tried this with my secondary node in different subnet public as well as private but its still getting synced.I am new to mongodb and might be doing something wrong. Could someone please suggest something.


texts: Best practice regarding concurrency for INSERT into a table with composite primary key?

texts:
Say I've got the tables users, teams and a teams_users junction table (team_ID, user_ID / composite PK). If I wanted to add a user to a team, what's the best option when it comes to performance / concurrency issues?
Option 1)
First query the table and see if the relationship already exists, if it does just notify the user that made the request.
Option 2)
While inserting, use the WHERE EXISTS syntax (2nd example).
Option 3)
Use Postgres 9.5 Beta for the UPSERT functionality. (I suppose using a Beta version for a school project isn't that bad of an idea?).
What option would be best when it comes to concurrency. I'm not very experienced with SQL (just the standard stuff for CRUD applications). But as far as I know, in the first option, a relationship could be inserted by another user right after querying the junction table but before actually inserting a relation myself. I suppose there won't be any duplicates because I've got a composite primary key in that junction table, but I feel like it's bad practice to just let it make the exception and continue. Furthermore, in my current code, I'd return a 500 http status in this case (because of a db exception), which doesn't really seem right.
I've read I could partially solve the above by 'locking' the database/table, but I don't know if that's the right method.
Finally, I have no idea if there's a race condition when it comes to the 2nd option.


texts: ORA-01113: file 1 needs media recovery -- system01.dbf

texts: The only way to recover is from media backup? I know I cannot "rebuild" system datafile, but if I don't have a backup - I know shame on me! - Is there anyway to recover database?


texts:
if I have a list of date.
eg. Jan 01,2017, Mar 02, 2017, Feb 09,2017, Jun 02,2017

How can I get the most two recent date? Mar 02, 2017 and Jun 02,2017


texts: Extracting only numbers from data in mysql query

texts:
In mysql table, the data has special characters (.) and (_) mainly next to numbers. I'd like to extract only the numbers stripping the characters in mysql query. My current query is like this:

 $query = "SELECT Date, Tenext FROM tbdata";

In this query, I need this only for the second field name (Tenext).
How can I do this in the mysql query? any help is appreciated. Thanks!


texts: Couldn't install SQL Server 2012 on machine with Windows 7 SP1, VS 2010 SP1

texts:
I am trying to install SQL Server 2012 RTM on my pc, I have installed Windows 7 SP1, VS 2010 SP1 but it again and again giving this error:

  A network error occurred while attempting to read from the file:
  D:\Microsoft SQL Server 2012
   RTM\1033_ENU_LP\x64\setup\x64\sqlncli1.msi
  Error help link:
  http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=sqlncli.msi%40PublishProduct%401316

When I go to the Microsoft link I find

  We're sorry
  There is no additional information about this issue in the
  Error and Event Log Messages or Knowledge Base databases at this time.
  You can use the links in the Support area to determine whether any
  additional information might be available elsewhere.

How can resolve this, at first i thought adding current user in Display Replay controller making the problem, then uninstalled everything and again tried installing but couldn't succeed. Can anyone suggest what may be causing these problem and possible resolve for it.
I have also SQL Server 2008 R2 installed which I want to keep simultaneously with 2012

Additional Information's:

Its on local drive i.e on my harddisk, so there should be no error and Its the RTM version of SQL 2012.
I find below logs on C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\

Feature: SQL Client Connectivity SDK Status: Failed: see logs for details Reason for failure: An error occurred during the setup process of the feature. Next Step: Use the following information to resolve the error, and then try the setup process again. Component name: SQL Server Native Client Access Component
Component error code: 1316 Component log file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20121022_164723\sqlncli_Cpu64_1.log Error description: A network error occurred while attempting to read from the file: D:\Microsoft SQL Server 2012 RTM\1033_ENU_LP\x64\setup\x64\sqlncli1.msi Error help link: go.microsoft.com/…

This problem not yet solved, although other people installed SQL2012 with this installer on their PC's. I think something wrong with my PC or some settings i am using.
Please can anyone help me out?


texts: How can I model a medical scenario in an entity-relationship diagram?

texts:
I am a self-taught frontend developer that is trying to go full-stack, so I am trying to teach myself data modelling by means of working on some exercises out of a database management book.
In this way, I have started creating an entity-relationship diagram (ERD), for a medical scenario, that is shown bellow:

I am not quite finished as I am having some issues converting some of the relevant business rules into ERD notation. Also, when reading the business rules concerning the associations keep in mind that the entity types Physician, Order, Diagnosis, Treatment, and Patient are needed (because in the book they are stated as business rules and I would like to understand that specific model), and although they could probably be modeled in a different way, I am trying to understand this specific model.
Business scenario description

Physicians diagnose any number of Conditions affecting a Patient, and a Diagnosis may apply to many Patients.

The involved hospital records the following information: date and time of diagnosis, diagnosis code, and description.

Physicians may order and perform any number of Services and Treatments for a Patient, or may not perform any Treatment.

A Treatment or Service may be performed on any number of Patients, and a Patient may have Treatments performed or ordered by any number of Physicians.

For each Treatment or Service rendered, the hospital records the following information: Physician ordering the Treatment, treatment date, treatment time, and results.

Entity Type Definitions

Patient. A person who is either admitted to the hospital or is registered as an outpatient. Each patient has an identifier, the medical record number (MRN), and a name.

Physician A member of the hospital medical staff who may admit patients to the hospital and who may administer medical treatments. Each physician has a physician ID and name.

Diagnosis. A patient's medical condition diagnosed by a physician. Each diagnosis has a diagnosis has a diagnosis ID/code and diagnosis name. Example: 1032, STAPH FOOD POISIONING.

*Treatment. Any test or procedure ordered by and/or performed by a pysician for a patient. Each treatment has a treatment ID/treatment code and treatment name using standard codes. Procedure codes are used for diagnostic and therapeutic procedures. Example: 1244, Chest X-Ray.

Order. Any order issued by a physician for treatment and/or services such as diagnostic tests (radiology) and therapeutic procedures (physical therapy), or drugs (perscriptions). Each order has an order ID, order date, and order time.

In this manner, I was wondering if someone could look at this and derive the same ERD from the business rules I have or if I messed up the translation.
My considerations

The specific issue I can not seem to wrap my head around is the existence of multiple relationships between the entity types.
First of all, I do not even know what kind of cardinalities to put on regarding the relationship between Order and Treatment.

Another issue for me is the multiple relationships between Patient, Diagnosis and Physician. Also, the same issue with Physician, Patient, and Treament.
Are these perhaps suppose to be ternary degree relationships, and (or) contain weak entity types to make this more understandable? Like Order and Treatment, or Treatment and Diagnosis I could see one depending on another as a Treatment in my mind would depend on the Diagnosis (correct me if I am wrong).


texts: This query is choking the server. How can I improve it?

texts:
Following query is hogging a lot of resources, as much as that the queries are getting locked down & CPU usage goes through the roof!

The MySQL Processes list shows that as the number of requests for the queries increases, the time to completion also increases... It reaches to a point where queries just lock down.

As per the web host, there is some problem in the query design, but I am unable to figure it out.

SELECT DISTINCT pet.*,ownrs.id AS ownrunique, ownrs.super,
(SELECT count(*) from comments WHERE pet.id=comments.petid)
    AS noOfCom,
(SELECT count(*) from petfb WHERE pet.id=petfb.petid AND (feedback=1 OR feedback=100))
    AS newLikes,
(SELECT count(*) from petfb WHERE pet.id=petfb.petid AND (feedback=5 OR feedback=6))
    AS newDisLikes,
ownrs.name
FROM pet, ownrs
WHERE 1=1 AND pet.ownerID=ownrs.uniqueID AND catid=21
ORDER BY  date   DESC  LIMIT 0,10

EXPLAIN:

id  select_type         table       type    possible_keys   key         key_len ref                 rows    Extra
1   PRIMARY             pet         ref     catid           catid       4       const               4758    Using where; Using temporary; Using filesort
1   PRIMARY             ownrs       eq_ref  uniqueID        uniqueID    52      petdb.pet.ownerID   1
4   DEPENDENT SUBQUERY  petfb       ref     petid,feedback  petid       4       petdb.pet.id        11      Using where
3   DEPENDENT SUBQUERY  petfb       ref     petid,feedback  petid       4       petdb.pet.id        11      Using where
2   DEPENDENT SUBQUERY  comments    ref     petid           petid       4       petdb.pet.id        1       Using index

DESCRIBES of the 4 tables (linked to avoid lengthy post).

EDIT 1

Based on Bohemian & a1ex07 replies, I am now using the following query. Everything is working OK but the noOfCom is being calculated wrongly for the pets that have >0 comments - it just equals to the total number of feedbacks from the petfb table for that particular pet, not the actual no of comments. For the pets that have no comments, it shows correct as zero. What could be wrong here?

SELECT pet.*, ownrs.uniqueID AS ownrunique, ownrs.diamond, COUNT( comments.id ) AS noOfCom,
SUM( feedback =1 OR feedback =100 ) AS newLikes,
SUM( feedback =5 OR feedback =6 ) AS newDisLikes
FROM pet
JOIN ownrs ON pet.ownerID = ownrs.uniqueID
LEFT JOIN comments ON pet.id = comments.petid
LEFT JOIN petfb ON pet.id = petfb.petid
WHERE catid =13
GROUP BY pet.id
ORDER BY DATE DESC
LIMIT 0 , 10

EDIT 2

pet indexes:

Keyname     Type        Unique  Packed  Column          Cardinality     Collation   Null    Comment
PRIMARY     BTREE       Yes     No      id              40499           A
petindex    BTREE       Yes     No      detail (200)    40499           A
catid       BTREE       No      No      catid           62              A
date        BTREE       No      No      date            40499           A
detail      FULLTEXT    No      No      detail          1

petfb indexes:

Keyname     Type    Unique  Packed  Column      Cardinality Collation   Null    Comment
PRIMARY     BTREE   Yes     No      id          1222897     A
petid       BTREE   No      No      petid       71935       A
feedback    BTREE   No      No      feedback    18          A
deviceid    BTREE   No      No      deviceid    55586       A
ownrid      BTREE   No      No      ownrid      671         A

EDIT 3 - SHOW CREATE TABLE petfb

CREATE TABLE `petfb` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `petid` int(11) NOT NULL,
 `feedback` int(11) NOT NULL COMMENT '1=Like 100 = Hidden Like  5=Inappropriate  6=Dislike  7=Duplicate  8=Wrong Type',
 `fbdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `ownrid` varchar(50) NOT NULL,
 `deviceid` varchar(50) NOT NULL,
 `sugcatid` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `petid` (`petid`),
 KEY `feedback` (`feedback`),
 KEY `deviceid` (`deviceid`),
 KEY `ownrid` (`ownrid`)
) ENGINE=InnoDB AUTO_INCREMENT=1215274 DEFAULT CHARSET=latin1


texts:
I'm new to Mysql, just started today. I have to use the syntax shown here to add values, so I want to get the director name from the movie with id 289

INSERT INTO `db`.`movies` (`id`, `name`)
VALUES (289,"The movie title");

INSERT INTO `db`.`ddirectos` (`directorsid`, `moviesid`)
VALUES (1,289);

INSERT INTO `db`.`directors` (`id`, `name`,`last`)
VALUES (1,"roxy","roxylast");


texts: How to know when Postgresql password is changed

texts:
My question is how to know the date when a postgresql user password is changed.
Is it possible?
I know postgresql has account expiry feature,
but I'm asked to check if users have changed their passwords and to get the date.

Regards,

I tried pg_shadow etc. but they seem to have no date info.


texts:
SELECT
    tblstud.studid,
    tblsched.ip
FROM tblstud
LEFT JOIN tblstudsched
    ON (tblstud.studid=tblstudsched.studid)
LEFT JOIN tblsched
    ON (tblstudsched.schedid=tblsched.schedid)
WHERE
    tblstud.rfidid='".$rfid."'
    AND tblsched.ip='".$ip."'
    AND EXISTS
    (
        SELECT tblsched.schedid
        FROM tblsched
        INNER JOIN tblstudsched
        WHERE
            tblstudsched.schedid=tblsched.schedid
            AND NOW() BETWEEN tblsched.timein AND tblsched.timeout
    )

This is my code so far. It is designed to be done after Arduino microcontroller sends data to PHP. It takes me about 8 seconds before another data is to be accepted. I haven't checked the Arduino side but I think it's fast enough when I did another different MySQL query but that one is relatively shorter than the one above.

The tables are in Inno DB but I'll try MyISAM later since the latter is the best when there are more reads than adding data.

The data types are varchar with ranges varying from 3-30 characters each.

I'm confident also with the speed of the laptop I'm using.


texts: How can I combine a large CROSS JOIN query and return 1 result?

texts:
I have a rather large PostgresSQL query that returns a single JSON result that is then sent to the front-end app for display:

Select * FROM (SELECT sum(subscribers) AS subscribers FROM plans WHERE userid=$1 AND startDate>=$2 AND startDate<$3) subscribers
                CROSS JOIN (SELECT count(status) AS inactivesubscribers FROM subscribers WHERE userid=$1 AND status='inactive' OR status='suspended') inactivesubs
                CROSS JOIN (SELECT count(planName) AS plans FROM plans WHERE userid=$1 AND status='active') plans
                CROSS JOIN (SELECT count(promoCode) AS promotionals FROM promotionals WHERE userid=$1 AND status='active' AND startDate>=$2 AND startDate<$3) promotionals
                CROSS JOIN (SELECT count(chargeDate) AS charges FROM transactions WHERE userid=$1 AND chargeDate>=$2 AND chargeDate<$3) charges
                CROSS JOIN (SELECT count(refundDate) AS refunds FROM transactions WHERE userid=$1 AND refundDate>=$2 AND refundDate<$3) refunds
                CROSS JOIN (SELECT count(sentDate) AS messages FROM messages WHERE userid=$1 AND sentDate>=$2 AND sentDate<$3) messages
                CROSS JOIN (SELECT count(templateName) AS activetemplates FROM templates WHERE userid=$1 AND status='active') activetemplates
                CROSS JOIN (SELECT count(templateName) AS inactivetemplates FROM templates WHERE userid=$1 AND status='suspended') inactivetemplates

Returns:

[
  {
    subscribers: '146',
    inactivesubscribers: '12',
    plans: '12',
    promotionals: '12',
    charges: '7',
    refunds: '12',
    messages: '5',
    activetemplates: '4',
    inactivetemplates: '4'
  }
]

But, I'm not sure how I can add this query to gather 6 of the most popular plans within the large query:

CROSS JOIN (SELECT planName,subscribers as popularplans FROM plans WHERE userid=$1 AND status='active' ORDER BY subscribers DESC LIMIT 6) popularplans

Due to the CROSS JOIN instructions, the query above doesn't work as intended, because the query creates 6 different object results with planname: plan x and popularplans: x on each found row.

[
      {
        subscribers: '146',
        inactivesubscribers: '12',
        planname: 'Plan 1',
        plans: '12',
        popularplans: '22',
        promotionals: '12',
        charges: '7',
        refunds: '12',
        messages: '5',
        activetemplates: '4',
        inactivetemplates: '4'
     },
     {
        subscribers: '146',
        inactivesubscribers: '12',
        planname: 'Plan 2',
        plans: '12',
        popularplans: '12',
        promotionals: '12',
        charges: '7',
        refunds: '12',
        messages: '5',
        activetemplates: '4',
        inactivetemplates: '4'
     }
     ...etc
 ]

Ideally, I want the large query to return a single JSON object and be structured like so:

[
  {
    subscribers: '146',
    inactivesubscribers: '12',
    plans: '12',
    popularplans: [
      { planName: 'Plan 1', subscribers: '22' },
      { planName: 'Plan 2', subscribers: '12' },
      ...etc
    ],
    promotionals: '12',
    charges: '7',
    refunds: '12',
    messages: '5',
    activetemplates: '4',
    inactivetemplates: '4'
  }
]

Is there a better or a different approach to the large query than tons of cross joins?


texts: Select the same field based on different foreign keys

texts:
I have two tables:

Containing a list of products with ids and names
A table linking several products with parent-child relationship based on ids using foreign keys.

I want to create a view that will display the product name for both parent and child but both derives from the same table.

Each product can be a parent for multiple children
Each product can be a child for multiple parents
The parenting relationship goes only one level without the need for recursion (no grandchildren)

Here is a sample of the construction of tables:

create table plugins_t
(
  rid          int auto_increment,
  product_name varchar(156) not null,
  constraint plugins_t_rid_uindex
    unique (rid)
);

alter table plugins_t
  add primary key (rid);

create table parent_2_childs_t
(
  rid              int auto_increment,
  parent_plugin_id int not null,
  child_plugin_id  int not null,
  constraint parent_2_childs_t_rid_uindex
    unique (rid),
  constraint parent_2_childs_t_plugins_t_rid_fk
    foreign key (parent_plugin_id) references plugins_t (rid),
  constraint parent_2_childs_t_plugins_t_rid_fk_2
    foreign key (child_plugin_id) references plugins_t (rid)
);

alter table parent_2_childs_t
  add primary key (rid);

INSERT INTO plugins_t
  (product_name)
values
  ('product1'),
  ('product2'),
  ('product3'),
  ('product4');

INSERT INTO parent_2_childs_t
  (parent_plugin_id, child_plugin_id)
VALUES
  (1, 3),
  (2, 3),
  (2, 4);

INSERT INTO plugins_t
  (product_name)
values
  ('product1'),
  ('product2'),
  ('product3'),
  ('product4');

INSERT INTO parent_2_childs_t
  (parent_plugin_id, child_plugin_id)
VALUES
  (1, 3),
  (2, 3),
  (2, 4);

I used a two select using a subquery but I get duplicate values.

select
       pt.product_name as parent_product_name,
       c.product_name as child_product_name
from plugins_t as pt
right join parent_2_childs_t p2ct on pt.rid = p2ct.parent_plugin_id

  join (
    select pt2.product_name from plugins_t as pt2
    join parent_2_childs_t p2ct2 on pt2.rid = p2ct2.child_plugin_id
    ) as c

This is the desired output:

| Parent name  | child name  |
------------------------------
| product1     | product3    |
| product2     | product3    |
| product2     | product4    |


texts: MySQL Best approach for recording last update timestamp for every column and every row

texts:
This question is about possible approaches for time stamping every column of every row in a table.
I have a table with a small number of rows (fewer than 200 and not likely to grow much larger). Each row refers to an individual entity and each column corresponds to piece of data about that entity. The columns will be updated whenever new information becomes available, and I need to record when that happens.
For example, think in terms of a contacts table:
CREATE TABLE `contacts` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100),
    `address` VARCHAR(100),
    `phone_number` VARCHAR(100),
    `email_address` VARCHAR(100),
    `updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

If the phone number is updated this month, we might be inclined to believe that the `email_address` was updated as well, even though it may be severely outdated.
I can think of three approaches:
1. Create an additional timestamp column for each column:
CREATE TABLE `contacts` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100),
    `name_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `address` VARCHAR(100),
    `address_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `phone_number` VARCHAR(100),
    `phone_number_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `email_address` VARCHAR(100),
    `email_address_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

2. Or, I could created a secondary table that contains the same information:
CREATE TABLE `contacts_updated` (
    `id` INT,
    `name` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `address` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `phone_number` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `email_address` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_contacts_updated_contacts_id`
        FOREIGN KEY (id)
        REFERENCES contacts (id));

In which case, is there an easy way to duplicate a table and automatically change the column types to timestamps? And use triggers to update the secondary table?
3. Or, I could create an Entity-Attribute-Value table:
CREATE TABLE `contacts_updated` (
    `id` INT,
    `column_name` VARCHAR(15),
    `updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

...but this seems like it would be more difficult to query the information.
Are there other options that would be better? And I also assume that the answer will be different for a small dataset vs. a large one, so I'd love to know your opinion on both cases.


texts: Checking records consistency after mongorestore --noobjcheck

texts:
I had an HDD malfunction on a Mongo node.  After running fsck and mongod --repair I was able to dump the needed collection with mongodump and move it to another server.

However, some records appear to be malformed and mongorestore fails with:

  assertion: 10331 EOO Before end of object

I restored the collection with mongorestore --noobjcheck and now I want to remove the malformed records from the collection.

How do I do that?  Is there a way to force Mongo to check itself or must I do it manually?


texts:
In the er diagram below (not mine),

A Class can have many instructors via a standard junction table.
Is there a way of designing the Instructors/Classes tables using constraints to ensure that a Class must have at least one Instructor? I.e. 1 or many instead of 0 or many? So given Classes.class_id=1, there must be an Instructors_Classes(class_id=1, instructor_id=?)
Or is this something that you'd just use triggers to enforce?

As a secondary, can anyone suggest a good way of identifying one, and only one Instructor as the primary instructor for a class?
Currently I've added a field Instructors_Classes.IsPrimary (number) and applied a unique constraint on (class_id, IsPrimary) and a validation constraint of IsPrimary=Null OR 1, so only 1 instructor can have the value 1 per Class.
Then when I want to get a list of all Classes, I can just join on Instructors_Classes.class_id WHERE IsPrimary=1 so that I get a single value for the Instructor of that class.
This just feels a bit wrong but I'm not a db person so I don't know if there's a more conventional way of doing it. I thought of adding a Classes.instructor_id field but couldn't work out how to enforce the link between it and the Instructors_Classes table.


texts: Is there any way to efficiently perform the equivalent of DENSE_RANK in MongoDB?

texts:
SQL Server and Oracle both have DENSE_RANK functions. Is there a way to do something similar in MongoDB without having to resort to MapReduce? In other words, suppose you have a T-SQL select clause like this:

SELECT DENSE_RANK() OVER(ORDER BY SomeField DESC) SomeRank

What is the best way to do the same thing in MongoDB?

(Note: This is a repost of the MongoDB question over here. I'm hoping to get more feedback from DBAs...)


texts: How to reclaim the unused Space in a database

texts:
The database is almost of size 55GB. This database should have never grown to this size as some of the maintenance jobs were not running.

I have deleted almost 30GB of data. Now the database size is almost 45GB and Space Available is almost 20GB .

Which shows that Actual size of the database is now almost 25GB and there is some Unused space that needs to be returned to Operating System.

I have executed DBCC commands for both Database and individual files (8 of them). And I only managed to release a couple of GBs.

The Commands I used are as follows

DBCC SHRINKDATABASE (N'MyDB');
GO

DBCC SHRINKFILE('Filename1', 0 , TRUNCATEONLY)
GO
DBCC SHRINKFILE('Filename2', 0 , TRUNCATEONLY)
GO
  and so on....

But this seems to have no affect on database size and the available space.

I read an article which suggested rebuilding indexes and then shrinking database will solve this issue.

But once I have shrunk the database I would have to rebuild indexes anyway as shrinking database would have made a mess of indexes.

My requirement is to only leave 10% space for db to grow and release the unused space. Then I plan to rebuild Indexes and update statistics after that.

What would be the best approach to this problem. Any suggestions or pointers in the right direction are much appreciated.

Thank you in advance


texts: Why can't I establish a connection to the local oracle database from within the pre-built oracle virtual machine?

texts:
I downloaded DeveloperDaysVM2016-06-02_13.ova from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and successfully loaded it into Virtual Box.

I logged in as "oracle" user and I can see the desktop.

From within the virtual machine, I opened Firefox and navigated to:

http://localhost:8080/ords/hrrest/employees/ and successfully received a list of employees in JSON format. So I know the database is up and running.

Now I am trying to connect to this database via the "ojdbc6.jar" Java connector/driver from Netbeans (see image below).  However,

when I enter in

username:hr
password:oracle
SID:orcl12c
URL: jdbc:oracle:thin:@localhost:1521:orcl12c

I get an error message indicating

  "Unable to add connection. Cannot establish a connection to
  jdbc:oracle:thin:@localhost:1521:orcl12c using
  oracle.jdbc.OracleDriver (ORA-01017: invalid username/password; logon
  denied"

But this doesn't make any sense because I was able to successfully login from the terminal:

sqlplus hr

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 7 17:21:07 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password: oracle
Last Successful login time: Thu Jul 07 2016 16:33:31 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Why can't I login via the java connector from netbeans?

I also checked

lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-JUL-2016 17:22:12

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                05-JUL-2016 16:48:08
Uptime                    2 days 0 hr. 34 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orcl12c
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/vbgeneric/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vbgeneric)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "ords" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@vbgeneric oracle]$

So what am I doing wrong? Everything seems to be up and running correctly in this pre-built virtual machine.


texts: How to fill gaps with calendar table, count and group by

texts:
I'm building a bar chart. I would like a query which counts the entries from a particular day and groups them together as one row with a count. I have the following query working and I have a working date dimension table which I would like to try to join with to fill in the gaps. It would also be nice to have min and max variables to easily alter the span (1 week, 1 month, 1 year, or from start of this year etc).

I got a variant working with a CTE query but it was painfully slow (1min+). I can't seem to figure this out, help!

The Date Dimension table was created following this guide: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

CREATE TABLE dbo.DateDimension
(
DateKey             INT         NOT NULL PRIMARY KEY,
[Date]              DATE        NOT NULL,
[Day]               TINYINT     NOT NULL,
DaySuffix           CHAR(2)     NOT NULL,
[Weekday]           TINYINT     NOT NULL,
WeekDayName         VARCHAR(10) NOT NULL,
IsWeekend           BIT         NOT NULL,
IsHoliday           BIT         NOT NULL,
HolidayText         VARCHAR(64) SPARSE,
DOWInMonth          TINYINT     NOT NULL,
[DayOfYear]         SMALLINT    NOT NULL,
WeekOfMonth         TINYINT     NOT NULL,
WeekOfYear          TINYINT     NOT NULL,
ISOWeekOfYear       TINYINT     NOT NULL,
[Month]             TINYINT     NOT NULL,
[MonthName]         VARCHAR(10) NOT NULL,
[Quarter]           TINYINT     NOT NULL,
QuarterName         VARCHAR(6)  NOT NULL,
[Year]              INT         NOT NULL,
MMYYYY              CHAR(6)     NOT NULL,
MonthYear           CHAR(7)     NOT NULL,
FirstDayOfMonth     DATE        NOT NULL,
LastDayOfMonth      DATE        NOT NULL,
FirstDayOfQuarter   DATE        NOT NULL,
LastDayOfQuarter    DATE        NOT NULL,
FirstDayOfYear      DATE        NOT NULL,
LastDayOfYear       DATE        NOT NULL,
FirstDayOfNextMonth DATE        NOT NULL,
FirstDayOfNextYear  DATE        NOT NULL
);

Current query:

select count(*) as total, dateadd(DAY,0, datediff(day,0, CreatedAt)) as created
from Table1
group by dateadd(DAY,0, datediff(day,0, CreatedAt))
order by dateadd(DAY,0, datediff(day,0, CreatedAt)) desc

total   created
1       01/11/2017 00:00:00
16      01/03/2017 00:00:00
1       12/27/2016 00:00:00
1       12/20/2016 00:00:00
1       11/30/2016 00:00:00
1       11/29/2016 00:00:00
11      11/28/2016 00:00:00
13      11/25/2016 00:00:00
4       11/24/2016 00:00:00
2       11/22/2016 00:00:00

I want something like this

total   created
1       01/11/2017 00:00:00
0       01/10/2017 00:00:00
0       01/09/2017 00:00:00
0       01/08/2017 00:00:00
0       01/07/2017 00:00:00
0       01/06/2017 00:00:00
0       01/05/2017 00:00:00
0       01/04/2017 00:00:00
16      01/03/2017 00:00:00
etc


texts: Query with dynamic ranges in PostgreSQL based on status

texts:
We have a table with invoices classified per day, with normal invoices and void invoices (invoices that are voided, in our country we use the Spanish term "anuladas"). For example suppose that we have the next list of invoices for today (where APL stands for Applied and ANU for Void or Anulada):

ID     TOTAL       STATUS     DATETIME            INVOICE_NUMBER

1      00.50       APL        2014-07-04 07:35    1355
2      10.50       APL        2014-07-04 07:36    1356
3      12.50       APL        2014-07-04 07:38    1357
4      10.50       ANU        2014-07-04 07:35    1358
5      10.50       ANU        2014-07-04 07:45    1359
6      09.50       APL        2014-07-04 07:55    1360
7      10.50       APL        2014-07-04 08:15    1361
8      10.50       APL        2014-07-04 08:25    1362
9      13.50       ANU        2014-07-04 08:35    1363
10     10.50       ANU        2014-07-04 08:45    1364
11     10.50       APL        2014-07-04 09:05    1365
12     14.50       APL        2014-07-04 09:15    1366
13     10.50       ANU        2014-07-04 09:16    1367
14     19.50       APL        2014-07-04 09:37    1368
15     10.50       ANU        2014-07-04 10:45    1369

This is a sample information for one day. In my report I have to show all the sales but as ranges, per day. I don't have problem with getting the information for each day. The problem is that, in this example, for today July 7th, I have 15 sales and it has to show like this:

TOTAL       INITIAL CORRELATIVE        ENDING CORRELATIVE     STATUS

23.50            1355                     1357                 APL
21.00            1358                     1359                 ANU
30.50            1360                     1362                 APL
23.50            1363                     1364                 ANU
25.00            1365                     1366                 APL
10.50            1367                     1367                 ANU
19.50            1368                     1368                 APL
10.50            1369                     1369                 ANU

As you can see it groups the first three sales as a range based on correlatives, but when we have one or more void invoices, it cuts the range and inserts a range of void invoices (1358 to 1359) then continues to the next range.

[UPDATE]: After Erwin's right answer I leave the query that we coded according to his:

SELECT sum(total)      AS total
  ,min(num_documento) AS initial_correlative
  ,max(num_documento) AS ending_correlative
  ,estado,grp
  ,count(1) AS ct
FROM  (
   SELECT *, count(step OR NULL) OVER (ORDER BY vtaprsv_id) AS grp
   FROM  (
      SELECT *, lag(estado) OVER (ORDER BY vtaprsv_id) IS DISTINCT FROM estado AS step
      FROM  (
         SELECT total, num_documento, vtaprsv_id, fecha_venta
              , CASE estado WHEN 'PEN' THEN 'PGD' WHEN 'APL' THEN 'PGD' ELSE estado END estado
         FROM   venta_variada
         WHERE  fecha_venta BETWEEN '2014-07-02 00:00:00' AND '2014-07-02 23:59:59'
         ) consi
      ) x
   ORDER BY fecha_venta
   ) y
GROUP  BY grp, estado
ORDER  BY grp;

Hope it helps somebody else.


texts: Filter first then join or join first then filter?

texts:
Consider 5 tables with more than 1 billion records each.

A query needs to join them. And I know that less than 10% of their records will be require. Say they all have a Date dimension, and only data from current month is needed.

What should be faster:

1) Use a simple SELECT: join all tables, then filter (WHERE) each table's dimension for current month.

2) create 5 temp tables, filtering each source table for current month records, here we can also take the opportunity to select only required columns, then join these temp tables.

Extra possibility:

3) Maintain secondary tables, having only current month/year worth of data. These tables are maintained by same ETL that feeds main ones.


texts: How bad is adding new column each time to compare the fixed values?

texts:
As for now we have a program that gets some data weekly which need to be compared with previous values, maimly for uniqueness.

All data keys are dynamic and are expected to be changed.

So creating the table for the data with specific columns is not a solution, as their title and number may vary.

We proposed to a client storing XML or json with dynamix data in the table and then deserialize it for analysis.

But eventually we left with the decision to append the new column to the table each week, and will it with as much data as we have and then compare the lists inside of columns.

Basically, it's a horizontal-growing table.
Deep inside I see that it's a bad idea, but unfortunately I don't have a strong argument about it. Could you please help me?

How bad is this design and which consequences we should expect with it?

-----------------------------------------------------------------------------
| PropertyName W1 | Property Value W1 | PropertyName W2 | Property Value W2 |
-----------------------------------------------------------------------------
| Title           | SomeTitle         | OtherProp       | PropValue         |
-----------------------------------------------------------------------------
| SomeProp        | SomeVal           | Title           | SomeUpdatedTitle  |
-----------------------------------------------------------------------------


texts: How to restore a database with SMK and Cerificate to another computer

texts:
On ComputerA, I have a database with SMK and a certificate. I've created this with the following code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO

CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO

CREATE SYMMETRIC KEY SymmetricKey1
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE Certificate1;
GO

Now I have created a backup file for this to restore it on ComputerB. The restore works fine but when I try to execute the following code on ComputerB:

OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;

 -- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;

It returns the error:

Please create a master key in the database or open the master key in the session before performing this operation.
The key 'SymmetricKey1' is not open. Please open the key before using it.

I've tried deleting the SMK and Cert. under the Security Folder it still returns the same error. Any idea how to solve this?


texts: SQL Server 2012: flatten hierachy to dynamic number of columns

texts:
In MS SQL Server SQL 2012, I have a hierarchy table with a dynamic number of levels. How can I transform the following table:

CREATE TABLE #Hierarchy (AccountId INT, ParentId INT, Name VARCHAR(50))

INSERT INTO #Hierarchy VALUES (1, NULL, 'Revenue')
INSERT INTO #Hierarchy VALUES (2, NULL, 'Costs')
INSERT INTO #Hierarchy VALUES (3, 1, 'Sales')
INSERT INTO #Hierarchy VALUES (4, 1, 'Interests')
INSERT INTO #Hierarchy VALUES (5, 2, 'COGS')
INSERT INTO #Hierarchy VALUES (6, 2, 'Expenses')
INSERT INTO #Hierarchy VALUES (7, 6, 'Salaries')
INSERT INTO #Hierarchy VALUES (8, 6, 'Other')

to get such a table:

Regards,
Przemek


texts: Table Space not being freed up after large DELETEs

texts:
We offload execution log data from SSRS into our main database for auditing purposes. We keep a year's worth of this data "online" in our main db and have a weekly job that offloads anything over a year to a separate archive db.

In our main db, a year's worth of data is equal to roughly 9 million rows, so in this table there is consistently about 9 million rows. Each week we import about 150K new rows, and delete about 150K old rows.

Over time, we see that the number of rows stays pretty consistent, but as of April 1, the table size has started growing (to 5 times it's original size since April 1). On April 1 we added an NVARCHAR(MAX) column to the table.

It would appear that as a result of adding the NVARCHAR(MAX) to the table, the "garbage collection" is never freeing up the unused space in this table when we delete the 150K rows each week.

The table has a clustered index (IDENTITY PRIMARY KEY).

What can I do to free up this space?

The documentation for DBCC CLEANTABLE discusses freeing up space after a column is dropped. Not sure that applies in my situation.

(I have no interest in shrinking my DB or data files - that has nothing to do with my issue - rather it is the allocation of table space that I am trying to free up so I end up with a lot more free space within this physical database file).


texts: How do I use SSMS to compare Database 1 Table 1 against Database 2 Table 1?

texts:
I have two databases - Database1 and Database2.

Both databases contain a table that has similar structure, exemplified as follows:

===========================================================================================
| ID | Name | PhoneNoFormat | DialingCountryCode | InternationalDialingCode | InternetTLD |
===========================================================================================
|    |      |               |                    |                          |             |
===========================================================================================

However, due to some reason, (a) one of the tables in one of the databases has data that is not exactly the same as (b) that contained in the other table in the another database.

So, how can I compare Database1.Table1 against Database2.Table1?

I tried using the following query, but nothing happened so was wondering if I have to rewrite it:

SELECT MIN(TableName) as TableName,
       ID,
       Name,
       PhoneNoFormat,
       DialingCountryCode,
       InternationalDialingCode,
       InternetTLD

FROM

(

  SELECT  'Table A' as TableName,
          A.ID,
          A.Name,
          A.PhoneNoFormat,
          A.DialingCountryCode,
          A.InternationalDialingCode,
          A.InternetTLD

  FROM [D:\DATABASE1.MDF].[dbo].[Table1] AS A

  UNION ALL

  SELECT 'Table B' as TableName,
         B.ID, B.Name,
         B.PhoneNoFormat,
         B.DialingCountryCode,
         B.InternationalDialingCode,
         B.InternetTLD

  FROM [D:\DATABASE2.MDF].[dbo].[Table1] AS B

) tmp

GROUP BY ID, Name, PhoneNoFormat, DialingCountryCode, InternationalDialingCode, InternetTLD

HAVING COUNT(*) = 1

ORDER BY ID


texts:
I am trying to change a SQL Agent Operator in my system and it will not let me.

I have sa but I think it's partially due to how I'm doing it.

I'm trying to modify the operator with the name NIT and Arts to NIT and ARTS and when I do that it comes back with the error of "The specified @name (NIT and ARTS) already exists even though it doesn't currently.

I'm running on SQL Server 2008R2. Any help with this would be great. Thanks.


texts: Is any possible way to get warning message through by oracle

texts:
I am working in developing oracle data base tool, I want display warning message when executing function. For example:

CREATE OR REPLACE FUNCTION FN_OEDS_CONVERTDATE2(
V_DATE DATE) RETURN VARCHAR2
    IS
BEGIN
DECLARE
    V_RETVALUE VARCHAR2(15) :='';
    BEGIN

    SELECT
        CASE V_DATE
            WHEN NULL THEN ''
            ELSE TO_CHAR(V_DATE, 'DD-MM-YYYY')
        END INTO V_RETVALUE FROM DUAL

     RETURN V_RETVALUE;
    END;

END;
/

Above function will execute successfully, but it wont through any error message , but it status would be invalid, how to get the warning message through by out in java.


texts: Increasing the size of the Oracle datafile

texts:
I have a third party application using Oracle 10g database with 1 tablespace and 1 datafile. They have assigned 10GB as the size of the tablespace which is almost full now. I know we can can increase size of this.
I want to clarify what if I add one more datafile and assign 10GB space to that?
Do I need to modify anything on application or will it work normally?
So all I need to do would be:
alter tablespace USER_DATA_TBS add datafile '/oradata/ovbs/user_data04.dbf' size 1000m;

Please help, I am a total beginner.


texts: Using the correct database when calling a system stored procedure in SQL Server 2008

texts:
I think there's something I'm missing when it comes to which database is being used when running a custom system stored procedure. I have the following in my stored procedure (edited for brevity):

ALTER PROCEDURE sp_mysp
AS
IF (EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'mytable'))
BEGIN
    --Do stuff to the table
END
ELSE
BEGIN
    PRINT 'Table mytable does not exist'
END

The problem is that when I call the procedure from mydb, the Master table is used for the initial check, rather than the current database. If I write mydb.INFORMATION_SCHEMA.TABLES it works, but that's not really a solution, as it's defeating the whole point of maintaining this as a single SP rather than one SP for each DB.

Any ideas? I tried passing the DB name as a parameter and starting the SP with 'use @db_name', but apparently stored procedures don't allow use statements.

Thanks in advance for all help.


texts: How to transfer the ownership of the dbo schema in SQL Server 2012

texts:
I've accidentally given a user the ownership of the db_owner schema (using the check box in the UI as below) and now I cannot:

Transfer ownership to another user
Drop the user from DB (though I can delete login in the SQL Server)

I tried The database principal owns a schema in the database, and cannot be dropped.

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo

And while it completed successfully, the user still has ownership, and it's greyed out so I can't seem to do it in the UI either.

Found a solution:

In addition to Arron's answer, I realised that I'd run the above command in wrong DB (facepalm!). Once the DB had been corrected both the above SQL and the answer below worked.


texts:
I have the following table, called sales

+--------+--------+
| fruit  | amount |
+--------+--------+
| cherry |    124 |
| plum   |     23 |
| pear   |     19 |
| apple  |     13 |
| banana |      4 |
| orange |      2 |
+--------+--------+

And using this to get the rank

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC;

.

+------+--------+--------+
| rank | fruit  | amount |
+------+--------+--------+
|    1 | cherry |    124 |
|    2 | plum   |     23 |
|    3 | pear   |     19 |
|    4 | apple  |     13 |
|    5 | banana |      4 |
|    6 | orange |      2 |
+------+--------+--------+

So now I want to get the rank of apple but I couldn't figure out how exactly to do it.


texts: Why a select from the tables, joined by a CASE statement, could be 7x slower?

texts:
There are two queries from the same two tables: one joins them on the columns @t1.b and @t2.x, the other uses a case statement as below. The 2nd query is 7x slower. The record counts are only different by 1 between them as the CASE is processing a special situation that only occurs once per query.

The first guess was that indexes on the columns @t1.b and @t1.c were not being used. However, the execution plan did not show any table scans that could explain the slowness.

declare @t1 table (a int, b int, c int)
insert into @t1 select 1, 10, null union select 2, null, 20 union select 3, 30, null

declare @t2 table (x int)
insert into @t2 select 10 union select 20 union select 30

select *
from
    @t1 as t1
    inner join @t2 as t2 on
        case t1.a
            when 2 then t1.c
            else t1.b
        end = t2.x

Is there any way to return the same result set, but make it as fast as when joining on the columns?


texts: Mysql keeps crashing with an error that seems to only be fixed once mysqldb is running

texts:
Installed mysql as part of a provided ansible script
I cant keep mysql running.  The only error I get is the following

2020-05-29T19:18:35.663272Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200529 12:18:35

mysqld: Table 'mysql.plugin' doesn't exist
2020-05-29T19:18:35.663472Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2020-05-29T19:18:35.663713Z 0 [ERROR] unknown variable 'validate_password_policy=MEDIUM'
2020-05-29T19:18:35.663728Z 0 [ERROR] Aborting

but all the solutions suggest I connect to the db and fix it.  Of course it keeps rebooting so I cant.


texts:
I have this table which I would like to store a chain of records.

CREATE TABLE table_name (
    id INT,
    unique_id varchar,
    reference_id varchar,
);

I want to implement SQL query for MariDB which prints all records by id with all records with reference_id. Something like this:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 43           |   |   |
| 66 | 55655     | 45           |   |   |
| 78 | 88877     | 66           |   |   |
| 99 | 454       | 33           |   |   |

I would like when I select record 66 to get all up and down transactions because each other are using id which points to them. How I can implement this using Recursive CTE? Is there a better way?

Expected result for record with unique_id 66:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 43           |   |   |
| 66 | 55655     | 45           |   |   |
| 78 | 88877     | 66           |   |   |

This SQL query works:

select t.id, t.unique_id, @uid := t.reference_id reference_id
from (select * from mytable order by id desc) t
join (select @uid := 66) tmp
where t.id = @uid or reference_id=66

https://www.db-fiddle.com/f/33mbBmQRiF5Z8eRoCR3oco/0

But when I tried this CTE query I can't get the above rows.

with recursive cte as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on cte.id = t.reference_id
     )
select *
from cte;

https://www.db-fiddle.com/f/ppmxX12PJ1rhszp85nJcWp/0

Can you give me a hand how to rewrite the SQL query properly, please?


texts: Local user login access in sql server 2008

texts:
I have a created a local user called 'MynetDB' with SQL Server Authentication (password) in SQL server 2008. Server roles is public and user mapping to 2 databases as db_owner and public. Status has Permission to connect database engine: = Grant and Login: = Enabled.

When I try to connect to server database engine with SQL Server authentication it is giving error Login failed for user 'MynetDB'. (Microsoft SQL Server, Error: 18456)

I also tried from C# code giving same error. Please advise me how access the databases?

Edit:
I am getting error on Management studio also

C#

<remove name="MyNetDB" />
    <add name="MyNetDB" connectionString="Data Source={ServerName};Initial Catalog=MynetDB;User Id=MynetDB;Password=taD6eg4c;" providerName="System.Data.SqlClient" />