texts: What is a good database design approach for my Online Quiz Android Application?

texts:
I have been working on an Android Online Quiz Application with PHP & MySQL. I know what attributes i need however when designing the relationship it becomes complex and confusing when I analysed it. I do not know if this would be a good database design before I write in MySQL and implement it in my project. See my ERD Below:

As the design above, I am trying to design that will also work for creating charts with any android chart generation library. I am not sure about this design and I am thinking that I might struggle ending up to redo my database design. Any suggestions for this? Thanks in advance.


texts: MySQL Update column with case on joined tables

texts:
I have 2 tables and I want to update table 1 using a case check on table 2:

update
cms.Clients c #c_id unique
join
cms.ClientDomains d #c_id not unique
on c.c_id = d.c_id
set c.country =
    case
        when d.domain_id=2 then 'MEX'
        when d.domain_id=1 then 'USA'
    end
where c.c_id in (113113)

This update changes for domain id 2 the country to 'USA'.
What am I missing?


texts: Purged more than 100,000 records in table but size of database has not reduced

texts:
I am using SQL Server 2008 R2 version running on Windows Server 2008 R2 Standard. My database size is around 207MB. As it contained 100's of thousands of records in a table, I decided to keep only the first 10000 records and delete the remaining so as to minimise the size of the database.

I deleted the 90000 records from the database and I also rebuilt the indexes:

 DELETE FROM toptrends
 WHERE HandleID NOT IN (SELECT TOP 10000 HandleID
                        FROM toptrends
                        ORDER BY lastmodifieddatetime DESC)
 go

 ALTER INDEX ALL ON TopTrends
 REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
          STATISTICS_NORECOMPUTE = ON);
 GO

and checked the size of the database and database_log files. The database_log file size has increased in size but the database file remained the same. I thought it should decrease

file size before deletion : around 207892
file size after deletion : around 207892(same)
size of database_log file 625MB (up from some 300MB)

Can't we reduce the size of the database by purging unwanted/old records from the table and rebuilding the indexes?

p.s : My database_log file has increased dramatically after purging the table and I don't want that too going large.


texts: Howto find out details and reasons for idle transactions?

texts:
I have started to use the "Idera SQL check" tool. It shows floating bubbles for each process active on the Sql Server system:

Those bubbles are surrounded by a red border in case the transaction is idle. The legend says this is dangerous:

Those are some particular sample information related to spid 200:

spid           : 200
kpid           : 0
blocked        : 0
waittime       : 0
lastwaittype   : MISCELLANEOUS
waitresource   :
dbid           : 5
database       : DBNAME
uid            : 1
cpu            : 0
physical_io    : 0
memusage       : 3
login_time     : 29.02.2016 09:37:17
last_batch     : 29.02.2016 09:37:17
ecid           : 0
open_tran      : 1
status         : SERVERNAME
program_name   : PROGRAMNAME
host_process   : 5380
cmd            : AWAITING COMMAND
nt_damain      :
nt_username    :
net_address    : 99887766
net_library    : TCP/IP
loginname      : sqlusername
stmt_start     : 0
stmt_end       : 0
input_buffer   :

It's obvious it is dangerous.
But I have trouble finding out the reason. Looking at the sys.sysprocesses DMV to find out the queries executed in the context of those spids.
The queries executed are running very fast and are no candidates for long running. So I don't understand why they are getting idle within their transactions? How to find out the backgrounds?

EDIT:
The query behind the scenes getting the processes is the following:

exec sp_executesql N'
            SELECT p.spid, p.kpid, p.blocked, CAST(p.waittime AS BIGINT) as waittime, p.lastwaittype, p.waitresource, p.dbid, db.name as [database], p.uid, CAST(p.cpu / 1000 AS BIGINT) as cpu,
            CAST(p.physical_io AS BIGINT) as physical_io, p.memusage,
            p.login_time, p.last_batch , p.ecid , CAST(p.open_tran AS BIGINT) as open_tran, p.status, p.hostname, p.program_name, p.hostprocess, p.cmd, p.nt_domain, p.nt_username, p.net_address,
            p.net_library, p.loginame, p.stmt_start, p.stmt_end
            FROM master.dbo.sysprocesses p join master.dbo.sysdatabases db on p.[dbid] = db.[dbid]
            Where hostprocess <> '''' AND program_name <> @application',N'@application nvarchar(17)',@application=N'Idera.Application'

It looks like the OPEN_TRANS indicates processes in a transaction and having a red border. And the thickness of the red border increases over time meaning the longer this process is part of the resultset, the thicker the red border gets.


texts: What type of database can I use to store mutual scores between every combination of users?

texts:
Let's say I have an application that calculates and stores a mutual score between every combination of users.

I'm currently using Postgres to store this information.

So if my system had users with id 1, 2, and 3 that table might look like

CREATE TABLE scores (id,user_id,friend_id,score)
AS ( VALUES
    ( 1, 1, 2, 15.2 ),
    ( 2, 2, 1, 15.2 ),
    ( 3, 1, 3, 19.7 ),
    ( 4, 3, 1, 19.7 ),
    ( 5, 2, 3, 21.0 ),
    ( 6, 3, 2, 21.0 )
);

As you can see, the data itself is pretty simple.

Every combination of users has a mutual score calculated between them (the derivation of that score or what it means isn't important here) and I store it in the table.
Because I could look up either user, I have to store the pairings "both ways" (e.g. 1 -> 2 and 2 -> 1)

The problem is that this increases quadratically. With 10,000 users there are 100 million records in this table. With 100,000 users (which is a reasonable scope for my app) there would be 10 billion records.

So my question is - surely Postgres can't be the most scale-able way to store this information right? I don't need access to it constantly, just periodically when I'm running a batch job so I'm happy to store it in some separate type of DB on a separate server.

What kind of DB specializes in storing this type of paired data and can perform quick reads?

For example, I commonly query for all the ranked friends of a given user with SELECT friend_id FROM my_table where user_id = ? ORDER BY score DESC so I'd like to run the equivalent of that query on this new database.

Thanks!

P.S Someone had suggested a Graph DB - didn't know if that would apply here.


texts: Can we decompose a schema into BCNF when it is not even in 1NF

texts:
Given a schema r(A,B,C,D,E,F) and  a set of functional dependencies

A --> BCD
BC --> DE
B --> D
D --> A

As we can see the schema is not even in 1NF as can be read here, now the question is can we directly decompose the schema into 3NF forms or we have to first bring it to 1NF.

If we directly decompose, it will be as follows

Since A --> BCD is not a trivial relation and A is not a primary the relation breaks up into

r1(A,B,C,D) and r2(A,E,F)
and then we again break r2 because r1 is now in 3NF but not r2
then we get r1(A,B,C,D) r2(A,E) and r3(F)

But this does not make sense because we must first bring the relation into 1NF first and then decompose it, by doing that we can get two schemas r1(A,B,C,D,E) and r2(F). By this we see that r1 is now in BCNF and we need not further decompose it and r2 is also in BCNF.


texts: Why Jira can't connect database with pgBouncer?

texts:
pgbouncer.ini

[databases]
jiradb = host=127.0.0.1 port=5432 dbname=jiradb

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres

userlist.txt

"postgres" "postgres1"

It can be connected by psql:

psql -p 6432 -h 127.0.0.1 -U postgres jiradb
(Prompt password: postgres1)

But try connect from application:

Jira dbconfig.xml:

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>postgres72</database-type>
  <schema-name>public</schema-name>
  <jdbc-datasource>
    <url>jdbc:postgresql://127.0.0.1:6432/jiradb</url>
    <driver-class>org.postgresql.Driver</driver-class>
    <username>postgres</username>
    <password>postgres1</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <validation-query>select 1</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <pool-test-while-idle>true</pool-test-while-idle>
  </jdbc-datasource>
</jira-database-config>

When restart Jira, the database can't be connected again:

Database: JIRA couldn't connect to your database

JIRA failed to establish a connection to your database.
This could be because:

    Your database isn't running
    The configuration of your dbconfig.xml file is incorrect (user, password, or database URL etc.)
    There is a network issue between JIRA and your database (e.g. firewall, database doesn't allow remote access etc.)

I am testing it in one server. Jira is installed at jira user. pgBouncer is running under postgres user.


texts: Get value from one field, and paste it in to a new column

texts:
I'm relatively new to Microsoft Access, and just a beginner with SQL programming.

I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or Code in it. This number always Comes after a string "Arbeitslpatz "(The first character starts at string Position 17) and always changes every 76 rows. (Total rows 29790). What I want to achieve is to copy this number (for example 101100) and paste it in a new column in the first 76 rows. Then comes the next number for the next 76 rows, and so on. (I later want to to a lookup function with SQL WHERE functions and return some desired values, and for that I need to have these "Arbeitsplatz numbers" in a new row.
I already assigned an ID to the file, but I didn't manage to get farther. (I experimented with VBA Loops, but didn' really succeed)
Either SQL or VBA Code would be appreciated.

I don't know how well the screenshots can be seen &/ if I provided enough Information...

Thank you all for your help

This is an Image from the orignial text file which I linkt to the database in Access:


texts: Postgresql Slow performance query after table partitioning

texts:
I have a query which was executing in 30 secs before one of its table parititioned and after partitioning the query is ever running.

Table that is recently partitioned is ysm_yield_session_master.
This new table holds 4 to 5 partitions and each partition has up to ~90 million rows.

yr_yield_result and ywd_yield_website_details are other partitioned tables (each partition is very large, millions of rows) which were partitioned long ago.

SELECT
     ovp_ota_name AS ota_name,
     COALESCE(t.pending_count, 0) AS pending_count
FROM ovp_ota_vendor_profile
LEFT JOIN
(
     SELECT
           ovp_ovp_key as ota_key,
           COUNT(*) AS pending_count
     FROM ysm_yield_session_master
     LEFT JOIN ywd_yield_website_details ON ysm_ysm_key=ywd_ysm_key
     LEFT JOIN yr_yield_result ON yr_ywd_key=ywd_ywd_key
     LEFT JOIN wm_abc ON ywd_wm_key=wm_wm_key
     LEFT JOIN wmo_abc ON wmo_wm_group=wm_group
     LEFT JOIN ovp_abc ON ovp_ovp_key = wmo_ovp_key
     WHERE (   ysm_yield_date = 'now'::text::date
            OR ysm_yield_date = 'yesterday'::text::date)
       AND (GREATEST(now(), ysm_yield_time) - LEAST(now(), ysm_yield_time))
          < '06:00:00'::interval
       AND (   ywd_yield_date = 'now'::text::date
            OR ywd_yield_date = 'yesterday'::text::date)
       AND (   yr_yield_date IS NULL
            OR yr_yield_date = 'now'::text::date
            OR yr_yield_date = 'yesterday'::text::date)
       AND yr_yr_key IS NULL
       AND ovp_ovp_key IS NOT NULL
     GROUP BY ovp_ovp_key) t ON ovp_ovp_key = ota_key
ORDER BY ovp_ota_name;

I still have the original table, and I see the query runs much faster when I replace the partitioned table with the old table. I compared the execution plan of the query before and after partitioning and they are very different and the one with partition is expensive.

I ensured the stats are updated after partitioning. Not sure what else I could do.

Any thoughts..?

FYI,
The total number of rows for all partitions is approx 360 million records.
The total number of rows of standalone table is approx 2 billion records (however it is much faster on this table)

Execution plan on old standalone table

Execution plan on new partitioned table


texts:
I currently have a database design that I have been looking at and I am wondering if its the best one.

I have the following tables:

Each request has a current status. I need to keep track of all the status changes. RequestStatus currently keeps all these records, with the current record being marked as being the current one (to remove the need to find the one with the most recent date).

Looking at it I am beginning to think that I should have a StatusID column on Request changing the relationships to:

I know this breaks proper normalisation but what I really need is just an audit trail or status changes which is only ever accessed via Request - I will never need to access RequestStatus via Status.

Thoughts?

UPDATE:

There will be 6 statuses total: Pending, Approved, Rejected, Cancellation Requested, Cancellation Approved and Cancellation Rejected.

Most Requests will have 1 or 2 statuses total with the majority being 2 e.g. Pending then either Approved or Rejected or straight to approved (for automatically approved requests).


texts:
Looking for an example code in Oracle to do the same. i.e.

Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like?

What would I loose in the real world app by replacing an Insert trigger?

More clarification based on the responses below:
 We do not want Remote client to write to both table T1 and T2. This is a high throughput data and performance will suffer. Hence Remote client would write only to T1. Transfer from T1 to T2 should happen locally.


texts: Can SQL Server Change Data capture (CDC) log rows to an external DB?

texts:
I have not used SQL Server CDC before, but to my understanding it works like this:

business data changes in source DB, e.g. SQL Server
CDC has been configured to track certain table/column changes, which it reads asynchronously from business data server transactional logs

I have two question:
1. Can CDC be triggered to store e.g. some DB rows to an external DB, or do any eventual database rows that are being created from the transaction logs, need to be stored back to the source DB?
2. Can CDC read from the business DB transactional logs and create new rows to a NoSQL DB?

Thanks!


texts:
I have a SQL Server 2008r2 database server running on Windows Server 2008. On this server every week the log of at least one database throws this error:

  Backup detected log corruption in database dbname. Context is
  FirstSector. LogFile: 2
  'L:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dbname_1.ldf' VLF SeqNo: xe1c94
  VLFBase: x280900000 LogBlockOffset: x282d9d600 SectorStatus: 2
  LogBlock.StartLsn.SeqNo: xd0010047 LogBlock.StartLsn.Blk: x213b Size:
  x6400 PrevSize: x8496

I can fix this but I want to know why the error appears frequently. I do not see disk problems in the event viewer. I'm doing log backups every hour with Tivoli TSM. It is a virtual server over VMWare.

Why do log files get corrupted frequently? Where can I start looking?


texts: Is dimensional modelling useful for modelling tables of calculations?

texts:
I have a set of tables that look a bit like this:

Key 1 | Key 2 | Key 3 | Var 1 | Var 2 | Var 3 | ... | Var 200
-------------------------------------------------------------
   X  |   Y   |   Z   |   2   |   3   |   5   | ... |    8

The first three columns form a compound key. The remaining columns are populated by a series of calculations such as:

Var 3 = Var 1 + Var 2
Var 4 = Var 3 + Var 2
Var 5 = Var 3 + Var 4
....

(This is a huge simplification of the formulae. But, in essence, there is a chain of calculations that builds on previously calculated items.)

So, when considering how to build a dimensional model, I can identify the components of the key as a dimension (or two), and the variables seem to be measures, but I am struggling to identify what the fact is. I wonder if there is a key insight into the dimensional modelling process that I'm missing, or whether it's just inappropriate for my situation.

EDIT: I am not certain what I would achieve by dimensional modelling either! The context is an existing application which processes 3NF tables, does calculations, and spits out 3NF tables. It is proposed that these tables are migrated into a data warehouse which is being built using the dimensional modelling methodology. I am struggling to understand how this might be of benefit, since the calculations are abstract mathematical and statistical computations, while dimensional modelling seems to be oriented around identifying the "business process" that gives rise to facts.


texts:
I have no idea of what I have done here, but my InnoDB engine seems to have gone from my MySQL  server. I recently upgraded it from the dotdeb repository, then installed mysql-server.

There is no mention of InnoDB in my my.cnf except some comments which explain InnoDB is enabled by default, which I don't understand. There is also no mention of InnoDB in SHOW ENGINES.

Is there something I'm missing here?

If it matters, my MySQL server version is: 5.5.24-1~dotdeb.1 (Debian).

EDIT: SHOW ENGINES:

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

Data dir contents:

root@slone:/var/lib/mysql# ls
debian-5.1.flag  ib_logfile0.bk  ibdata1  mysql_upgrade_info  test
debian-5.5.flag  ib_logfile1.bk  mysql    performance_schema  tstore


texts: Deploy a SSIS package to remote SQL Server

texts:
I am trying to deploy a SSIS package to a remote SQL Server.
In the deployment wizard, when i want to create a new project with SQL Server Authentication, it gives me this error:

  The operation cannot be started by an account that uses SQL Server
  Authentication. Start the operation with an account that uses
  Integrated Authentication. (Microsoft SQL Server, Error: 27123)

As far as I understand, it is saying that I must use Windows Authentication and I don't know how I can use Windows Authentication in non Domain environment.


texts: Decomposition of a relation to 2NF and 3NF

texts:
I am trying to solve this example, but for now I can't. Can somebody show me, step by step how to decompose this to 2NF and then to 3NF?

R = {ABCDEF, ABC -> EF, B -> E, E -> D}

key of this relation is:

{ABC}

So now I try to split R into 2 relations:

R0 = {ABCEF, ABC -> EF}
R1 = {BED, B -> E, E -> D}

but its all wrong... i got other answers in my book. I dont even try to make 3NF out of it.


texts: Connecting to the default instance of SQL Server connects to a named instance

texts:
I have 2 instances of SQL Server installed on one server: SQL002.

One instance is a default instance, SQL2008 R2, and the SQL service is generally not running except for specific tests.

The other instance is a named instance, SQL 2014 (so SQL002/SQL2014). This is generally running all the time.

Today I just had a developer tell me that when they connect to SQL002 in SQL Server Management Studio it will connect successfully. I replicated this from my machine and also see a successful connection, however when I look at the properties of the connection it is reporting that it connected to the named instance.

What is going on here? Is there a redirect I'm not aware of?


texts: Which of these build numbers will tell me what version of SQL Server is installed on a server?

texts:
The main Microsoft SQL Server 2008 R2 (64-bit) doesn't contain a version number in Programs and Features in Microsoft Windows Server 2008 R2:

To me it almost looks like I have SQL Server 2008 R2 RTM installed on the server since the version numbers match some of the other components listed, notably Microsoft SQL Server 2008 R2 Policies.

My goal is to install SP2 but when I tried last night I got an error message concerning an unexpected version number.

Is there a way to use sqlcmd to find out what version it is?


texts: How to ship new rows from the source to a target server?

texts:
Context
We have a SQL Server 'source' where daily 10M rows inserted to a table. No updates. I would like to ship the new rows to a 'target' server, then delete those (sooner or later) on the source where disk space is limited. PK is monoton increasing identity column.
(edited: I forgot mentioning, the target server is mostly offline)
Question
Besides of programmed export import which based on administering which PK ranges which were shipped, is there any more efficient solution?. I have very limited DBA knowledge, my first idea was differential backup with recovery model simple, then some log shipping with recovery model full, but I think both cases the deletes on the 'source' will by applied on the 'target' which I do not want to happen.


texts: SSIS package Job Fails with Unexpected Termination

texts:
I scheduled a SSIS package in SQL Server 2012 Agent which fails sometime with "Unexpected Termination" without any error message.

Any Idea why it happens?


texts: Need suggestions for best way to archive MySQL (InnoDB) tables

texts:
Problem:
I have two fairly large tables. A 'wall' table for messages between users with 9 million rows at 2 GB and a 'chapter' table at 2 million rows and 18 GB. I want to keep the number of active rows for the 'wall' table small while I want to diminish the size of the chapter table. I made the mistake of not compressing text data to begin with and I'd like to start compressing data in the archives.

For the 'wall' table, I'm thinking that everything older than a certain wall id will be transferred and compressed to a 'wall_archive'. Anyone wanting to view older posts will just be given a "view archive" link where older post queries use the archive table. Then I run a cron job to do this every now and then and the last wall id archived will be stored somewhere for reference. Am I going the right direction here?

I'm not so sure how to keep the 'chapter' table manageable. Perhaps it's less archiving and more needing to partition the table (or both). But what's the best way to do this? I was thinking of splitting 'story' IDs into odds and evens and dividing the chapter into two tables but I'll run into the same problem again down the road. Or I can archive stories modified before a certain date. Or before a certain story ID. Any suggestions for a scalable solution?

Lastly, how should I go about compressing text data? Should I use PHP's gzcompress function at level 9 to store text data into a BLOB column then gzuncompress the data on retrieval? Or should I use MySql's COMPRESS/UNCOMPRESS functions? I'm leaning towards using PHP in case I separate the web server(s) from the DB server where I can have PHP do the compression processes instead of the more valuable DB server but I'd like to know what best practices are.

Considerations:
I'll still need to be able to access old 'chapter' data easily. 'wall' data can be put into slower storage if needed but it isn't necessary at the moment.

Environment:
6 Core AMD Opteron, 16 GB RAM, 256 GB SSD for MySql,
Percona Server 5.5, Apache, CentOS 6, PHP 5.3, innodb_file_per_table is enabled, database and webserver is running on same machine, total database size is 30 GB, all tables are InnoDB

Schemas:

CREATE TABLE `wall` (
  `id_wall` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_author` int(10) unsigned NOT NULL
     COMMENT 'wall''s owner',
  `id_user` int(10) unsigned NOT NULL DEFAULT '0'
     COMMENT 'user that wrote the comment',
  `comment` text NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id_wall`),
  KEY `id_user` (`id_user`),
  KEY `id_author` (`id_author`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8
  COMMENT='User profile wall'
  AUTO_INCREMENT=9710655 ;

CREATE TABLE IF NOT EXISTS `chapter` (
  `id_story` int(11) unsigned NOT NULL,
  `id_chapter` int(11) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL,
  `main_image` varchar(2047) DEFAULT NULL,
  `body` mediumtext,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `is_not_shown` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_story`,`id_chapter`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8
  ROW_FORMAT=DYNAMIC
  COMMENT='Story content by chapter';

ALTER TABLE `chapter`
  ADD CONSTRAINT `chapter_ibfk_1`
    FOREIGN KEY (`id_story`)
    REFERENCES `story` (`id_story`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;


texts: Free space of mdf and ldf does not match with database free space

texts:
In SSMS I saw file size related properties and found below details for one database. Here values does not match with other properties. Here size of the mdf, ldf and total size matches with other values under each window. But Available free space of mdf and ldf if added then it does not equal to Available free space shown in shrink database window and free space shown in database properties. This is true for any database. Why is it so? Please can anyone explain the logic behind this?

Under database properties:
Size:               91.31 MB
 Space available:13.40 MB
Under database file properites:
mdf size: 17 MB
ldf size: 75 MB
under shrink database:
Currently allocated size: 91.31 MB
Available free space:     13.40 MB
under shrink file-for data file:
currently allocated size: 16.38 MB
Available free space:     12.63 MB
under shrink file-for log file:
currently allocated size: 74.94 MB
Available free space: 55.62 MB


texts: Multiple join with self join in MySQL and split column in row

texts:
I have four tables "Commission" , "Vendors", "Accounts" and "comsplitconfig" like

commission Table

commissionid    vendorid    accountid   sales   commission
1               V1          A2          2000    100
2               V1          A1          1000    100
3               V2          A4          3000    150
4               V2          A2          4000    200
5               V2          A2          4000    200
6               V2          A5          5000    500

Vendor Table
vendorid    vendorname
V1          VEN1
V2          VEN2
V3          VEN3

Account Table
accountid   accountname parentid
A1          ACC1        NULL
A2          ACC2        A1
A3          ACC3        A6
A4          ACC4        A5
A5          ACC5        NULL
A6          ACC6        NULL

comsplitconfig Table

vendorid    account_commission  parent_commission
V1          75                  25
V2          50                  50

and I wish for the result to be like

vendorid    accountid
V1          A2
V1          A1
V1          A1
V2          A4
V2          A5
V2          A2
V2          A1
V2          A2
V2          A1
V2          A5

Using the following query i get thousands of multiple records.

SELECT  commission.vendorid , Account.accountid, Account.parentid
    FROM  commission
    INNER JOIN  comsplitconfig  ON comsplitconfig.vendorid = commission.vendorid
    LEFT JOIN
        ( SELECT  accountaccountid.accountid as oid,
                  accountparentaccountid.accountid as cid
            FROM  Account accountaccountid, Account accountparentaccountid
            WHERE  accountaccountid.accountid != vaccountparentaccountid.accountid
        ) as u1
      ON u1.oid = commission.accountid
              OR  commission.accountid = u1.cid
    inner join  Account
      ON u1.oid = Account.accountid
              or  Account.accountid = u1.cid

Result is based on Account Parent id.
if parentid of an account associated to commission record is not NULL then it'll be shown in result as rows(using self join to alias).
For Example In above Commission table for(commmissionid => 1) V1 vendor A2 account is associated and A2 account's parentid is A1. therefore result for V1 shoulb be

vendorid    acountid
V1          A2
V1          A1

for commissionid => 2, No V1 and A1 but A1 has no parentid so overall result of Commission for V1 vendor is

vendorid    acountid
    V1          A2
    V1          A1
    V1          A1


texts: Splitting Existing Partitions in SQL 2014

texts:
I have a SQL Server table that contains over 20 billion rows, with 13 billion rows in the last partition.

The table is currently partitioned 6 ways by a "Time" field:

2011
2012
2013
2014
2015
2016/2017/2018

The 2016/2017/2018 partition needs to be split into their own partitions so there is a 2016, 2017, 2018 partition.

How can this be done and in the process avoiding a massive log file growth? The database is in Simple Recovery and currently each partition is in a separate file group on its own set of disks.

CREATE TABLE [dbo].[Log](

      [RefNo] [nchar](7) NOT NULL,

      [DevID] [nvarchar](7) NOT NULL,

      [Time] [datetime] NOT NULL,

      [summary] [float] NULL,

      [staging] [float] NULL,

      [position] [float] NULL,

CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED

(

      [RefNo] ASC,

      [DevID] ASC,

      [Time] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96)

)


texts:
I am trying to configure Barman to backup. When I do a barman check replica I keep getting:

Server replica:
WAL archive: FAILED (please make sure WAL shipping is setup)
PostgreSQL: OK
superuser: OK
wal_level: OK
directories: OK
retention policy settings: OK
backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: FAILED (have 0 backups, expected at least 2)
ssh: OK (PostgreSQL server)
not in recovery: FAILED (cannot perform exclusive backup on a standby)
archive_mode: OK
archive_command: OK
continuous archiving: OK
archiver errors: OK

I am using Postgresql 9.6 and barman 2.1; I am not sure as to what the issue is could someone help?
Here is my Barman server configuration:

description = "Database backup"
conninfo = host=<db-ip> user=postgres dbname=db
backup_method = rsync
ssh_command = ssh postgres@<db-ip>
archiver = on


texts: not-yet-assigned record is indeterminate, but triggers on update (postgresql 9.3)

texts:
I create a very simple function, ensuring it only happens with an update.

CREATE OR REPLACE FUNCTION simple_trigger() RETURNS trigger AS $$
DECLARE
        _unused_variable INTEGER; -- unrelated placeholder.
BEGIN
        IF (TG_OP = 'UPDATE') THEN
                INSERT INTO trigger_log(id, value) values (OLD.id, OLD.value);
        END IF;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

I register the trigger

DROP TRIGGER simple_trigger_trigger on trigger_test; -- just in case.
CREATE TRIGGER simple_trigger_trigger AFTER UPDATE ON trigger_test FOR EACH statement execute procedure simple_trigger();

I have simple table being inserted into on update.

mydatabase=# \d trigger_log
  Table "public.trigger_log"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 value  | integer |

Nothing in that table at first.

mydatabase=# select * from trigger_log;
 id | value
----+-------
(0 rows)

Three rows in trigger_test (the table being updated).

mydatabase=# select * from trigger_test;
 id | value
----+-------
  1 |     2
  2 |     2
  3 |     2
(3 rows)

I can't update and have the insert happen.

mydatabase=# update trigger_test set value = 100 where id = 2;
ERROR:  record "old" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  SQL statement "INSERT INTO trigger_log(id, value) values (OLD.id, OLD.value)"
PL/pgSQL function simple_trigger() line 6 at SQL statement

My understanding per postgres docs was that NEW is unassigned on DELETE operations and OLD is unassigned on INSERT operations. That makes sense. This is neither.

How can it possible be unassigned?

This is very frustrating. I can't think of a more simple example.


texts: How to port LTRIM from Oracle to SQL Server?

texts:
Moving an application from Oracle to SQL Server, I have this pseudo Oracle PL/SQL:

SELECT LTRIM(MyColumn, '-, ') FROM MyTable

I.e. I'm using Oracle's LTRIM with a second argument, specifying the characters to trim from the left side of the string.

Unfortunately, the T-SQL version of LTRIM doesn't allow me to specify the characters to trim.

Currently, I'm rather clueless how to migrate that LTRIM. I'm even thinking about processing the results in my hosting C# application, after I read the MyColumn.

This looks rather inelegant to me.

My question:

Is there any meaningful way of getting an LTRIM-like functionality for T-SQL to pass the characters to trim away?

Edit 1:

I need to replace -, , and  from the beginning of the string.

E.g.:

   -----, ,,, This is ,- a test,---,

would result in

This is ,- a test,---,

Edit 2:

I strongly hope this isn't an XY problem.

Maybe rewriting my whole query would remove the need for LTRIM altogether, although I would rather focus on porting it as 1:1 as possible and later question the usefulness of the LTRIM.


texts: High tup_returned value for a dormant database

texts:
I was reviewing some statistics on a set of production databases on a Postgresql-9.3 server by running this query:

SELECT * FROM pg_stat_database ORDER BY tup_returned DESC;

I noticed some interesting numbers for a particular database. I know that the database is dormant and should have zero activity, yet it was reporting a tup_returned count of 698378 for the last ~24 hours. Per the docs, tup_returned is the:

  Number of rows returned by queries in this database.

There is no possible way that our queries could account for this row count.

I then connected to the database and ran this command to try to figure out which tables were responsible for the usage:

SELECT * FROM pg_stat_all_tables;

All of the activity for seq_tup_read, idx_scan, and idx_tup_fetch was logged for pg_* tables such as pg_database, pg_authid, pg_tablespace, etc. None of the tables we created had any kind of count higher than ~3-4.

Anyone know why or how all of this activity is being recorded if the table is dormant?


texts:
Our database Server is being hosted by another company.  They provide a file level backup that occurs once per day.  They also allow for Log Shipping.  My company is taking advantage of their Logshipping option as a major part of their backup and recovery solution.

I would like to have a better backup system that would allow us to restore data up to the point in time of failure.  Typicly this would be full backkup model for the database and backups of the transaction logs as well as the full and incrimentals.  However, the vendor has told me that providing us with transaction log backups would cause issues with the Log Shipping.

Is there a backup strategy that will allow me to restore to the point of failure and also use logshipping?


texts:
I'm currently working on a Sql Query that's having performance issues. I ran the execution plan and saw the Hash Match has 50% cost and also, doing a index scan. I'm starting to learning about the execution plan, so please help me tuned the performance on the query.

SELECT TOP 20 CustomerPrimaryExtID,
          Max(POSTimeStamp)     AS TransactionDate,
          ExtLocationCode,
          0                     AS RedemptionAmount,
          0                     AS RedemptionCount,
          TerminalNum,
          TH.LogixTransNum,
          POSTransNum           AS TransNum,
          0                     AS DetailRecords,
          CustomerTypeID,
          PresentedCustomerID,
          PresentedCardTypeID,
          HHID,
          Replayed,
          0                     AS TransContext,
          isnull(TransTotal, 0) AS TransTotal
FROM   TransHist AS TH WITH(nolock)
   LEFT OUTER JOIN
(
    SELECT
        LogixTransNum
    FROM
        TransRedemption with(nolock)
) TR2
ON TH.LogixTransNum = TR2.LogixTransNum
WHERE  ( ( ( CustomerPrimaryExtID IN ( '' )
         AND HHID IS NULL )
        OR HHID = '0000000250000013408'
           AND CustomerTypeID <> 1 )
      OR ( CustomerPrimaryExtID = '0000000250000013408'
           AND CustomerTypeID = 1 ) )
           AND TR2.LogixTransNum IS NULL

GROUP  BY CustomerPrimaryExtID,
      HHID,
      CustomerTypeID,
      PresentedCustomerID,
      PresentedCardTypeID,
      TH.LogixTransNum,
      POSTransNum,
      TerminalNum,
      ExtLocationCode,
      Replayed,
      TransTotal
ORDER  BY TransactionDate DESC


texts: How to grant permissions to allow anything inside a SQL Server schema?

texts:
I have a schema called training. I have a new role called training_modify. Here are the desired permissions for users placed in that role:

Select, update, insert, and delete permissions on any object in that schema
Create any object in that schema
Drop any object in that schema
No permissions on any other object outside that schema (except for those granted through the public role
No create/drop/alter permissions for the schema itself
The role and individual users should not require membership in db_ddladmin, db_datareader, db_datawriter, or any other default security roles

In simple terms, I want the users in the role to be able to do anything they want within the schema without affecting/seeing anything outside the schema or the schema itself. What is the least privilege to grant this type of access?

My approach so far:

Create training schema with dbo as the owner
Create training_modify role with dbo as the owner
Grant all permissions on the schema to the role
Grant create objects to the role
Add a user to the role

Here is the code for the above steps:

CREATE SCHEMA training AUTHORIZATION dbo;

CREATE ROLE training_modify AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::training TO training_modify;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO training_modify;

EXEC sp_addrolemember 'training_modify', 'example_user';

Is this the right approach? Does it have any unintended consequences? I am most worried about how ownership chaining may affect this approach negatively with dbo being the owner of the schema and the role (as well as other schemas/objects in the DB), and the alter permission being granted on the schema.

Also:

Should dbo still own the role? or should TestOwner own both the role and the schema?
Is it accurate that if I took away the CREATE PROCEDURE and CREATE FUNCTION permissions, this would prevent any DML on objects outside the specific schema? I don't think I care if they can create procedures, although I would like them to be able to execute procedures/functions (which I think would be covered by the EXECUTE permission on the schema). If this is accurate - are there any pros/cons between this approach and changing the owner of the schema?


texts: SQL Server Partitioning vs Indexes on Separate Filegroups

texts:
I am just about to start on an excercise to reorganise a partitioned table and I was looking for best practises or recommendations.

Let us say I have a billion row table and 8 logical disks available to me for this table.

The question I have is, is it better to create a partition scheme which divides the data into 8 filegroups and has the indexes storage aligned with the data...

Or would it be better to create 4 file groups for data and 4 for indexes (not aligned), then place each of the filegroups onto one logical disk?

Any suggestion or comments would be welcome.


texts: pg_xlog not recycling WAL files on slave server

texts:
I've setup streaming replication with Postgres 9.3
My problem is that on the slave server the pg_xlog folder just gets fuller and fuller and WAL files are not getting recycled.
The slave server has the following (relevant) values in postgresql.conf:
wal_keep_segments = 150
hot_standby = on
checkpoint_segments = 32
checkpoint_completion_target = 0.9
archive_mode = off
#archive_command = ''

My initial replication command was:
pg_basebackup  --xlog-method=stream -h <master-ip> -D .  --username=replication --password

So I guess my WAL files are OK.
Here is my slave server startup log:
2017-05-08 09:55:31 IDT LOG:  database system was shut down in recovery at 2017-05-08 09:55:19 IDT
2017-05-08 09:55:31 IDT LOG:  entering standby mode
2017-05-08 09:55:31 IDT LOG:  redo starts at 361/C76DD3E8
2017-05-08 09:55:31 IDT LOG:  consistent recovery state reached at 361/C89A8278
2017-05-08 09:55:31 IDT LOG:  database system is ready to accept read only connections
2017-05-08 09:55:31 IDT LOG:  record with zero length at 361/C89A8278
2017-05-08 09:55:31 IDT LOG:  started streaming WAL from primary at 361/C8000000 on timeline 1
2017-05-08 09:55:32 IDT LOG:  incomplete startup packet
2017-05-08 09:58:34 IDT LOG:  received SIGHUP, reloading configuration files
2017-05-08 09:58:34 IDT LOG:  parameter "checkpoint_completion_target" changed to "0.9"

I even tried to copy older WAL files from master server manually to slave but that also didn't help.
What am I doing wrong? How can I stop the pg_xlog folder from growing indefinitely?
Is it related to the "incomplete startup packet" log message?
One last thing: under the pg_xlog\archive_status folder all the WAL files are with .done suffix.
Appreciate any help I can get on this.
Edit:
I enabled log_checkpoints in postgresql.conf.
Here are the relevant log entries since I enabled it:
2017-05-12 08:43:11 IDT LOG:  parameter "log_checkpoints" changed to "on"
2017-05-12 08:43:24 IDT LOG:  checkpoint complete: wrote 2128 buffers (0.9%); 0 transaction log file(s) added, 0 removed, 9 recycled; write=189.240 s, sync=0.167 s, total=189.549 s; sync files=745, longest=0.010 s, average=0.000 s
2017-05-12 08:45:15 IDT LOG:  checkpoint starting: time
2017-05-12 08:48:46 IDT LOG:  checkpoint complete: wrote 15175 buffers (6.6%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=209.078 s, sync=1.454 s, total=210.617 s; sync files=769, longest=0.032 s, average=0.001 s
2017-05-12 08:50:15 IDT LOG:  checkpoint starting: time
2017-05-12 08:53:45 IDT LOG:  checkpoint complete: wrote 2480 buffers (1.1%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=209.162 s, sync=0.991 s, total=210.253 s; sync files=663, longest=0.076 s, average=0.001 s


texts:
In Postgres, I have a table like this:

    Column    |            Type             |                                 Modifiers
--------------+-----------------------------+--------------------------------------------
 problem_pk   | bigint                      | not null default nextval(...)
 version      | bigint                      | not null default 1
 problem_type | e_problem_type              | not null
 building_fk  | bigint                      | not null
 dpoint_fk    | bigint                      |
 native_name  | character varying(256)      |
 event_date   | timestamp without time zone | not null default now()
 count        | bigint                      |

and e_problem_type looks like this:

\dT+ e_problem_type;
                                         List of data types
  Schema  |      Name      | Internal name  | Size |             Elements             | Description
----------+----------------+----------------+------+----------------------------------+-------------
 ecorithm | e_problem_type | e_problem_type | 4    | nil                             +|
          |                |                |      | empty_string                    +|
          |                |                |      | nan                             +|
          |                |                |      | dash                            +|
          |                |                |      | other_bad_data                  +|
          |                |                |      | unmapped                        +|
          |                |                |      | missing_header                  +|
          |                |                |      | low_data_volume                 +|
          |                |                |      | attachment_with_no_valid_headers |

And I have a query like this:

select
    problem_type, native_name, max(event_date)
from
    point_problem_history
where
    building_fk = 3
and
    problem_type = 'nil'
group by
    native_name, problem_type
order by native_name;

I want to add the "count" field to my output, but have been unsuccessful in figuring out how to do this.  If I add it, I have to group by it and I end up with duplicate native names.  What I want is, for each native name,  the value of the 'count' field for the most recent event_date for that native name and problem type = (the string 'nil').  I've tried a lot of different queries and subqueries with no success.  Any and all help appreciated.


texts: GRANT PRIVILEGES TO users stored in Database Users table

texts:
I have set up a general user account using phpMyAdmin with no privileges. I can now use GRANT to give SELECT privileges:

GRANT SELECT ON DB.Table  TO general@localhost

Is it possible to apply this general user account to users within the table Users and then give them privileges to view only their own data?

For example:

GRANT SELECT ON DB.Booking TO general@localhost IDENTIFIED BY DB.Users.UserId


texts: MySQL 5.5.21 ON UPDATE CURRENT_TIMESTAMP incorrect

texts:
As the title says, I'm having a few issues with 'ON UPDATE CURRENT_TIMESTAMP'. Here is the SQL definition for the table:

CREATE TABLE `judgements` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) NOT NULL DEFAULT '0',
  `entry_id` bigint(20) NOT NULL,
  `group_id` bigint(20) NOT NULL,
  `comments` longtext,
  `rating` int(11) DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '1',
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`entry_id`,`group_id`),
  KEY `group_id` (`group_id`),
  KEY `entry_id` (`entry_id`),
  KEY `status_id` (`status`),
  CONSTRAINT `entry_id` FOREIGN KEY (`entry_id`) REFERENCES `entries` (`id`),
  CONSTRAINT `group_id` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`),
  CONSTRAINT `status_id` FOREIGN KEY (`status`) REFERENCES `status` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9082 DEFAULT CHARSET=utf8;

Records are inserted using the Wordpress database class ( $wpdb->insert ). All fields except id and modified are passed in an associative array to the function. This works as expected, the record is inserted and a timestamp is applied. The problem is that this timestamp is not always correct.
In seemingly random instances (around 15% of inserts) the timestamp will be anywhere up to 30 minutes in the future... I couldn't find any sort of pattern to this behaviour which makes the problem difficult to track down.
If it is relevant, this table experiences quite a high volume of inserts for several hours a day.
After searching Google and this site I am no closer to tracking down the problem. I did manage to find one forum post, from several years ago, where someone had a similar problem but the only reply was to file a bug report.
Has anyone else experienced this or have a theory as to why it is happening?

Thanks.


texts: Permanent count of waiting tasks (2) in Activity monitor

texts:
Now I'm observing in Activity Monitor permanent count of waiting tasks:

and at "Resource waits" tab I see next:

so, my question is: How to find and resolve this wait tasks?

I suspect that it related with transactional replication, which running on the server. This wait tasks I see only on Distributor. This issues appeared after restarting SQL server service.

sp_whoisactive - doesn't show any locks.

Thanks a lot for any information.


texts: How can I design a relational database with words that are all related to each other

texts:
I'm developing a simple language game about antonyms, synonyms and 'similar speaking' words.

If you play in the 'Synonyms screen', the game will show you two synonyms and two unrelated words and you have to guess wich are the synonyms.
If you play in the 'Antonyms screen', the game will show you two antonyms and two unrelated words and you have to guess wich are the antonyms.
If you play in the 'Speaking screen', the game will show you two 'similar speaking' words, will speak one of each and you have to guess wich is.

My problem is that you can have more than one synonyms, like:

angry: enraged, furious, irated, offended, etc..
love: affection, passion, devotion, etc..

My first idea is something like this one:

Table Synonyms:

+-----------+-------------------+
|   word    |     related       |
+-----------+-------------------+
| angry     |   enraged         |
| angry     |   furious         |
| angry     |   irated          |
| angry     |   offended        |
| enraged   |   furious         |
| enraged   |   irated          |
| enraged   |   offended        |
| furious   |   irated          |
| furious   |   offended        |
| irated    |   offended        |
| love      |   affection       |
| love      |   passion         |
| love      |   devotion        |
| affection |   pasion          |
| ....      |   ....            |
+-----------+-------------------+

And build the antonyms and speaking tables in the same way.

But it seems like a lot of work and it's difficult to add/remove one synonym.

Also, I don't know where I can put the information about how to speak every word. Maybe another table like:

+-----------+-------------------+
|   word    |  pronuciation     |
+-----------+-------------------+
| angry     |   ˈaNGgrē         |
| enraged   |   enˈrājd         |
| ....      |   ....            |
+-----------+-------------------+


texts: find and insert row to another table using mysql trigger


texts:
I'm using enum as an alternative to check constraint which is still not supported by mysql.
Could you clarify why optimizer doesn't take into account allowed values for enum ? For example,

CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null);
insert into test_1(val) values ('A');
1. explain select * from test_1 where val is null;
2. explain select * from test_1 where val ='C';

The first explain shows impossible where in Extra, but the second doesn't. There is no way val can contain 'C' (as well as null), and it can be deducted from table definition without looking into the table.

Thanks.


texts: How do I list all schemas in PostgreSQL?

texts:
When using PostgreSQL v9.1, how do I list all of the schemas using SQL?

I was expecting something along the lines of:

SELECT something FROM pg_blah;


texts: How to avoid primary key violation while doing inserts

texts:
I have a code which does insert into destination table from source, but at times we are ending up with deadlocks.

Due to deadlock , I have to rerun this code again.

while i do re-run , I end up with Primary key violation. Hence I want to add a check to insert only those rows which doesn't exist.

Can some help me with Tsql for this please

    declare @batchSize int = 1000
    INSERT INTO [dbo].[Destinationtable]
       ([PacketId]
       ,[DeviceId]
       ,[PacketDate]
       ,[PacketNumber]
       ,[Processed]
       ,[CreateDate]
       ,[NewReadingId])
 SELECT top (@batchSize)
       [PacketId]
      ,[DeviceId]
      ,[PacketDate]
      ,[PacketNumber]
      ,[Processed]
      ,[CreateDate]
      ,newid()
  FROM [dbo].[SourceTable]
  where Processed is null
  order by CreateDate

Error message when i run after deadlock

Violation of PRIMARY KEY constraint 'PK_packetid'. Cannot insert duplicate key in object 'dbo.destinationtable'. The duplicate key value is (1234567).


texts: Long Transaction On File Upload Locks Strategies

texts:
I need to upload files to some SFTP or FTP servers (using libcurl) and register them in database.

For this I have several tables

-- locations to store files - some SFTP and FTP servers
CREATE TABLE locations(
  id bigint,
  size_quota bigint, -- maximum amount of data, that location can store
  priority real      -- like metric for network interfaces - more is better
  /* server params like URL and etc omitted for simplicity */
);

-- files located on those servers
CREATE TABLE files(
  id bigint IDENTITY(1,1),
  loc_id bigint,  -- location id
  size bigint     -- file size
  /* file params like path and etc omitted for simplicity */
);

-- example data
INSERT INTO locations
VALUES (0, 100, 1.0) (1, 100, 2.0);

INSERT INTO files
VALUES(0, 50), (1, 60)

So, to register file in database, I need to chose a location for it. Basically its just SELECT TOP 1 id, FROM locations ORDER BY priority DESC; but i need to account size_quota of locations.

So, it can be

BEGIN TRANSACTION;

SELECT TOP 1 loc.id from locations loc
INNER JOIN (
  SELECT COALESCE(sum(size), 0) total_size, loc_id
  FROM files
  WITH (HOLDLOCK) -- note the holdlock I need to hold it to prevent quota overflow due to race conditions
  GROUP BY loc_id
) sq ON sq.loc_id = loc.id
WHERE loc.size_quote - sq.total_size > 50 -- 50 is the size of file being uploaded
ORDER BY loc.priority DESC

Then there comes file uploading operation, which is quite long - files are large - they can be several gigabytes in size.

And then

INSERT INTO files (loc_id, size) VALUES(chosen_loc_id, 50);
COMMIT TRANSACTION;

Obviously, that HOLDLOCK strikes performance hard because there is only one file can be uploaded at a time.

How can I evade that? I thought a solution with another table with space reserves, but there is possibility, that client will fail and reserve will stuck in database.


texts:
on mariadb at information_schema.global_status they have variable name  memory_used in bytes
this is actual memory use from mariadb ?
edit add example case
on my servers i have threads_running=10 and memory_used=2666 on MB so total 26 GB
but when run command free -mh is 89 gb
with configuration
...
innodb_buffer_pool_size = 70G
innodb_buffer_pool_instances = 70
innodb_log_file_size = 8G
...


texts: Postgresql can I access inline array items returned from function?

texts:
Say I have a function which returns an array. Can I access its items inline inside a select query? I can not use sub-querying since function relies on query parameters. And I can not declare outside variable for the same reason.

I expect somewhat like this:

select foo,bar, function(foo,bar)[1] as array_item_1, function (foo,bar)[2]  as array_item_2 from table...

Currently I return function(foo,bar) as array_items and iterate it in application which is really cumbersome. I want db to return prepared outputs.

Thanks.


texts:
When the autovacuum process is vacuuming a large table, will queries like DROP and TRUNCATE be blocked until vacuuming completed?
In the documentation,it says

  Also, the standard form of VACUUM can run in parallel with production database operations. (Commands such as SELECT, INSERT, UPDATE, and DELETE will continue to function normally, though you will not be able to modify the definition of a table with commands such as ALTER TABLE while it is being vacuumed.)

But I wonder if DROP or TRUNCATE is included.


texts:
SCD type 4 keeps the foreign keys to the original dimension and the mini-dimension in the fact table.
Why is the foreign key to the mini-dim not stored in the original dim?

Link to an article explaining SCD types: http://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/


texts: SQL Server 2017 Merge Not Running Triggers

texts:
Everywhere I am reading, when a merge statement happens, it will fire the triggers (INSERT, UPDATE, DELETE) in no particular order.  I have a basic merge:

MERGE dbo.Users WITH(HOLDLOCK) as tgt
USING #Users as src
ON tgt.ID = src.ID
WHEN NOT MATCHED BY INSERT
(..)
VALUES(..)
OUTPUT INSERTED.ID, src.UserID into #Results;

However, I have an insert trigger on Users but nothing ever gets run from the trigger perspective.  Follow-up update statements do get run and I see the results of those trigger executions, just not the inserts from the merge.

Is there anything I can do about that?  I'm surprised it's not working when it's documented that others do get that functionality to work...


texts: MySQL upgrade 5.0.88 to the latest version

texts:
I have some questions regarding upgrading from 5.0.88 to the latest version.

Why should I upgrade?
If so, which version should I consider?
What are all the known risks?
Is upgrading step by step suggested? Or is binary replacement suggested?


texts: Illegal XML character out of the extended event

texts:
I'm capturing extended events in SQL server. Trying to analyse the trace - converting the event data to xml an error comes up:

  Msg 9420, Level 16, State 1, Line 28
  XML parsing: line 1, character 731, illegal xml character

Investigating the issue, I find that there is a character of ascii number 3 in the sql_text action value. This value is passed by a client in form of dynamic SQL. This is of little concern here. The problem is that XE cannot handle weird characters in sql_text.

This is how I get to the data causing the trouble:

declare @data nvarchar(max)
declare @id int
SET NOCOUNT ON

if object_id('tempdb.dbo.#xerrors') is null --drop table #xerrors
begin
    select CAST(null as XML) as xml_event_data, *
    into #xerrors
    from sys.fn_xe_file_target_read_file
            (
                '...\Log\error_*.xet',
                '...\Log\error_*.xem',
                null,
                null
            )

    alter table #xerrors
    add id int identity(1,1) primary key not null

end

while exists(select 1 from #xerrors where (@id is null or ID > @id) and xml_event_data is null)
begin
    select top 1 @id = id, @data = event_data from #xerrors where (@id is null or ID > @id) and xml_event_data is null order by id

    print @id

    update #xerrors set xml_event_data = CONVERT(xml, event_data) where id = @id

end

select ascii(SUBSTRING(event_data, 731, 1)), event_data from #xerrors where id = 11253

And my session setup is:

-- TRIES TO ELIMINATE PREVIOUS SESSIONS
if exists(select 1 from sys.dm_xe_sessions xes where xes.name = 'error_capture')
      DROP EVENT SESSION error_capture ON SERVER
GO

-- CREATES THE SESSION
CREATE EVENT SESSION error_capture ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION (
        sqlserver.tsql_stack,
        sqlserver.sql_text,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.client_app_name,
        sqlserver.is_system,
        sqlserver.nt_username,
        sqlserver.username,
        sqlserver.transaction_id,
        sqlserver.session_id,
        sqlserver.database_id,
        sqlserver.database_context
    )
    --actions
    WHERE Severity > 10
)
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = '...\Log\error.xet' , metadatafile = '...\Log\error.xem')
GO

-- STARTS THE SESSION
ALTER EVENT SESSION error_capture ON SERVER STATE = START

Do you consider this a bug in the XE framework or am I doing something wrong? I believe all events should be valid XML...

The file paths have been intentionaly shortened


texts:
I want to know what logging options are available to me in MySQL; I find a lot more results about binary logging than I do about general/error logging and I'm not sure what options are available to me for such logs.

Could anyone tell me a list of the my.cnf variables I can set to get different types of logs?

Cheers!

Chris


texts: Can connect to SQL Server 2012 via OSQL and SQLCMD but not via SSMS

texts:
We have individual developer edition SQL Server 2012 databases set up on each of our developers PC's. All PC's are Windows 7, firewall is disabled and everything runs fine.

We're now looking to upgrade to Windows 8.1. We have had some problems with the firewall but have put in exceptions for port 1433 and 1434. We can connect using SQL or Windows authentication when using sqlcmd and osql, however if we try to connect via SSMS we get an error:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections (provider: SQL
  Network Interfaces, error: 26 - Error Locating Server/Instance
  Specified)(Microsoft SQL Server)

What's confusing me is that we can connect without any problems using osql and sqlcmd. SSMS has no problems when running locally on the computer.

TCP looks to be setup correctly and listening for connections (and accepting connections from osql and sqlcmd).

What else do SSMS need to connect to SQL Server?


texts: cons of linked server providers not running in "allow in process"

texts: what are the cons of having linked server providers not running in "allow in process"? SQL box crashed due to oracle provider crashing and it provider was running in "Allow in process", now have been advised to un-check allow in process.


texts: 2012 Transactional replication not delivering new articles

texts:
I have added a new article to an existing push publication with the following code

sp_addarticle @publication='statics', article='FlightBaggageMarkup_tbl',source_object='FlightBaggageMarkup_tbl'

  EXEC sp_addsubscription
  @publication = 'Statics',
  @subscriber = 'SURVIVOR',
  @destination_db = 'MLT-Replicated'

this is a push subscription with immediate_sync switched off, running continuously instead of a schedule

run the snapshot agent and see it picks up just the new table, ok great. but the table and the custom stored procedures never appear at the subscriber and then replication falls over if you try to change a value in the source table as it cant find the stored procedure to call on the subscriber ?  I have only seen this behavior since upgrading to 2012, but this maybe a coincidence


texts:
I would like to know if SERVICE_NAME is always configured for a Oracle standalone and RAC setup or it's optional to set up.

In my code I would like to connect to Database using SERVICE_NAME. But if SERVICE_NAME is not always present then I will have to have option to connect using SID. Please advice.


texts: Is it possible (or good practice) to register data upon Deletion of a record? What would be a better solution?

texts:
The problem is the following.

A company has one or more representatives (a person who speaks on behalf of the company).

When a representative is deleted, it's desired to keep it in an list called "Last representative". So if the company has no representatives, one can look at that list and see what were the past representatives.

Is this possible? Or, is this a good solution/practice? What would be a better solution?

Thanks


texts: Inserting and updating and selecting at thousands of times per second

texts:
I have a table that gets inserted, updated and selected from thousands of times within one second. I am having deadlock issues though.

The database has 2-5 simultaneous 1000+ row inserts using Linq to Sql.
40 times per second, there is also a select statement from that table, and if a condition is true (95% of the time it is), an update happens with code similar to this:

create procedure AccessFile (@code, @admin) AS

declare @id int, @access datetime, @file string

select @id=Id, @accessed = accessed, @file = file from files where code = @code

IF @admin<> 0 IF @accessed is null begin

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

update files set accessed = getdate() where id = @id

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

end

select @id as Id, @file as File

It seems as though it is the updates conflicting with the inserts that are causing the deadlocks.

The example is a 1 to 1 with the stored procedure, only difference is the names. Assume the 1 and 2, regardless of the stored procedures names.


texts: Aurora / MySQL 5.6 process list thousands of threads in state 'cleaned up'

texts:
I have a scaling issue with an application, it uses Aurora as the DB and uses Slick as the ORM. I am not sure where the issue lies but I have a question releated to MySQL / Aurora that I hope will shed some light on my issue.

The application needs a significant number of connections in the pool otherwise the application starts failing. However the when I look at INFORMATION_SCHEMA.PROCESSLIST I can see a significant number of threads in the 'cleaned up' state.

I can't see 'cleaned up' on the list of general states - https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

What does 'cleaned up' mean exactly? ( I can guess but I can't find any documentation )

below is a count of the number of threads total vs the number in 'cleaned up'

mysql> select count(*) from INFORMATION_SCHEMA.PROCESSLIST where STATE = 'cleaned up' order by time desc ;
+----------+
| count(*) |
+----------+
|    11982 |
+----------+
1 row in set (0.08 sec)

mysql> select count(*) from INFORMATION_SCHEMA.PROCESSLIST order by time desc ;
+----------+
| count(*) |
+----------+
|    12007 |
+----------+
1 row in set (0.08 sec)


texts: Should mysql database be replicated in a replication setup

texts:
When I do a replication setup I always ask myself should I put mysql and other internal databases (like information_schema) in the binlog_ignore_db option.
Documentation about replication I read never mention what to do with mysql, but I don't see any value (and rather see possible problem) to have these databases replicated.
The only benefits would be having all the users and permissions the same everywhere but I had a replication issue last week.
I created a user on both nodes of my setup using a configuration system, and the replication broke when the slave tried to replay the user creation coming from the logbin which was already existing so that's can be problematic.
What do you think?


texts: How can I restrict which computers can connect to an instance of SQL Server?

texts:
I'm not sure if this is a more a database or sysadmin question, so let me know if it doesn't fit.

Because of some licensing agreements, I've been asked to find a way to restrict which computers on our domain can connect to an instance of SQL Server.

My original idea was to try and create a login trigger to check against a list of computers to see if they matched but I've managed to lock myself out of the server 3 times already. Further to this, apparently using HOST_NAME() to identify the connecting client is not not secure.

How else could I do this from within the SQL Server environment or do I need to look at setting up Firewall rules elsewhere?

Just to be clear. We want it so any user can connect from a specific collection of computers.


texts:
I have a database with sale data for the past 5 years, 2012 - current.  I am in need of showing a breakdown by employeeID and the monthly sale amount.  I know this syntax will work, but it is going to be quite cumbersome to add a new case statement every time we roll into a new month.  Is there a quicker/more efficient way to accomplish this?

This is sample DDL (sample as it is only a very few rows)...

Declare @Monetary Table(empID varchar(10), empsaleamt float, empsaledate date)
Insert Into @Monetary (empID, empsaleamt, empsaledate) Values
('123abc', 100.00, '20160101'), ('123abc', 200.00, '20160101'), ('123abc', 300.00, '20160201')
,('456cde', 200.00, '20160101'), ('456cde', 100.00, '20160201'), ('456cde', 100.00, '20160301')

Select
empID
,Sum(case when (Year(empsaledate) = '2016' AND Month(empsaledate) = '01') Then empsaleamt Else 0 End) As Jan16Sales
,Sum(case when (Year(empsaledate) = '2016' AND Month(empsaledate) = '02') Then empsaleamt Else 0 End) As Feb16Sales
,Sum(case when (Year(empsaledate) = '2016' AND Month(empsaledate) = '03') Then empsaleamt Else 0 End) As Mar16Sales
FROM @Monetary
Group By empID
ORDER By empID ASC


texts:
I have created couple of jobs on SQL SERVER out of SSIS packages , i can see command line for some of them start with /DTS and for some /SQL , even though all jobs creates in same version and perform data load operation only.
What is the difference between these options?


texts: Full text search with mispelled greek words fails to retrieve the epxected results

texts:
I have the following table named search_index:
id PK SERIAL
key UNSIGNED INTEGER
value TEXT

And over the table I ηαωε τηε following values
id  | key | value
--- | --- | ---
1   | 1   | 'χαλανδρι'
2   | 5   | 'χαλανδρη'
3   | 6   | 'μπουγάτσα χαλάνδρι'
3   | 9   | 'μπουγάτσα αθήνα'

And then I run the following query:
select * from search_index where to_tsvector(value) @@ to_tsquery('χαλανδρι');

And I retrieve the values:
id  | key | value
--- | --- | ---
1   | 1   | 'χαλανδρι'
3   | 6   | 'μπουγάτσα χαλάνδρι'

Whilst I expect the following results:
id  | key | value
--- | --- | ---
1   | 1   | 'χαλανδρι'
2   | 5   | 'χαλανδρη'
3   | 6   | 'μπουγάτσα χαλάνδρι'

Do you have any idea why I fail to retrieve any result containingχαλανδρη (I mislelled the iota ι with itta η;


texts:
I have two tables. A client table and a vehicle table, the two are linked in a unit lookup table.
My constraint is that a vehicle cannot belong to a client if it already is associated with another client.
This is what I have so far:
ALTER TABLE unit
   ADD CONSTRAINT unit_vehicle_across_client_constraint
   EXCLUDE USING GIST (client WITH =, vehicle WITH <>) DEFERRABLE;

But it doesn't seem to work. I guess I just don't really understand how the exclusion parameters really work.
Any suggestions?


texts: How do I determine what MDF files are in use

texts:
I just right-click deleted all my databases that were not in use. However all the MDF and LDF files are still in the data folder on the machine. What is the best way to determine what MDF and LDF files I can remove since the database no longer exists in SSMS.

Also is this the best way to delete unused databases? (The right-click -> Delete method.)


texts: Query execute slowly when changed compatibility Level of my database

texts:
I use SQL Server 2014 and have a database with compatibility level 110 (SQL Server 2012).
also I have a stored procedure on it that execute about 10 second. When I change compatibility level of my database to 120 (SQL Server 2014), execution time of my stored procedure became about 30 minute.
I use following steps but execution time of my stored procedure not changed.

Step 1: Use DBCC CHECKDB command.
Step 2: Execute DBCC UPDATEUSAGE on my database.
step 3: Rebuild all indexes on my database.
step 4: Drop all index and all foreign key on my database and create all again.

After all of above step my stored procedure execution time not changed.
But when compatibility of my database returned to 110, my sp run about 10 second.
I check above step multiple and get a single result.
How must I do. and how can I change compatibility level of my database to 120 (SQL Server 2014).
Thanks in advance.


texts: Syntax error around converting timestamps to time

texts:
I am writing a function that requires some comparison between the difference date and time parts of the timestamp data type. However it keeps running into syntax errors and I cannot find out what is going on. I looked into this post and keep having alternating errors like

psql:ERROR:  operator does not exist: time without time zone >= timestamp with time zone
and

plpgsql: No function matches the given name and argument types. You might need to add explicit type casts

Can anyone enlighten me what is wrong and what could be a direction I can head towards?

create or replace function riders_available_at_date_time(specified timestamp)
returns table(riderId integer) as
    $$
    begin
        select F.riderId
        from FullTimeRiders F
        where specified::date between F.startDate and F.startDate+27
        and exists (
            select 1
            from MWSWorkDays M join ShiftsInformation S on (S.shiftNumber = M.shift)
            where M.riderId = F.riderId
            and (specified::time without time zone between to_timestamp(to_char(firstStartHour, '99'), 'HH24')
                and to_timestamp(to_char(firstEndHour, '99'), 'HH24')) or
                (specified::time without time zone between to_timestamp(to_char(secondStartHour, '99'), 'HH24') and
                    to_timestamp(to_char(secondEndHour, '99'), 'HH24'))
                  )

        union
        select riderId
        from PartTimeWorkingHourIntervals P
        where specified between calculate_start_date(P.dayIndex, P.startDate)+P.startHour
            and calculate_start_date(P.dayIndex, P.startDate) + P.endHour;
    end;
    $$
language plpgsql;

create or replace function calculate_start_date(index int, starting date) returns date as
    $$
    declare
        position integer;
    begin
        select extract(dow from starting::date) into position;
        index = (index - position)%7;
        return starting + index;
    end;
    $$
language plpgsql;


texts: Default Schema allows dbo table selection without using schema prefix

texts:
I have created a Login and a database user on my server as follows:

USE master
GO
CREATE LOGIN MyLogin WITH PASSWORD=N'Password123!'
GO
USE AdventureWorks2014
GO
CREATE USER MyLogin FOR LOGIN MyLogin
GO
ALTER USER MyLogin WITH DEFAULT_SCHEMA=HumanResources
GO
GRANT SELECT ON SCHEMA :: Production TO MyLogin
GO
GRANT SELECT ON SCHEMA :: HumanResources TO MyLogin
GO
GRANT SELECT ON SCHEMA :: dbo TO MyLogin

When I log in as MyLogin I can now select objects in the HumanResources schema without using the four part name (as HumanResources is the default schema for this user)

SELECT * FROM HumanResources.Department

can now be run without the schema name:

SELECT * FROM Department

This is fine. If I then try to select from a table in the Production Schema without using the schema prefix:

SELECT * FROM ProductDescription

I get an error as expected. This can be remedied by using:

SELECT * FROM Production.ProductDescription

So based on this, I need to specify the schema name when selecting from any table outside the default schema.

So why when I select from a table in the dbo schema, do I still not need to use the schema prefix?

SELECT * FROM DatabaseLog

returns results

I am confused. I thought if a schema name does not prefix the table in a SELECT statement, that the default schema is used and selecting from a table in any other schema would require the schema prefix.

Is the dbo schema an exception to this rule?


texts: Does an empty column value occupy same storage space as a filled column value?

texts:
I have a table with 2 columns. The type of both columns is set to varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not empty?

In other words, will MySQL reserve storage space for the column (depending on its type) when a row is created?


texts: SQL Server 2008: How to send an email when a step in a SQL Server agent job fails, but overall job succeeds

texts:
I have a SQL Server job with 6 steps.  Steps 5 and 6 must be run regardless of any failures in the first four steps, so these first four jobs are set to skip to step 5 if they fail.

However, if steps 5 and 6 then succeed, the whole job is regarded as a success. I have an email notification set up for the job failure, but I do not receive an email if any of the first four steps fail due to the overall job being considered a success. I would like this to happen.

It would not be ideal to split out the first four steps into a separate job, as they must be completed before steps 5 and 6 begin.

Please can anyone give me advice to solve this problem so that:

Steps 5 and 6 run even when any of steps 1-4 fail.
Steps 5 and 6 begin strictly not before steps 1-4 complete.
When any of steps 1-4 fail, an email notification is sent indicating the step that failed.

Thanks very much in advance for your help.


texts:
From what I understand the system database is used to hold temporary tables, intermediate results and other temporary information.

On one of my database instances I have a tempdb that is seems very large (30GB).  This database has not been modified (as in "last modified date" on the mdf file) in over a week.  Is it normal to have the temp db remain that large for that long of a period?  It seems to me that it should be updating fairly often and returning space that it is using fairly quickly...

Am I way off here or is SQL Server doing something weird?

FYI:  This is a SharePoint 2010 database, not sure if that makes a difference.

UPDATE:

Martin, your query returns the following:

usr_obj_kb|internal_obj_kb| version_store_kb| freespace_kb  | mixedextent_kb
6272      |1984        | 64              | 30418048      | 4416


texts: How to add a new column with auto increment ,increment factor, min-value and max value in psql?

texts:
I have table t1 and I would like to add a new column app_id with the properties given below

auto increment
increment factor(increment by 4)
min-value/initial value(100)
max-value(2000)

I have tried

alter table t4
add column app_id int not null auto_increment = 100 ;

but which is not worked.is it possible to add such a column with the properties mentioned above?


texts:
My query returns roughly 590 rows and 8 columns.  The issue that I have is that from start to finish the query takes 2 minutes 30 seconds to complete.  The great group of people on here have taught me a lot on how to write more efficient queries, so here is another!

I am using a date variable  and not a datetime since my variable only contains a date - and I am also using storing my date(s) in the yyyymmdd format as suggested by Aaron Bertrand - Bad Habits To Kick.

Is there anything I can do to optimize this query and have the results return faster?

DECLARE @Startdate date = '20170101',
        @Enddate   date = '20170131';

WITH fc As
(
    Select
    Teacher
    ,Team
    ,fc
    FROM [Helper].[dbo].[fc]
)
,ia As
(
    Select
    Teacher
    ,tia
    FROM dbo.ia
    WHERE [hiredate] >= @Startdate
    AND [hiredate] <  DATEADD(DAY,1,@Enddate)
)
,inb As
(
    Select
    Teacher
    ,tinb
    FROM inb
),
ripcord As
(
    Select
    Teacher
    ,pit
    FROM [homebase].[dbo].[rip]
)
,YRTR As
(
    Select
    Teacher
    ,totamt
    FROM totamt
    WHERE CAST([begindate] As DATE) BETWEEN CAST(DateAdd(yy, -1, @startdate) As Date)
                                     AND CAST(DateAdd(yy, -1, @enddate) As Date)
)
Select
DISTINCT rost.[Teacher] As Teacher
,[Team Name] = fc.team
,[TIA] = ROUND(SUM(ISNULL(ia.tia,0)),0)
,[SUM] = CAST(ROUND(SUM(ISNULL(inb.tinb,0))+SUM(ISNULL(rip.pit,0)),0) As INT)
,[YR] = ROUND(SUM(ISNULL(tr.totamt,0)),0)
,[fc] = ISNULL(fc,0)
,[1st Count] = COALESCE(b.[students],0)
,[2nd Count] = COALESCE(c.[potstudents],0)
FROM dbo.roster rost
LEFT JOIN fc fc
ON rost.Teacher = fc.Teacher
LEFT JOIN ia ia
ON ia.Teacher = rost.Teacher
LEFT JOIN inb inb
ON rost.Teacher = inb.Teacher
LEFT JOIN ripcord rip
ON rost.Teacher = rip.Teacher
LEFT JOIN YRTR tr
ON rost.Teacher = tr.Teacher
OUTER APPLY
( SELECT
        DISTINCT Teacher [Teacher]
        , COUNT(students) AS students
    FROM students
    WHERE Teacher = rost.Teacher
    AND regdate >= @Startdate
    AND regdate <  DATEADD(DAY,1,@Enddate)
    GROUP BY Teacher
) AS b
OUTER APPLY
(
    Select
    DISTINCT Teacher Teacher
    ,COUNT(potstudents) As potstudents
    FROM dbo.potstudents
    WHERE Teacher = rost.Teacher
    AND returndate >= @Startdate
    AND returndate <  DATEADD(DAY,1,@Enddate)
    GROUP BY Teacher
) AS c
GROUP BY rost.[Teacher],fc.TEAM, fc.fc,b.[students],c.[potstudents]
ORDER BY rost.[Teacher] ASC

EDIT (forgot the link, sorry)
At the requet of @sabin bio - here is a link to the Query Execution Plan

Also, the CTE's query views which have no indexes on them.


texts: Delete with subquery with no unique columns

texts:
I have a table LIKES (ID1, ID2). Both id1 and id2 are non unique columns.

Database is SQLite

delete from LIKES where ID1 = 10   /// Wrong there more then one record with ID1=10
delete from LIKES where ID2 = 's ...'  /// Wrong there more then one record with ID1=155
delete from LIKES where ID1 = 10 and ID2='s ....' /// right query but i have to find out
             values of id1 and id2 before to make run this query

delete from LIKES where id1=m and id2=t (select m, n  from table where ...some condition)

The last query does not work


texts: How do I archive and restore selected tables using SQL Server?


texts: How do I determine if FORCE LOGGING is on for the entire database?

texts:
How can I determine if force logging is on, at the database level. (Not asking about finding out if force logging is on for particular tablespaces.)

What rights are needed to perform the check of the above. (I have SELECT_CATALOG_ROLE, but other than that, pretty limited rights.)

I've tried to find out how to query from the Reference and the Administrator's Guide manuals.

Version:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0  Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


texts: The network I'm using doesn't allow port 3306 so I can't connect to Google Cloud SQL. How do I set up a proxy server (on a different network that allows 3306) that I could connect to via SSH to communicate with GCS directly using Navicat/MySQL Workbench?


texts: Access SQL Server Configuration Manager on a remote server

texts:
I am trying to add a traceflag as a startup parameter to a distant SQL Server 2016, installed on a Windows 2016 core server and I am following these instructions

At this point in the document:

  In the console tree, expand Services and Applications, and then expand
  SQL Server Configuration Manager to manage the remote computer's
  services.

There is no SQL Server Configuration Manager here.

Is this a permissions issue?
Does anyone know what permission needs to be added and where so I can see SQL
Server Configuration manager under Services and Applications?
Does core have anything to do with this?

Thanks,
Craig


texts:
TL;DR : How can I improve insertion time in my table so that every inserts take under 100ms to perform?

This table is used by a webservice which receives transactions one by one so I can't group inserts. The timeout of the webservice must be under 150ms.

Infrastructure : AWS
Application (php) : EC2 t2.micro
Mysql : RDS t2.small - 100GB magnetic storage

Here are the measurments I've done in production

Top left hand corner : We can see that a lot of inserts take more than 500ms and some even take more than 1s. Only a few are under 50ms. (red bars are 95th percentile)

Top right hand corner : RDS cpu is always low (memory usage is also constant and fine)

Bottom left hand corner : Number of inserts in the table

Bottom right hand corner : blue line is write IOPS, red line write lentency, blue bars is disk_queue_depth * 100. Disk queue is pretty much always below 1. We can see huge spikes in write lentency when everything else looks fine : no increase in IOPS nor in hardware ressource consumption.

I've created a script to measure insertion time in account_transaction in isolation :

// pseudo code
for ($i=0; $i<100; $i++) {
    $ts=microtime(true);
    $db->insert(data);
    echo microtime(true)-$ts;
}

Output for 100 inserts : (sorted)

0.012468814849854
0.012633085250854
0.012740135192871
0.012858867645264
0.012872934341431
...
...
...
0.038048028945923
0.040112018585205
0.040735960006714
0.042028903961182
0.42462396621704
Avg : 0.025037434101105

I pretty much always end up with the same results : 1% or 2% of the inserts are very slow. Same if i perform 1000 inserts.

What I've tried

disable all indexes except PK : no improvements.
rebuild all indexes : no improvements.
innodb_flush_log_at_trx_commit=0 : great improvements :

slow inserts totally disappeared
average insert time 5 time faster

switch to a bigger RDS (m4.large with 100GB SSD with 1000 IOPS preprovisioed) : some improvements :

slow inserts almost disappeared : maybe 0.1% if any and it rarely exceeds 100ms
average insert time 3 time faster

Unfortunately innodb_flush_log_at_trx_commit=0 isn't an option as I can't afford to lose any transaction.

My thoughts

I would expect inserts to be pretty fast most of the time (<1ms) even on basic hardware. Table only has 4 indexes and 200k rows. Am I wrong to assume that?

I suspect the bottleneck to be in the I/O realm because of the test with innodb_flush_log_at_trx_commit=0 but the metrics I've got from AWS doesn't seem to confirm that. Am I wrong again?

Table account_transaction

CREATE TABLE `account_transaction` (
  `id` char(21) COLLATE utf8_unicode_ci NOT NULL,
  `seqid` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` char(21) COLLATE utf8_unicode_ci NOT NULL,
  `token` bigint(20) unsigned DEFAULT NULL,
  ...
  ...
  `creation_date` datetime NOT NULL,
  `processing_date` datetime DEFAULT NULL,
  PRIMARY KEY (`seqid`),
  UNIQUE KEY `id` (`id`),
  KEY `consolidation_idx` (`trans_link`,`token`),
  KEY `idx_token` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=239304 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

id look like 1473430240HBCRSPL9FPB = unix epoch + 10 random chars : we use this format vs a standard UUID to preserve good insert performance. Always 21 char.

seqid is used to keep track of the row number. Our id has a random part so we can't use it for that purpose.

select count(1) from account_transaction = 239k

Questions

Is there a way to remove the high lentency spikes?
Should i go with bigger hardware to improve performance? My traffic is really low...
I must ensure that 99.9% of inserts are done under 100ms. Is that realistic on RDS mysql?


texts:
Is there any way to create a MS SQL Server Linked Server to connect to MySQL through SSH?
I managed to connect to MySQL through SSH using other database management tools like HeidiSQL or DBeaver, however I'd like to have this setup up for SSMS. I downloaded the MySQL ODBC driver but I can't find where to setup the SSH connection


texts:
We have some SQL Servers that are backed up using T-SQL and some that are using 3rd party backups (like Egnyte).

Is there a way to setup alerts inside SQL Server for that? I also need a way to test so we can be sure the alerts are working.

Any help is appreciated.


texts: Users who are connected into the database

texts:
I need some help on how to find all the users that are connected to one of my database. I should check this on weekends to find out all the users that connected during the week.

Thank you.


texts: MySQL: Does Amazon's RDS Multi-AZ feature use binary logs?

texts:
We are currently running our database in RDS - just one master (no read slaves), with a Multi-AZ deployment for fail-over durability.

I am contemplating some performance tweaks, one of which is disabling binary logs. I know they're used for master->slave replication, but I am not sure whether Amazon uses the same mechanism to support its Multi-AZ set-up or not.

I don't want to accidentally break the Multi-AZ feature by disabling binary logs.


texts:
I have a large MySQL data backup file which consists all the databases. But I want to restore only a few number of databases from above.

Is there a good way to split the sql file into several files per database?


texts:
I'm using SQL Server 2014 and I have a table with one column containing a CSV string:

110,200,310,130,null

The output from the table looks like this:

I want to select the second column as multiple columns, putting each item of the CSV string in a separate column, like this:

So I created a function for splitting a string:

create FUNCTION [dbo].[fn_splitstring]
(
    @List nvarchar(2000),
    @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
    Id int identity(1,1),
    Value nvarchar(100)
)
AS
BEGIN
    while (Charindex(@SplitOn,@List)>0)
    begin
        insert into @RtnValue (value)
        select
            Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

        set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    end
    insert Into @RtnValue (Value)
    select Value = ltrim(rtrim(@List))

    return
END

I would like to use it similar to this:

select Val , (select value from tvf_split_string(cchar1,','))  from table1

But the above code obviously won't work, because the function will return more than one row causing the subquery to return more than one value and breaking the code.

I can use something like:

select Val ,
(select value from tvf_split_string(cchar1,',') order by id offset 0 rows fetch next 1 rows only ) as col1,
(select value from tvf_split_string(cchar1,',') order by id offset 1 rows fetch next 1 rows only ) as col2,
................
 from table1

but I don't think it's a good approach.

What is the correct way to do it?


texts:
We have an Access db that has around 300 linked tables (to SQL Server tables). To switch environments, we run some VBA code that re-links all these tables to the relevant server. But this seems to be very slow. Approximately 0.25 seconds for each table, so the whole process can take almost 2 minutes.

I thought that I could use the Split Database feature to create a backend db that has linked tables that link to the dev environment, and another backend db that has linked tables to the live environement. Then the process of changing environments would be to programmatically tell the front-end db which back-end db to use.

However, when I tried the Split Database wizard, the back-end db it created has no linked tables, only the local tables.

Has anyone got some suggestion as to achieve that I'm after?


texts:
I'm new to DB design and was wondering if the following design can be improved.
I want to create a bookkeping database which handles these use cases:

For a given customer record all the transactions
For a given customer give me the total balance
For a given customer give me the total balance for a given time range

I was thinking of storing this in DynamoDB. The table schema would look like:
PrimaryKey: transactionId
Attributes: [customerId (string), timestamp (epoch millis), transactionAmount (double)]

The entries in these tables will never expire and we can expect around 1000 new transactions per second across many customers. As for getting the balances, we can expect around 10 reads per second.
I would then have two reverse indices:

customerId - to support getting the entire balance for the customer
customerId + timestamp (as sort key) - to support getting the balance within a given time range

Both of these indices would be used to get the balance which I will sum up on the server side.

I think this should scale pretty well and since I am use index on the table the query time should be pretty fast as well since I won't be scanning the entire table.
Any feedback or callouts would be appreciated.


texts:
I have an installation script for Windows to install mariaDB as service:

   mysql_install_db --datadir=c:/mariadb/data --service=MariaDB

It works, but I want to change the default my.ini file adding innodb_file_per_table=0 setting before default table creation.
How can I change the my.ini before default db creation?


texts: Handling growing number of Tenants in Multi-tenant Database Architecture

texts:
Handling a modest number of customers (tenants) in a common server with separate databases for each tenant's instance of the application is relatively straightforward and is normally the correct way to do this.  Currently I am looking at the architecture for an application where each tenant has their own database instance.

However, the problem is that this application will have a large number of tenants (5,000-10,000) with a substantial number of users, perhaps 2,000 for a single tenant.  We will need to support growing the system by several tenants every week.

In addition, all tenants and their users will be presented with a common login process (i.e. each tenant cannot have their own URL).  To do this, I need a centralised login process and a means to dynamically add databases to the system and register users.

How could the registration and database creation process be automated robustly?
Is the process of creating and registering tenants' databases on the system likely to cause performance or locking issues.  If you think this could be an issue, can anyone suggest ways to mitigate it?
How can I manage central authentication in a way where user credentials will be associated with a particular tenant's database but the user can log in through a common page (i.e. all through the same login URL, but their home application will be on some specific tenant's database).  The tenants will have to be able to maintain their own logins and permissions, but a central login system must be aware of these.  Can anyone suggest a way to do this?
If I need to 'scale out' by adding multiple database servers, can anyone suggest what issues I might have to deal with in managing user identies across servers (impersonation etc.) and some way to mitigate those issues?


texts: What is the most efficient way to create new MySQL database

texts:
I need to setup a development environment for several developers. Each of them needs to test the software with a "fresh" MySQL database. There is a SQL file with many CREATE, ALTER and INSERT queries.

Currently there is a PHP script with mysqli::multi_query that creates a new database and performs all queries from the SQL file. It is called each time when some developer needs a fresh instance of database. But it takes too much time to execute all needed queries.

I tried to change script to execute mysql < my_pre_mysqldumped_file.sql, but it is almost same slow.

Also, I tried to have an "initial" database and copy each table with CREATE TABLE ... LIKE ..., but it does not copy foreign keys.

So, the question: what is the fastest way from server performance point of view to create a new one or copy existing MySQL database?


texts:
Imagine I have three servers.
x.x.x.31 - master
x.x.x.32 - slave
x.x.x.33 - slave

If I run a simple update in master. What does the slave servers done. run the update command or copy the updated data from master?


texts:
I have a SQL Server job which has got different steps, including running a SSIS package. Currently it is on my PC (my local instance).

I should deploy it to UAT and assume some steps need to be taken to deploy it, in terms of creating login which run it, removing my login from it, etc.

What are the steps involved to deploy it successfully? Please note there is a CLR function call in this job as well; what would you recommend for required security settings around that?


texts: Two-step Replication between SQL 2008 and 2017

texts:
I am currently using transactional replication from several SQL 2008 publishers to a central 2014 subscriber/distributor. I need to replicate this data to a 2017 server, which would require a 2017 distributor.

Since 2008 -> 2017 transactional replication is not supported, I am trying to replicate in a second step from the 2014 server.

2008 -------------------> 2014 ------------------> 2017
         Dist: 2014                Dist: 2017

However, I cannot figure out how to get the 2014 server to recognize the 2017 server as the distributor for its publications, while still acting as the distributor for its subscriptions. Specifically, the 2014 server is configured as a publisher at the remote 2017 distributor, but when I try to enable a 2014 database for publication in the GUI, it asks me to first enable the server as a publisher on the local 2014 distributor.

So I want to know:
1. Is it possible to configure a local distributor for subscriptions and a remote distributor for publications?
2. Could I just use the 2017 distributor for both legs? Is 2017 supported to act as a distributor for the 2008 -> 2014 replication?
3. Is there some other best practice for transactional replication from 2008 -> 2017?


texts: Caching queries that last longer then then X seconds

texts:
Let's assume that I want work with query cache enabled. For example let's set it to 128mb value but my working enviorment is very random and dynamic, so cache is full in 1 minute after setting it.

When checking

SHOW STATUS LIKE "qcache%";

Qcache_lowmem_prunes value is growing dangerously high because of high ammount of random queries.

So let's go the point of this question:
How to cache only that queries that are taking longer then ie: 1 sec?

Is it even possible with query cache enabled or is it better to turn it off for good in such dynamic enviorment? Are there any different aproaches to cache those long queries?

Let's assume that 1 second for query is achieved due to low budget hardware, not from bad query construction.