texts: Help needed in creating a SQL statement containing four joins

texts:
Please help. Given a studentID and a TagNumber i want to get the UserID related to that TagNumber. The difficult condition is that TagNumbers can be duplicate within a Users table. So we can pinpoint the exact UserID using the UserCities table as a student is linked to a school and a school is linked to a city and UserCities table have linking information for users and cities. You can assume that the given studentID:45 and TagNumber:99.

Users:

UserID, TagNumber

Students:

StudentID, SchoolID

Schools:

SchoolID, CityID

UserCities:

UserID, CityID

I have tried this so far:

select
    u.UserID
  from
    UserCities uc  inner join School sc on uc.CityID = sc.CityID
    inner join Student st on sc.SchoolID = st.SchoolID
  where
    st.StudentID = 9


texts:
I am running a database for log analysis. At the moment I use a MySQL database and the table for my analysis looks like this:

UUID
REQUEST_ID
REQUEST_TIMESTAMP
RESPONSE_TIMESTAMP
RUNTIME
SERVER_NAME
EXSIGHT_NAME

I use this table to create views for each entry, for 5 minute aggregation and for a daily aggregation. I am inserting about 400.000 entries a day. At the moment there are about 70 Million rows in this table.

My actual problem is, that my queries are getting slow, my insert/update queries as well as my aggregation queries.

So I created a second table for my daily aggregation. Once a day a job will run, to make an aggregation for the last day. A second job will delete all entries which are older than 30 days from the original table.

My question:
Is this the right approach or would be a different table structure or even a another database (e.g. NoSQL, Graphdatabase, etc.) better?

[EDIT]

Select for daily aggregation:

select
        date_format(REQUEST_TIMESTAMP,'%Y-%m-%d 00:00:00') as INTERVAL_START,
        null as INTERVAL_END,
        count(REQUEST_ID) as Anzahl,
        FORMAT((sum(RUNTIME)/count(REQUEST_ID))/1000,0) as dStime
    from REQUEST_LOGS
        where EXSIGHT_NAME like (case when '<EXSIGHT>' = 'alle' then '%' else '<EXSIGHT>' end)
        and SERVER_NAME like '<SERVER>'
        and (REQUEST_TIMESTAMP between '<FILTERFROMDATE>' and '<FILTERTODATE>')
    group by INTERVAL_START
    order by INTERVAL_START desc

Indexes:


texts: Is a distributed database needed for a low-data use, quick read, low write, AP system?

texts:
this may not be the proper place to ask this question, but I was not welcome posting it on StackOverflow so I thought this may be a good place. If this is the wrong place, please refer me to the right place. Anyways!

Right now I am using a distributed database to store encryption keys. We have multiple nodes that all need to use these whenever a device connects, so all nodes must be able to quickly access and retrieve the keys. This means that if the database is down, our service doesn't work, so the uptime is incredibly important. Reads happen 50x more often than writes, so consistency is not super important, as the keys don't change often. The most amount of data storage we could possibly need is 8GB and that would cover 10m users - so the data per user is incredibly small.

I realize now, after a long while of using this distributed database, that it may not be the right solution for my needs. Because our data overhead is so low, keys might as well be stored in RAM and a copy on disk. Also, distributed databases are usually built to store LOTS of data, so they aren't necessarily geared towards our idea of small data + quick access times.

So, I guess my question is, is a distributed database necessary here, or would a relational be better for us? Maybe an NDBM or relational?


texts: How do I list all columns for a specified table that begin with a specific string

texts:
How to select all the columns whose names begins with "img" from a PostgreSQL table?

I wrote that, but doesn't work

select 'img%' from mytable


texts: Retrieve linked servers out of system database files?

texts:
I have the data and log files of a SQL-Server that has crashed. It's user databases have been restored. I am looking for the jobs and linked servers that have been there before the crash. I attached the old system databases and looked ad some views. Jobs have been found quickly in msdb.
but sys.servers and sys.sysservers seem to query and display the current server always.

I tried:

select * from sys.servers
select * from msdb_old.sys.servers
select * from master_old.sys.servers

but all of them return the same, only the current server config.

Are the linked server objects persisted and queryable anywhere in the old files?


texts: What is the purpose of %ORACLE_HOME%/dbs directory in Windows Environment?

texts: I see some files in there like an init.ora and opctrn11.h, but what is the purpose of this directory, if we use %ORACLE_HOME%/database to store the p/spfile instead?


texts: Expanding contractions/number ranges into separate records

texts:
I am planning the migration of a directory of image files plus a FileMaker-database containing the corresponding metadata into an Imagic IMS database (formerly known as ImageAccess). The vendor provides a Java “Datasheet to xml conversion tool” which creates an XML file for importing image metadata from a tab-separated file. The images' filenames are made up by their image reference numbers listed in the FileMaker database.

In the source FileMaker-database the image reference numbers are often contracted, making a sample export record describing four images look something like this:

ref        subject                    photographer
123/3-6    Building A, Living room    Photographer A

For the program converting the tab separated file to XML, these four images should be listed as:

ref        subject                    photographer
123/3      Building A, Living room    Photographer A
123/4      Building A, Living room    Photographer A
123/5      Building A, Living room    Photographer A
123/6      Building A, Living room    Photographer A

How can I best expand these contracted records into separate lines?


texts: How use cascade delete for array field in PostgreSQL?

texts:
How can i remove the value from array field?
I need to delete the user id from the university table's user_id array field if exists when i delete any users. so can i do this by using delete cascade  or any other method? please help me on this

Table:

CREATE TABLE IF NOT EXISTS users
    (_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
    "userName" VARCHAR NOT NULL,
     active BOOLEAN NOT NULL);

CREATE TABLE IF NOT EXISTS university
    (_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
    "universityName" VARCHAR NOT NULL,
    user_id integer[]);

User:

| _id | userName | active |
===========================
| 1   |  Abc     |   true |
| 2   |  vty     |   true |
| 7   |  hyu     |   true |
| 9   |  Agc     |   true |

University:

| _id | universityName    | user_id |
=====================================
| 1   |  HGV              |  {1,9}  |
| 2   |  CPC              |   {2}   |

1) if i delete user 2 then entire row should get deleted from university
2) if i delete user 1 then only value 1 should be removed from the user_id field from university table


texts: Can I use OUTPUT from an UPDATE in an INSERT?

texts:
I want to add a record to my app's "SystemSettings" table and set the PK value using the value from an UPDATE.  The PK value comes from the "TS_LASTIDS" table which contains the maximum PK value for each table in this application (MicroFocus SBM).  I need to increment the "TS_LASTID" column in that table and use the new value as the PK when INSERTING a new record into the "SystemSettings" table.

Insert Into
   ts_SystemSettings ( ts_Id, ts_Name, ts_LongValue)

Values (
   ( -- ******************** This subquery updates the PK in TS_LASTIDS and outputs the new value
      Update
         ts_LastIds
      Set
         ts_LastId=ts_LastId+1
      Output
         INSERTED.ts_LastId
      Where
         ts_Name = 'SystemSettings'
   ) ,  -- ********************
   'NSLastChangeId' ,
   1 ,
) ;

I can't figure out the syntax.  This is MS SQL server 2012.


texts:
I need to delete rows from a table based on what is present in a temporary table.

For me, both of these statements work :

DELETE from main_table where `id` in (select `deletable_id` from temporary_table);

and

DELETE main_table from main_table join temporary_table on main_table.id = temporary_table.deletable_id;

Which of the two is advisable to use given the fact that main_table will be having a billion of rows and the temporary will have a few thousands.


texts: Move/Copy unique rows from TableA to TableB, if they are not in TableB

texts:
I'm required to move every unique row from TableA to TableB, if the unique row from TableA is not already in TableB.

Both tables look the same, they don't have any keys, but there's a combination of columns that make their rows unique for my task - num which is an int and com, a varbinary(512).

As my SQL is horrible I wanted to create a script that does the job, but I have ridiculous problems, escaping/handling the varbinary value. This is why I'm asking for help.

I can pseudo-code..

moving/copying::

SELECT * INTO TableB
WHERE (unique TableA.num + TableA.com)
AND NOT IN (TableB)
FROM TableA

afterwards, for moving only

DELETE FROM TableA WHERE (unique from above)

(Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2)

Additional details

Sorry. I'm still learning SQL AND asking proper questions..

TableA & TableB have the same design. They have multiple columns, including the two above mentioned ones. I'm required to both move and copy (depending on the chosen option). TableA is the source, TableB is the destination. Both tables may contain numerous occurrences of the same int/com combination. I need to have only one (it doesn't matter which one) of each of the combinations. With copying they're not removed from TableA, when moving, they should be.


texts: What is the difference between postgres=# and postgres-#? I tried googling it and looking on the official site but could not get a hit for the symbols.


texts: SQL Server 2012: Give rights to read ddl but not modify?

texts:
What rights would I need to be give a user for them to be able to view all DDL code (such as stored procedure code) but not be able to edit any of it?

The only possibility seems to be DDLadmin, but that would allow a user to edit as well.  Is there some other options, or perhaps a way to grant and then revoke part of DDLAdmin?


texts: How to efficiently handle querying for different combinations of optional parameters

texts:
Consider the following Tables:

Table: Parts
    part_id INT PK
    part_number VARCHAR

Table: Jobs
    job_id INT PK
    job_number VARCHAR

Table: Job_Parts
    part_id INT FK PK
    job_id INT FK PK
    quantity INT

Running the following statement:

SELECT
    p.part_number AS [Part],
    j.job_number AS [Job],
    jp.quantity AS [Quantity]
FROM
    dbo.parts AS p
    LEFT OUTER JOIN
        dbo.job_parts AS jp
        ON jp.part_id = p.part_id
    Left OUTER JOIN
        dbo.jobs AS j
        ON j.job_id = jp.job_id

Will give a table that can give results like the following:

Part        |Job        |Quantity
Part1234     Job1        10
Part1234     Job2        5
Part1234     Job3        7

Now let's pretend I have that above statement inside of a Function:

CREATE FUNCTION JobParts(
    @PartNum VARCHAR = NULL,
    @JobNum VARCHAR = NULL
) RETURNS TABLE AS (
    --SELECT STATEMENT
)

Is there a way for me to modify the table join in the select statement such that:

If @PartNum or @JobNum have a value, it joins where part_number or job_number equal the provided value
If @PartNum or @JobNum are NULL, it will only return the last record put in for a part.

To give an example, if I enter @JobNum='Job2' I want the result I get to be:

Part        |Job        |Quantity
Part1234     Job2        5

Otherwise, if @JobNum is NULL is defaults to just showing the last entry in the table, so my result would be:

Part        |Job        |Quantity
Part1234     Job3        7


texts: Equivalent of UTF8_UNICODE_CI collation in PostgreSQL

texts:
I would like a column in a table inside a PostgreSQL database (I am using version 9.6). I know of the UTF8_UNICODE_CI collation on MySQL, so I tried:

CREATE TABLE thing (
    id    BIGINT PRIMARY KEY
   ,name  VARCHAR(120) NOT NULL COLLATE "UTF8_UNICODE_CI"
);

but I get:

ERROR: collation "UTF8_UNICODE_CI" for encoding "UTF8" does not exist

Reading around, I found that the pg_collation table displays collations, which showed:

=# SELECT * from pg_collation;
 collname | collnamespace | collowner | collencoding | collcollate | collctype
----------+---------------+-----------+--------------+-------------+-----------
 default  |            11 |        10 |           -1 |             |
 C        |            11 |        10 |           -1 | C           | C
 POSIX    |            11 |        10 |           -1 | POSIX       | POSIX
(3 rows)

So it's clearly not there... Is there any way to add it, or obtain the behavior I want? (I want a search WHERE name LIKE '%lala%' to match 'lalá', 'LÂLÄ', etc.)


texts: SQL Server Difference between CPU and CPU_TIME

texts:
In MS SQL Server 2014, what is the difference between sys.dm_exec_requests.cpu_time and master.dbo.sysprocesses.cpu? I see that sometimes the two values are the same and other times the value are different. When should the values be different?

The following query retrieves both values.

SELECT
      req.cpu_time
    , sp.cpu
FROM sys.dm_exec_requests req
JOIN master.dbo.sysprocesses sp ON req.session_id = sp.spid

Here are examples from my server.


texts: Analyse and report disk usage growth historically

texts:
Is it possible to use historic information to show disk usage growth?

I haven't keep much in the way of database setup and disk usage information. So I can't say how much disk growth has occurred in the last 6/12/18 months.

Can I reconstruct that picture from info stored somewhere in SQL Server?


texts:
I'm trying to set the alias for command permanently in PostgreSQL.
I've set the alias for a command like given below.

Test=> \set x '\\! reset'

So, the alias has set for reset the terminal.
While using command, we have to use it like this,

Test=> :x

So it will clear[reset] the terminal.
But this alias is set for temporarily only. If I logged out from PostgreSQL and again logging in means, This alias is not working.
So I want to know,

  Is there any way to set the alias for command permanently in
  PostgreSQL?

Thanks in advance...!


texts: Fail to connect from MySQL Workbench to remote server

texts:
After trying many different suggestions as found on the net, I'm still unable to establish a connection from the Workbench application to the server. Here is a brief list of things I tried:

1) Granting all privileges on . to 'root'@'%' ; (+flush privileges),

2) Creating a new user 'test'@'%' and granting all privileges (yes, it is the same as above, but had to try with someone different from root),

3) Create a user as 'test2'@'' with all privileges,

4) Turning off firewall in both server and my computer,

5) Updated the file /etc/my.cfg my adding a bind-address = 0.0.0.0 line (there wasn't any such line, to begin with; note the location of this file which differs from what is presented in many posts; in my installation, there is no such a folder /etc/my.cfg/...),

...

The error I get is always that cannot establish a connection (with the 4 steps to check; obviously, I checked them all and it should work).

I have Workbench installed on Windows 10 and I'm using the standard MySQL included in the Oracle Linux 7 installation ISO.

Could anyone suggest additional checks to be made and to find what the issue is?


texts: postgres and passwordless authentication

texts:
We have users for out postgres database and none have passwords... Do not know the history behind it.

When I try to use clients to connect to these databases, for instance, pgadmin III, the connection always fails due to the lack of password.

How does one overcome this? I do not want to create username with passwords just to get around this. Sometimes that is not an option.

Thoughts?


texts: Slave (SQL thread) replicates very slowly

texts:
See this video: https://mckay.media/Yxjp2

I have two MySQL replication slaves which are replicating from the same master. Both slaves are running on identical hardware. Each has its data directory stored on an SSD, which has comparable throughput. There is no I/O lag as you can see, only SQL lag.

The terminal on the left is the new slave. The one on the right is the existing slave from which the one on the left was cloned. I cloned them by copying all configs (changing server-id), and rsyncing the MySQL data directory.

I can't for the life of me figure out why the new slave is replicating so slowly. The video was taken during an off-peak time. During peak time, the new slave is just unable to keep up at all.

I don't know 100% what was done when the old slave was set up. The individual who set it up is no longer available. The MySQL configs are 100% identical besides the server-id.

I'm running Debian 8 on both slaves. The old slave has MySQL v5.5.43-0+deb8u1-log. The new one has MySQL v5.5.47-0+deb8u1-log. There is nothing noteworthy in the slowlog for the new slave.

I did notice that the new slave has a much larger %iowait in iostat, but I'm unsure what to make of this, since both are running on SSDs. sda is an HDD, and sdb is the SSD where the MySQL data directory is stored (on both machines).

Old slave:

Linux 3.16.0-4-amd64 (mysql-cluster-5)  03/03/2016      _x86_64_        (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          34.11    0.00    0.93    0.22    0.00   64.74

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               3.85         0.25      1351.60    5234043 28302154992
sdb              73.54         2.41      1684.24   50383141 35267640148

New slave:

Linux 3.16.0-4-amd64 (mysql-cluster-6)  03/03/2016      _x86_64_        (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.53    0.00    0.23    8.75    0.00   89.49

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               2.49        23.51        25.60     186982     203664
sdb             145.48       581.01      5359.82    4621437   42632848

Here is the new slave's my.cnf, which is identical to the old one with the exception of server-id:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /ssd/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
max_connections        = 10000

innodb_buffer_pool_size = 20G
innodb_thread_concurrency=8
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=500
query_cache_type        = 1
query_cache_limit       = 64M
query_cache_size        = 512M
sort_buffer_size=74M
read_rnd_buffer_size=16M
tmp_table_size=1G
max_heap_table_size=1G

log_error = /var/log/mysql/error.log

slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log      = 1
long_query_time = 2
log_queries_not_using_indexes

server-id               = 11
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

There is nothing notable in /etc/mysql/conf.d/.

All of my tables are InnoDB.

Why is it replicating so slowly?


texts: Exadata RMAN vs. Non-Exadata RMAN - any differences?

texts:
Can you please let me know the differences, if any,  in the architecture of the non-Exadata RMAN vs. that of the Exadata-RMAN from an SBT plugin perspective?

Have you observed from your vast experiences when non-Exadata RMAN and Exadata RMAN existed as separate architectures?

Do you anticipate any behavioral change from a RMAN perspective going forward from now between the standard and Exadata infrastructures ?


texts: Does an index with multiple columns make a similar index redundant?

texts:
Let's say I have a table that looks like this:

CREATE TABLE Activity (
    ActivityID int primary key identity(1,1) ,
    ActivityName nvarchar(10),
    InactiveFlag bit
)

with an index that looks like this:

CREATE INDEX Activity_Index on Activity
(
    ActivityName
)

then for some reason, someone has created a second index:

CREATE INDEX Another_Activity_Index on Activity
(
    ActivityName, InactiveFlag
)

is it definitely safe to delete the first index? Is it just taking up unneccesary disk space? Will the second index cover all cases of the first? The column ordering is definitely "ActivityName" first.


texts: Postgresql update timestamp without timezone to have timezone

texts:
When I created the table, a Timestamp column without timezone was created. All the imported time is in UTC. Now I want to add the timezone information explicitly, and I have altered the column

ALTER TABLE review ALTER COLUMN review_time TYPE TIMESTAMP WITH TIME ZONE USING review_time AT TIME ZONE 'UTC';

Selecting the existing data does not show the added timezone information

review_time
2017-07-28 02:25:44
2017-07-28 03:10:35
2017-07-28 03:11:32
2017-07-28 03:11:35
2017-07-28 03:11:38
2017-07-28 03:11:41
2017-07-28 18:54:54

Do I need to run an UPDATE statement on the existing data, and if so, what is the syntax?

Update 1

The output that is missing timezone information is due to the application itself (SQLWorkbenchJ). Querying from psql will show the timezone

mydb # SELECT review_time FROM review;
      review_time
------------------------
 2017-08-20 08:00:02+08
 2017-07-27 00:45:33+08
 2017-07-27 00:45:37+08
 2017-07-28 02:24:03+08
 2017-07-28 02:24:27+08
 2017-07-28 02:24:31+08
 2017-07-28 02:25:31+08


texts: Postgresql 9.0 adding new database to streaming replication

texts: postgresql 9.0  streaming replication + hot standby is running. Now i need to add one more database to current cluster. What is the best way of doing this? I have a backup file about 20GB. should i restore to that cluster? can standby server have all the data?


texts: Simple mysql insert occasionally taking over 300 seconds

texts:
I have a super simple insert query, something like

insert INTO `table_a` (`choice`, `updated_at`, `created_at`) VALUES (?, ?, ?)

Which inserts one integer (1 to 10) and 2 time stamps. 99.9% of time its a normal sub 10 ms query, but occasionally it goes up to over 300 seconds.
The server at those times is not under heavy load.
We use AWS RDS instance for the DB and the logs there look fine.

Any ideas why this might happen?

The create table script:

CREATE TABLE multiple_choice_answers` (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  choice tinyint(4) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3875644
          DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The table has 3853275 rows.

We are using InnoDB and no DB snapshots or dumps were taken at that times. I was thinking it might be a deadlock, but it makes no sence since none of those 3 columns are used as foreign key for any other tables.


texts: Why do database users with no associated login have access to a database?

texts:
My understanding of SQL security is such that in order to have access to the server, you need a login. To access a database on that server, your login needs to be associated with a user in that database. Over time I have been consolidating security so that developers, QA, etc. are in various roles implemented as active directory groups. Those groups have logins on the server, so individual windows logins are not so prevalent on SQL instances. I grant proper accesses to the databases through these roles.

A common service I provide is to refresh non-production versions of databases from production. I'll do a backup and restore of a production database onto a development server, de-identify the data, rename it, etc.

I have noticed something recently in my environment that is troubling. I was granting access to a key table in production for a development team, when I noticed by using 'fn_my_permissions' that one of the developers has read and write access to the entire production database, rather than being restricted to read-only as I designed. I compared all of this developer's group memberships and found another group he was a member in that was assigned read/write - but only as a database user. There is no associated login  for that group on that SQL server.

It is typical here to copy a 'baseline' production database and repurpose it elsewhere - whether as another production database, or a non-production version. I am starting to find other examples of this anomaly. To sum it up, the problem I see is that when a database is copied to another server, the users in that database that were previously linked to logins on the original SQL instance seem to retain the same level of access on the new SQL instance - even if there is no related login for them on new SQL instance! This strange effect seems limited to users representing windows group logins.

Here is a summary of what I am seeing:
Server1 has a login tied to windows group A, which has been granted read/write to database X.
Database X is then backed up and restored onto Server2, with logins but none related to group A. The database still shows a user for group A, and when I run 'fn_my_permissions' for a user in that group he has read/write to the database.

This particular server is running SQL Enterprise 2012 SP3 with the latest CU. I've seen the same thing on servers running 2008 R2, as well as a current build of SQL 2016.

Why is this happening? And what can I do to ensure that users do not inadvertently circumvent the controls I have put in place? It seems obvious I need to remove all of the unnecessary users after the databases are copied, but why?


texts: Backup chain for DBs restored from a different server

texts: When a DB is restored from a different server (say, Test is refreshed with Prod), the info on the differential base is brought with the backup. So, the SQL instance will keep relying on the backup chain that exists in the original environment (Prod) until a new full backup is taken (in Test). Ola’s backup solution is not able to detect and resolve this condition, so it would keep taking diff backups (even with @ChangeBackupType = 'Y') until the next scheduled full backup. Is there any workaround on that?


texts: Special Considerations for SQL Server with 512 GB RAM / 64 Cores?

texts:
Apart from some Windows Hotfixes are there any special considerations (i.e. "things to worry about") when running SQL on a server with 512 GB RAM and 64 logical cores?

I'm aware of some guidance when operating above one or both of these thresholds but not right at them.

OS: Windows Server 2008 R2
SQL Server 2008 R2 SP3 (clustered)


texts: Any way to send notifications to different Emails on success/failure of SqlServerAgent job?

texts:
SQL Server 2012 R2 on Windows Server 2008R2

Is there any way to send notifications to different Emails:

on success of an Sql Server Agent job to one Email address and on failure - to another ?
on success (or failure) of a particular job step (but not the whole job)?


texts: Did a SQL Server instance ever participate in replication?

texts:
How can I tell whether a SQL Server instance ever had replication in use or not? I'm debugging an issue with a 3rd party software. This software (Red Gate SQL Backup) reports the distribution database to be missing. The company has asked to find out whether this server has ever used replication or not. How can I find out?

Replication does not seem to be installed and the distribution database is not present. None of the databases use replication.


texts:
Is Minimum Canonical cover supposed to be singular?

For example when I calculate Fc for:

AC->BD
B->ACD

I get different results if I remove AC->D before B->D and vice versa, so can there be many covers?


texts: Restoring postgres backup with WAL-E fills up disk

texts: I am running Postgres and WAL-E in a Docker container built from a custom image based on postgres:9.4. WAL-E works great and I have restored from backups a number of times. However, the backups take a while to run and they consume a TON of disk space. The space seems to be consumed by segments and log files, and they all get cleared out and the space is restored as soon as the restore completes. But is there any way to do the backup in batches, so that my disk doesn't fill up during restore?


texts: CHROOT install after I already installed Mysql 5.6

texts:
I installed some time ago Mysql 5.6 on Ubuntu server 12.04 and now I'd like to enhance security by putting mysql into a CHROOT environment on that same Ubuntu server.

Is this possible without re-installing the whole mysql?  mysql files are under var/lib and system files are under OPT/mysql/server-5.6

Anyone did this before?
Thanks


texts: Database schema for ecommerce with multiple attributes and multiple variants

texts:
firstly I wanted an opinion about the database that you will find in the attachment.
I am building a customizable ecommerce, and searching the web for a while I have come to this conclusion.
Only problem I can't come to a conclusion regarding attribute values.
Let's imagine we have a t-shirt product (products table), this will have two attributes attached to it, size (attribute table), color (attribute table).
In turn, the size will have linked sizes S (options table), M (options table), L (options table), and the color will have red (options table), green (options table), blue (options table).
Now let's say you have 10 pieces of size S, 3 of which are red, 3 blue, 4 green. and so on.
The database is correct if you want to set the names, but is wrong for the values.
How can I go about solving this problem?
If you have better solutions, welcome.
Thanks


texts: How to reclaim disk space on PostgreSQL?

texts:
I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued delete from command to delete all records from it (I should have used truncate, but I didn't know that). So I did full vacuum on my db to reclaim disk space, but it just doesn't help. My problem looks identical to this one, but there is no solution provided. I have already checked this thread and documentation on "recovering disk space", but still can't find a solution. I use this code to get size of all tables

 SELECT nspname || '.' || relname AS "relation",
 pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
 AND C.relkind <> 'i'
 AND nspname !~ '^pg_toast'
 ORDER BY pg_total_relation_size(C.oid) DESC
 LIMIT 15;

Totalling to less than 1GB, however

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database

still shows about 20 GB. Any advice much appreciated.


texts:
looking for ideas for diagnosing the root cause of the following issue. We have 3 availability nodes Primary,Synchronous replica, Asynchronous replica. no load other than T-Log backups exists on the Synchronous replica. At a certain time all replicas start lagging behind. The lag increases over a period of 6 hours. Data is still being written but very slowly. Suddenly the replicas start catching up and are back up to date within a period of around 30 minutes.
No errors in any of the SQL Server Error logs.
No blocking of the DB STARTUP process or any of the redo processes on either of the replicas (one has no users on it)
HADR_DATABASE_FLOW_CONTROL has moved into the top waits for the Primary
On Secondary top waits are normally
PARALLEL_REDO_WORKER_WAIT
REDO_THREAD_PENDING_WORK
PARALLEL_REDO_TRAN_TURN

During the issue this stayed much the same other than
PARALLEL_REDO_FLOW_CONTROL existed.

No evidence of a long running large transaction on the primary. Given that all replicas were affected it points to a problem with the primary. No index maintenance type jobs running. Can anyone think of any ddl,dml operations or other task that could cause this type of behavior?
thanks

Environment is all SQL2016 CU9, disks are local on all machines


texts: I am reading to all tutorials/books that when running SHOW ENGINE INNODB STATUS the Per second averages calculated from the last X sec, the X should be 20 to 30 seconds. I have never manage to exceed 10 sec, so my question is: Is the output valid? i can count on these statistics? Is there something i am missing here some parameter to collect status statistics more efficient?


texts: Will SSDT-BI work with SQL Server 2008 R2?

texts:
Sorry if this is redundant, but due to the crazy naming of the tools, it's hard to find the answer to the question.

Question 1

Will SSIS packages, reports and so forth built with Microsoft SQL Server Data Tools - Business Intelligence (SSDT-BI) for Visual Studio 2013 work on SQL Server 2008 R2?

Question 2

I'm currently using SQL Server Business Intelligence Development Studio (BIDS) for Microsoft Visual Studio 2008. I want to potentially upgrade to Data Tools - Business Intelligence for Visual Studio 2013.

I assume I would need to A) purchase a new copy of Visual Studio 2013 and then B) download the free SSDT-BI software? That's assuming SSDT-BI for VS2013 works for 2008 R2.


texts:
Not sure how to go about this:-

I have a query

select name, city, salary from Employee

I can get a sum by doing a grouping

select name, city, sum(salary) total_salary from Employee group by name, city

How can I get the total per city as a separate row under each city ?

Expected Sample Result:

Name City Salary
n1   c1   10
n2   c1   20
T1   c1   30
n3   c2   20
n4   c2   50
T2   c2   70


texts: Postgres 13 - Grant to Active Directory Groups?

texts:
Is it possible to grant an AD group in PG 13 without sorting to creating the group in the database?
Similar to how AD groups can be granted directly in SQL Server.
The PG server is on a Windows server that is already joined to the AD domain.
Note: I asking about authorization  and not authentication against AD.


texts:
I want to insert last insert query to the another column.For that i wish to use TRIGGERS

--
-- Triggers `sync_log`
--
DROP TRIGGER IF EXISTS `tg_sync_id_insertbgnn`;
DELIMITER //
CREATE TRIGGER `tg_sync_id_insertbgnn` BEFORE INSERT ON `student_master`
FOR EACH ROW BEGIN
INSERT INTO `sync_log`(`Sync_query`) SELECT * FROM student_master WHERE `SM_NO` = NEW.`SM_NO`;
END
DELIMITER ;

This doesn't seems to work.Any idea why ?


texts: Best practices for storing multiple SQL tables per user (built from uploaded csv file?)

texts:
I have a few users who will be uploading a few types of predefined CSV files. I need to store these as sql tables to be able to run queries on this data for a particular user, however that means there's potentially going to be loads of tables.

Right now I'm storing the csv's in an s3 bucket with the key username/filename/timestamp-filename.csv

What would be the best way to transform it into SQL? I have CSV parsing done using nodes fast-csv, it's just the schema that's baffling.

I was thinking table names like data-username-filename so each user will have as many tables as csv categories, so 10 tables each. Would it be best to store this in a seperate DB or the same DB just differntiating by table name/prefix?

This is a backoffice app with only a few users.


texts:
I am working on a query from our ticketing system, and I am having problems figuring out the best way to join these tables together.

I have the following tables/fields:

SR_service: stores ticket data
  SR_Service_RecID
  SR_Status_RecID
  Last_Update
  Date_Req
company: defines a company
  Company_RecID
  Company_Name
SR_type: stores ticket type
  SR_Type_RecID
  Description
SR_Status: stores ticket status
  SR_Status_RecID
  Description
Schedule: the tickets assigned users, and date/time for work*
  RecID (This is the ticketID)
  Member_ID
  Date_Time_Start
  Date_Time_End

*The SCHEDULE table also has a Schedule_Type_RecID, and we are pulling for type '4'. If we join this table with SR_Service, only schedules of type '4' are allowed to be related to tickets in the system.

The first sub-query is for the type of ticket I am looking for. The type of ticket is dependent on what 'board' the ticket belongs to. Leaving out @Board will give me the type of ticket I'm looking for, across all boards.

The second sub-query is making sure I only get tickets that are in NOT closed status. Again, the closed SR_Status_RecID is dependent on the board. I return the closed status of all boards here.

I am at a loss how to get the ticket, company info, schedule info, and ticket status all in one query. The following is the list of what I need: Ticket_ID, status, assigned resources, company name, last update, and required date.

So far, I have come up with:

SELECT s.SR_Service_RecID, s.SR_Status_RecID, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID
                        FROM SR_Type t INNER JOIN SR_Board b
                        ON t.SR_Board_RecID = b.SR_Board_RecID
                        WHERE t.description LIKE @Type
                           AND b.Board_Name LIKE @Board)
AND s.SR_Status_RecID IN (SELECT SR_Status_RecID
                          FROM SR_Status
                          WHERE Closed_Flag = 0)

This will get me a good amount of data, but there are a few problems:

I only get the status_ID, not the actual description of the status.
I am not quite sure how to add the Schedule table into the query to determine when a person is schedule out to perform the ticket tasks.


texts: Why does connecting to a database use tempspace?

texts:
Earlier today we had a problem logging into a database that had just been created automatically.

When on the actual box (Linux) sqlplus schema/pw, i.e. a local connection, was fine, though, it was occasionally impossible to do anything once connected. Otherwise, whether using a session on the box or not sqlplus schema/pw@db did not work at all. tnsping and DNS testing revealed nothing and there was no problem in listener.ora.

The Listener was accepting the connection but not handing it off to the database. This was confirmed by using tail -f on the listener log and watching it accept the connection. There is a logon trigger on the database that inserts the user details / time etc into a table, but this doesn't have any indexes and there cannot have been any locks.

We finally traced the issue. There was a conflict in the file that creates the temporary tablespaces for the database. It resulted in there only being 5MB of tempspace instead of the normal 25GB. Disabling the logon trigger and then adding the additional temporary tablespaces, sorted everything out. There were some sessions already running that would have been using some of this tablespace.

Apparently Oracle requires some temporary tablespace in order to accept an incoming connection. The specific version used on this DB was 9i, though I think this applies to all.

Why and what is it used for?


texts: YYYY-MM-DD has changed to YYYY-DD-MM after moving SQL Server to new domain how do I switch back?

texts:
I have moved the domain of my SQL Server and while everything is mainly working the date format for queries seems to have changed.

So if I run this:

select convert(datetime,'2012-02-13')

I get

  Msg 242, Level 16, State 3, Line 1
  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

When I run

select @@Language, @@Langid

I get

  British | 23

I have tried to set sp_defaultlanguage but this seems to make no difference.

I have also checked the regional settings in control panel and that seems to also be set to British.

Even this code sample causes a problem:

set language british

select convert(datetime, '2012-02-13')

With the result

  Changed language setting to British.
  Msg 242, Level 16, State 3, Line 3 The conversion of a varchar data type to a datetime data type
  resulted in an out-of-range value.


texts: Are there any guidelines for the "IBM Premier Level Business Partner"?

texts:
Our company currently subcontracts a DB2 project to a company which have "IBM Premier Level Business Partner". I was just wondering, are there any guideline about what they should provide (say, SQAP, source code....) as an IBM partner?

I ask this because we are not too familiar with database development and the db contract terms. Therefore, we may want to make sure of what we should have after the contract is finished.

Thanks~

Sorry, it may not be a suitable place for not programming related question, please suggest me a suitable place if I'm posted wrong.


texts: Oracle Databases - need to export nightly for backup/DR purposes

texts:
I know nothing about Oracle databases, so if my question's path seems odd, feel free to tell me the right way to do things.

I have 2 ways I see currently that I can backup a server running 3 Oracle databases for a 3rd party app we have.

I can use Symantec BE 2010 R3's oracle agent.  Which I downloaded and configured but it tells me that in order to backup the databases "when it is in NOARCHIVELOG mode, the database must be MOUNTED but not OPEN".  So I guess the question here would be how to change the databases to ARCHIVELOG mode so that they can be open when I back them up or how can I pass a command to CLOSE the database prior to backing them up and then OPEN it back up after the backup completes?
I can use the "exp" command.  The problem here is that I'm not sure what parameters/options to pass for each DB.  The 3rd party has steps to take, but they include choosing "(2) tables" and some other choices that don't seem to be default choices in the EXP command help.  For instance here's a screenshot of their exp "choices":

Any assistance with either one of these methods or if there's another/better method, please let me know.

Thanks!


texts: What is the best way to create a database diagram for SQL Azure without having to install SQL Server?

texts: I have a database on SQL Azure and want to create a database diagram for that, but don't want to have to install SQL server and copy the database etc.  Is there any way that I can do it that anyone knows of?


texts: Why add indexes to these columns in the Database?

texts:
I am trying to get my head around this code. It's from the Rails Tutorial Book and is part of the process of making a twitter like application.

   class CreateRelationships < ActiveRecord::Migration
  def change
    create_table :relationships do |t|
      t.integer :follower_id
      t.integer :followed_id

      t.timestamps
    end

    add_index :relationships, :follower_id
    add_index :relationships, :followed_id
    add_index :relationships, [:follower_id, :followed_id], unique: true
  end
end

Since their are only 2 columns (follower_id and followed_id) why would their be a need for an index?
Does the index sort them is some way? It just seems a bit strange to me to add an index to a table with 2 columns?
What does the index do to the rows?


texts: MariaDB mysqldump lacks all data - how do I force including it?

texts:
for years I've been using mysqldump without much trouble.  Recently I joined a project that is using "MySQL dump 10.14  Distrib 5.5.52-MariaDB, for Linux (x86_64)" and when I attempt to do a dump it includes all of the create table statements, and the routines if I include --routines, but it doesn't contain any of the data.  There are no INSERT statements in the file at all.

I've tried on multiple databases within this DBMS instance, and I've tried using several different user accounts, including root.  I've checked that the users I'm using have access to the data, and if I log directly into mysql from those usernames I can browse the data.

I've been unable to find any help information on the web because all posts seem to assume that the insert data will just be there (which I think it right).  Has anyone seen something like this before or is there a suggestion of what I might try to get a real database backup?

I recognize this is a very old MariaDB version, I'm new to the team and I'm willing to update it, but not until after I know the data is successfully backed up somewhere.

Thanks in advance for your help, this is really confusing me.

Here's what I'm trying:

/usr/bin/mysqldump -u root -p --all-databases  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql

/usr/bin/mysqldump -u root -p -d databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql


texts:
I am trying to send a csv file with 15000 records via the database mail in SQL Server 2014. The problem is that when I open my email the csv only contains 209 records. I have tried the same thing in SQL Server 2012 and it works as expected - it sends the 15000 records in the csv.
I have tested this on several servers with 2014 edition on them, and I have the same issue on all of them. Does anyone have an idea if something changed in the 2014 edition? Or can give me some tips on what to look at next?

The query breaks off at different points - for example one of them breaks off at 209 records as i said above, another one at 307. the last record gets truncated. the attachment size it's about 64 kb - which is well below the 4MB limit i've configured the database Maximum File Size bytes parameter. what i am doing basically is creating a job that is meant to execute a stored procedure and send the results in a csv in an email. The stored procedure is something like:

    select col1, col2, col3
    from table A1
    where col1 > 1000
    order by col1


texts: How do I efficiently partition a large MySQL (Aurora) table with multiple search columns?

texts:
I have a large and rapidly growing table.  It takes in about 5k rows / second and that will be doubling in about a month.  The table is Amazon's Aurora (heavily modified InnoDB.)

As of today, total aggregate rows is around 4B.

Data is mostly searched on in 6 ways:

count(distinct col1,col2) where timestamp range (1 day range)
count(distinct col1,col2) where timestamp range (30 day range)
count(1) where country IN (list,of,countries) and timestamp range (30 day range)
count(1) where foreign_key = int and timestamp range (1 day range)
count(1) where foreign_key = int and timestamp range (30 day range)
select * where timestamp range (1 day range)

The foreign_key, timestamp and country columns are all indexed.

Data is only really "active" for a 90 day periods with most select's happening between -42 and -12 days.

How do I effectively partition this able to speed up those 6 queries?

And, almost as important, how do I accomplish this with minimal downtime?
(assumption here is to create a new table, move all inserts to the new table & copy old data in... like I would do with any new index)


texts: Allow only find and insert action to mongodb collection

texts:
I want to create collection. that allow only insert and find operation. i read Privilege Actions in mongodb and i applied that method but all actions are performed like remove and update

i tried following

db.createRole({
  role: "master",
  privileges: [
    { resource: { db : "db_name",collection:"lock" }, actions: ["find","insert"] },
  ],
  roles : []
})

 db.grantPrivilegesToRole("master",[{resource : {db : "db_name",collection:"lock"},actions : ["find","insert"]}],{w : "majority"})

how to allow only insert and find ?


texts:
Database version is 12.1.0.1.
We have one primary database and two physical standby database and dataguard also configured.

Dataguard sync is fine working between primary and physical standby.(No lag)

While checking the status of standby database by using DGMGRL getting below error

  DGM-17016: failed to retrieve status for database "xxx_x"
  (xxxx_x - standbydbname)
  ORA-16706: Data Guard broker worker process not available


texts:
I am developing a web service where a term of search is given, then I return all the matches from employees table. In MySQL I used to do the following:

SELECT * FROM employees WHERE MATCH(name, lastname, nickname) AGAINST('billy jops');

The query finds records that match "billy jops", "billy" or "jops" in name, lastname or description columns of employees tables.

Now I need to emulate this searching pattern in Oracle or at least a little bit closer.

MATCHES SQL Query

I have read about Querying with Oracle Text, and looks like MATCHES SQL Query is what I need. However, looks like the search index is applied just to an unique column:

create table queries (
      query_id      number,
      query_string  varchar2(80)
    );

    insert into queries values (1, 'oracle');
    insert into queries values (2, 'larry or ellison');
    insert into queries values (3, 'oracle and text');
    insert into queries values (4, 'market share');

    create index queryx on queries(query_string)
      indextype is ctxsys.ctxrule;

    select query_id from queries
     where matches(query_string,
                   'Oracle announced that its market share in databases
                    increased over the last year.')>0

  This query will return queries 1 (the word oracle appears in the document) and 4 (the phrase market share appears in the document), but not 2 (neither the word larry nor the word ellison appears, and not 3 (there is no text in the document, so it does not match the query).

Could you please let me know how to adjust this query to be compatible with more than one column?


texts: How to set a flag in SQL, when there is a change in value

texts:
I have column which tells fee rates.
When there is change in value in that column, I need to set a flag for that corresponding row.
How can i check whether there is change in value from previous row and current row and flag it accordingly. There are l0's of thousands of records.

Please find below, when rate changes, then that row I have to flag.


texts:
I own a raspberry pi 1 running raspbian jessie (SYS_A). Two weeks ago I performed an update of the system (aptitude update && aptitude upgrade, not aptitude dist-upgrade, since I know it exists raspbian stretch) and an undesired situation cropped up.

I'm running a MySQL database, I guess version 5.5. After the upgrade, systemd didn't start the mysqld daemon, whose logs are the following:

user@rpi:~ $ sudo systemctl start mysql
Job for mysql.service failed. See 'systemctl status mysql.service' and     'journalctl -xn' for details.

user@rpi:~ $ systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since mar 2017-10-10 10:13:11 CEST; 55s ago
Process: 7815 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

user@rpi:~ $ sudo systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since mar 2017-10-10 10:13:11 CEST; 2min 46s ago
Process: 7815 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)
oct 10 10:13:10 rpi /etc/init.d/mysql[8535]: 0 processes alive and     '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
oct 10 10:13:11 rpi mysql[7815]: Starting MySQL database server: mysqld . . .     . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
oct 10 10:13:11 rpi systemd[1]: mysql.service: control process exited,     code=exited status=1
oct 10 10:13:11 rpi systemd[1]: Failed to start LSB: Start and stop the mysql     database server daemon.
oct 10 10:13:11 rpi systemd[1]: Unit mysql.service entered failed state.

Fortunately, I haven't removed the MySQL's files within /var/lib/mysql, so it seems all the data remain in there.

I've tried to open up the ibdataX files, but seem to be binaries...

What I've also tried is to set up another raspbian jessie (SYS_B) in another raspberry pi, install mysql, stop the service, backup the SYS_B's original /var/lib/mysql folder, replace it with the SYS_A's /var/lib/mysql folder and start again the mysqld daemon. But this resulted into the same startup error as in SYS_A.

I've got a wordpress database which I don't want to lose it. So, is there any way to restore it?

/var/logs/mysql/error.log

171010 10:12:36 [Warning] Using unique option prefix myisam-recover instead     of myisam-recover-options is deprecated and will be removed in a future release.     Please use the full name instead.
171010 10:12:36 [Note] Plugin 'FEDERATED' is disabled.
171010 10:12:36 InnoDB: The InnoDB memory heap is disabled
171010 10:12:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins
171010 10:12:36 InnoDB: Compressed tables use zlib 1.2.8
171010 10:12:36 InnoDB: Using Linux native AIO
171010 10:12:36 InnoDB: Initializing buffer pool, size = 128.0M
171010 10:12:37 InnoDB: Completed initialization of buffer pool
171010 10:12:37 InnoDB: highest supported file format is Barracuda.
171010 10:12:38  InnoDB: Waiting for the background threads to start
171010 10:12:39 InnoDB: 5.5.57 started; log sequence number 28689772
171010 10:12:39 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
171010 10:12:39 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
171010 10:12:39 [Note] Server socket created on IP: '0.0.0.0'.
171010 10:12:39 [ERROR] Fatal error: Can't open and lock privilege tables:     Incorrect file format 'user'

Summing up: I can't start mysqld, so I can't backup my databases, which I don't want to lose them, especially the related to wordpress. I haven't deleted the data placed on /var/lib/mysql, ibd files and so on. Could I restore mysql databases into another installation of mysql?


texts: Read Only Access Except Stored Procedures in Specific Schema

texts:
I'm building an application that is attached to a legacy application database. The application has it's own database that is on the same server as the legacy database.

My application is ASP.NET and I'm using Entity Framework.  I've set up a domain user that is a DB owner for my application database and has read-only access to the legacy database.  I don't want to accidentally make any updates to the legacy database through some bug in the application.

I would like to create stored procedures in my new database that can modify data in the legacy database.  I figured putting them in their own schema might be a logical start, at least for the sake of organization.  I understand schemas are good for organizing security access.  Is there a good approach I can take to make this work?


texts:
I've read Performance difference between MySQL and PostgreSQL for the same schema/queries.. Is it still relevant with appearance of Solid State Drive?

That article said that Postgres is better for complex queries and subquering, and worse for simple queries by ids and sequential scans.

Postgres is worse/slower for some queries due each table is a heap, means hasn't clustered index, means rows are not physically sorted on hard drive by their primary key. So if you want to read a significant portion of records (by id as I understand) from Postgres - that will lead to many random I/O and poorer performance if compare with MySQL.

Questions:

But how about SSD? Have sequential reads got same performance for Postgres and MySQL?
Also does it make any sense to use SSD instead of HHD with RDBMs?

Update: related to Rick James' answer

MySQL use Plan A and Postgres use Plan B, right?
Plan A means clustered index, right? The table is physically sorted by lastName + firstName.
Plan A on HDD could perform better that Plan B on SSD, so Postgres + SSD is not an silver bullet. You need to choose MySQL for quering clustered index, right?
Pls more details about which queries do you mean by I/O bound? In my understanding any call to database == I/O bound operation.


texts: I have to make quite a few changes to my tables.  I have numerous views all with schema binding on.  Is there a way to turn schema binding off for all views then turn it back on again when I'm done (other than doing it individually)?  Any help or direction would be appreciated.


texts: How can I get a list of column names and types from a resultset?

texts:
Say I get a resultset back from the following query:

select *
from sys.database_files;

For any given resultset, I want to be able to query the column names and types so I can then create tables to store the results.

What is a good way of performing this in T-SQL?


texts: Enterprise manager failed to start in oracle 10g

texts:
I am using oracle 10g with 4 instance with same server but one of them has failed to start Oracle Enterprise Manager and rest of three are working fine.

Starting Oracle Enterprise Manager 10g Database Control
....................................................................
failed.
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/vvs/sysman/log

Here I posted error log file -> emagent.log

2018-07-31 11:10:55 Thread-4124031232 Starting Agent 10.1.0.6.0 from /u01/app/oracle/product/10.2.0/db_1 (00701)
2018-07-31 11:10:55 Thread-4124031232 Starting Agent 10.1.0.6.0 from /u01/app/oracle/product/10.2.0/db_1 (00701)
2018-07-31 11:18:41 Thread-4124375296 Starting Agent 10.1.0.6.0 from /u01/app/oracle/product/10.2.0/db_1 (00701)
2018-07-31 11:35:28 Thread-4124748032 Starting Agent 10.1.0.6.0 from /u01/app/oracle/product/10.2.0/db_1 (00701)
2018-07-31 12:03:41 Thread-4124952832 Starting Agent 10.1.0.6.0 from /u01/app/oracle/product/10.2.0/db_1 (00701)
2018-07-31 14:36:27 Thread-4125055232 Starting Agent 10.1.0.6.0 from /u01/app/oracle/product/10.2.0/db_1 (00701)
2018-07-31 15:23:13 Thread-4124195072 Starting Agent 10.1.0.6.0 from /u01/app/oracle/product/10.2.0/db_1 (00701)
2018-07-31 18:19:37 Thread-4124940544 Starting Agent 10.1.0.6.0 from /u01/app/oracle/product/10.2.0/db_1 (00701)

and when i am going to start agent is says agent is already running but while checking status it shows the agent is not running

[oracle@vvs ~]$ emctl start agent
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Agent is already running
[oracle@vvs ~]$ emctl status agent
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent is Not Running

And when I'm going to start OEM of this instance it failed but the rest of OEM instance stops working

So please let me know how to solve this Problem

edited data

2018-08-16 16:47:50 Thread-4090489664 ERROR http: 9: Unable to initialize ssl connection with server, aborting connection attempt

2018-08-16 16:47:50 Thread-4090489664 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://vvs:5502/em/upload/: retStatus=-1

2018-08-16 16:47:50 Thread-4090489664 ERROR pingManager: Error in updating the agent time stamp file
2018-08-16 16:47:53 Thread-4087339840 ERROR command: nmejcn: error receiving response headers from http://vvs:5502/em/upload/


texts: Getting Values of the two rows before the selected row POSTRESQL

texts:
I want to update an row according to the values of two rows before the row how can i do that?

Let's assume my table looks like this

ID         amount

1           1
2           3
7           5

And i want to do this

UPDATE amount
FROM table
SET amount = (AmounToFRowOneBefore + AmounToFRowTwoBefore)/2
WHERE ID=7;

Expected output would be

 ID         amount

    1           1
    2           3
    7           2

And if I do this I don't know anything about the two rows before so no ID or anything is there a query to select the values before the selected row?


texts: Oracle 12c PDB Cloning with RMAN - Aux DB connection issues

texts:
I'm trying to run the following to duplicate a PDB using RMAN.

rman target  testuser/testuser@PDB01 auxiliary testuser/testuser@PDB02 as sysbackup

I can connect to both the target and aux PDBs remotely using sqlplus without any problems. I can connect to the target using RMAN also. But the auxiliary connection is a problem. Does anybody know why RMAN prevents a remote connection to an auxiliary database in this situation?

rman target testuser/testuser@PDB01 auxiliary testuser/testuser@PDB02

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 19 15:34:51 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB1 (DBID=1985676993)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06814: cannot connect to pluggable database PDB02
If I run the commands individually I get the following ....

RMAN> connect target testuser/testuser@PDB01
connected to target database: TESTDB1 (DBID=1985676993)

RMAN> connect auxiliary testuser/testuser@PDB02

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06814: cannot connect to pluggable database PDB02

Just to prove the aux PDB02 has no issues I can use it as a the RMAN target and it works. It's just the auxiliary command that's the problem:

RMAN> connect target testuser/testuser@PDB02
connected to target database: TESTDB2 (DBID=958109427)

Any suggestions as to the possible cause of this?


texts: Will the job schedule obey Daylight Saving rules?

texts: We have an Oracle 11g Scheduler Job that was created with a start date of 08/AUG/15 05:00:00.100000000 PM AUSTRALIA/SYDNEY and a Repeat_INTERVAL of FREQ=DAILY. Sydney, as a city, observes Daylight Saving. I'm wondering if this job schedule will honour the change in Daylight Saving, or is the AUSTRALIA/SYDNEY component meaningless?


texts:
I want to see all fsid limit 25 from my table -- I tried this query

Select *
from btmt
WHERE Count(fsid) > 1
limit 24

But this throws an error of

  Aggregates are not allowed in WHERE clause.

What do I need to alter in order to run this query?


texts: Migrated data with Special Characters Counted as 2 Characters in DB2 Linux

texts:
We are currently migrating data from DB2 10.1 AIX to DB2 11.1 Linux. Since we are using some features like versioning/history tables and rowchangetimestamps, db2move is not an option for some tables. As a result we do manual export and load for some tables.

The problem arrises for data having special character such as "Ñ".

  Sample Data

  PIÑATA

The AIX DB2 10.1 Counts the length for this as 6 characters and it fits fine to VARCHAR(6) columns. But when I load this to DB2 Linux 11.1, this is counted as 7 characters, thus data is truncated.

I tried CASTING this as VARCHAR(6) but the data is still truncated. I hope someone can point me to a possible solution.


texts: Database service likely hang at some moments

texts:
Our team adopted MariaDB to replace SQL Server 2008 last year end. There is no issue for DB service hanging, and last week and yesterday I met 2 services hang issues. The following are configurations and operations.

Configurations

The MariaDB version 10.1.18 has been installed on MicroSoft Cluster
Services (MSCS). (Windows Server 2012 R2)
There are 2 VMs for The MSCS with active-standby mode.
The shared storage (Data Files) is on NetApp Storage, using raw device mapping (RDM).
There is a Slave DB VM for DB replication. (read-only)

Issues

When the service hang, I logged into the DB VM, and I could see the DB service (mysqld) was active, and the port was bound by mysqld service.
From localhost of DB VM or console I could not telnet or used tool to connect DB service.
There was no error message in Mariadb errorlog or windows events.
In order to recover services quickly, I rebooted the DB VM to force the cluster hand-over to another DB VM.
In the recent DB VM or standby DB VM, there was no errorlog or error events in windows event. (there is only mysqld normally shutdown messages)

Operations

Before the first hang, I copy the DB backup file to Slave DB, because the replication was far from current, I wanted to restore the database and made replication again. In copy progress after 10 mins, the services start to hang.
Before the second hang, I started replication and show slave status said Queuing logs and then Waiting logs, and after few mins the services hang again.

How to find true evidence

I think the problem could be on file system or storage, but in db logs or windows event I can not find evidences to prove it. Could somebody help me to more advance method to find out root cause?

Update

This question is issued in MDEV-13025 and wait for next time to gather data.


texts: Create/Alter stored procedure without compiling

texts:
Is it possible to create or alter a stored procedure in SQL Server 2005* that doesn't compile the procedure?

(* - yes, unfortunately, we still need to support 2005 as our minimum standard)

I have a situation where it would be exceedingly useful to be able to create/alter a stored procedure that is syntactically valid but not semantically valid (due to incorrect table/column names)... which I can then alter accordingly within SSMS.

I've just had a thought, and it's a pretty simple solution... comment out the entire contents of the stored procedure when it's being altered/created.

Using Ctrl+K Ctrl+C will comment out each line individually - which would be better than using /* */ around the entire thing, as that will fail if there are any instances of /* */ in the stored procedure already.

I would still be very interested to hear if there is an actual way to tell SSMS to ignore invalid table/view/column names.

Thanks to @marcelo for his answer, and it does appear that using the name of a non-existent table will allow you to create the stored procedure.

However, it does not allow you to create a stored procedure if you use a non-existent column in an existing table


texts: Multiple create table statements in one query only creates first table

texts:
I've read you should create multiple queries for each creation but I'm super confused about why.

I have a bash script which I cannot include because my terminal lacks copy/paste functionality at this point :/ But this should give you a clear picture:

read -d '' query << EOF
BEGIN;
CREATE DATABASE IF NOT EXISTS users;
USE users;

CREATE TABLE IF NOT EXISTS a ( ... );
DELETE * FROM a;
INSERT INTO a ( ... ) VALUES ( ... );

CREATE TABLE IF NOT EXISTS b ( ... );
DELETE * FROM b;
INSERT INTO b ( ... ) VALUES ( ... );

COMMIT;
EOF

echo $query | mysql -u root -proot

The result is that the database users is created, a table a is created and the INSERT INTO query is executed because table a contains these records.
The problem is that table b is not created at all.

I've got two questions about this:

Why, if every query would need to be in a separate queries, are the DELETE AND INSERT INTO queries committed?
Why does it stop at the second CREATE TABLE query and not after the CREATE DATABASE statement as I would expect?

Maybe because CREATE statements are executed within their own transactions as by design? But then again, DELETE * FROM a; and INSERT INTO ...; are two committed statements...

It's annoying because in case you want to use BEGIN; COMMIT;, you would need to include this in every query along with USE users.
Any suggestions?


texts: What is difference between is_unique column and is_unique_constraint columns in sys.indexes

texts:
We have two columns is_unique and is_unique_constraint for unique constraints in sys.indexes and I want to clarify few concepts based on these two columns

Create statement for both types of indexes are same except fill factor property value or do I missing something?
If statement 1 is true then why we have two columns in sys.indexes for unique constraint?
When is_unique=1 is_unique_constraint =0 and then drop statement for index is
DROP INDEX [index name] ON [dbo].[TableName] WITH ( ONLINE = OFF )

And when is_unique=1 is_unique_constraint =1 and then drop statement for index is
ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [IndexName]

Why is that so?
Where we use unique index and where unique constraint, what is best practice?


texts:
Is there any way that I could use trigger instead of check? like I have

  CREATE
      /*!50017 DEFINER = 'root'@'localhost' */
      TRIGGER test BEFORE UPDATE ON apply
      FOR EACH ROW BEGIN      IF NEW.cname = "hi" THEN
              SET NEW.cname = "hello";
          ELSE
              RETURN 1;
          END IF;
      END; $$

but return 1 doesn't work. Is there anyway that I can prevent the query from executing?


texts: During table migration of tables from one tablespace to another, does it needs free space to do the alter table move?

texts:
During migration of tables from one tablespace to another (all datafiles in the same disk/partition/mount), does it needs free space to do the alter table move?

exemple:. I have 30gb of free space on that disk, table of 1TB in tablespace_1, i wanna move the table using alter table move to tablespace_2.. can i do it or do i need to have more space available? if so, how much free space available?

Thx a lot :)


texts: Probable circular dependency, but query still runs

texts:
I built this ER diagram in MySQL Workbench and it will run and generate the database.  My concern is that the relationship between the employees, departments, positions, and employment_statuses is a circular relationship.

Because I know people will ask, this is something I was working on in a class.  I'm not asking for help with homework; this is something we were working on in class and I took a different route than the professor.  I'm asking for clarification about database modelling theory.


texts: How to check user defined schemata creation date in SQL server

texts:
I want to find Schema creation date and user who created schema in SQL server,
I am unable to find information in  system views.

Note: Not asking about schema scoped objects


texts: Unable to deploy SQL Server Big Data cluster in Kubernetes. Readiness probe fails on a pod

texts: While deploying SQL Server 2019 Big Data cluster trial version, as part of deployment steps, we observe that deployment is not progressing because one pod is failing with an error    "Readiness probe failed: cat: /var/run/container.ready: No such file or directory"


texts: Snapshot Folder Location for Replication with Clustered Instance

texts:
I am assigned to create a transactional replication with a SQL Server clustered instance for reporting purposes. I'm planning to use the Distributor and the Subscriber on the same box.

Regarding the snapshot folder, is it ok to place it on the same box where the Distributor\Subscriber resides? Will there by any problems?


texts: Best field type for cryptocurrency big numbers?

texts:
I'm using PostgreSQL for my cryptocurrency exchange database, the question is: for saving currency amount (numbers) with their precision (like 323232323232323.45454545 ~ 23 digit + 1 dot: 15 digit before dot and 8 digit after that), Should I use varchar(24) type for them or double precision or numeric(15,8) ?
Edit: numeric(23,8) is right for having 15 digit before dot and 8 digit after that!
Note: It seems that double precision type cant properly save big numbers like example above and it will be rounded to 323232323232323!
Witch one has better performance (speed) and needs less resources?


texts: What does: SET @Variable1 = (Select field1 From INSERTED) mean?

texts:
I'm trying to understand an existing Trigger on one of my tables (DocumentDistribution), that it seems is the bridge table between DocumentInfo and DocumentSource tables. I don’t understand how this line (SET....FROM INSERTED) gets the value from? Is this what it called Dynamic SQL and it gets the value from the front end App for this database based on the user’s selections?

I’m getting errors when I try to test, as the sub-query is returning multiple rows while the syntax is expecting a single row, any advice?
I'm using SQL Server 2008R2.

Alter TRIGGER [dbo].[DocDist_Dup_Check]
ON [dbo].[DocumentDistribution]
AFTER UPDATE, INSERT

AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
DECLARE @docid int,
        @sourceid int,
        @errstr varchar(255);

SET @errstr = 'The distribution you have attempted to create already exists in the database.' + CHAR(13) +
              'Duplicate distributions are not allowed for any document source except eBinder.';
SET @docid = (SELECT DocumentDistDocID FROM INSERTED);
SET @sourceid = (SELECT DocumentDistSourceID FROM INSERTED);

IF  (SELECT COUNT(*) FROM DocumentDistribution WHERE DocumentDistDocID = @docid AND DocumentDistSourceID = @sourceid) > 1

    AND
    (@sourceid NOT IN(SELECT DocumentSourceID FROM DocumentSource WHERE DocumentSourceName = 'eBinder' OR DocumentSourceName = 'eBinder - West Division' OR DocumentSourceName = 'Consolidated e-Binder' OR DocumentSourceName = 'MA Consolidated eBinder'))
BEGIN
    ROLLBACK TRANSACTION
END

ELSE
BEGIN
    RETURN
END

END


texts: How to pinpoint root cause of excessive log file growth

texts:
The log file stays pretty small (~500MB) until about 11PM, and at that point it begins growing until no disk space is available. The log file reached it's maximum size at 12:39AM, so the growth took place over approximately an hour and a half time frame. The drive that houses the logs only has about 40GB of total space available, so the log file can only grow to that 40GB. That should be plenty though as the data is fairly static and only about 50GB total. Here are a few more additional notes:

DB recovery model is full
Transaction log backups are taken every 15 minutes
A defrag job is called at 11PM but looking at the history it only runs for a few minutes max

How can I pinpoint the exact cause of this? It has to be a large transaction that is open but how can I determine that?


texts:
I have a users index which has fields like id, email, password, etc. A user can see all other users except the ones who had blocked him. I want to design a database schema for this problem.

So I'm using MongoDB and I have these two tables.

Users

id   email   password
01   a@b.co  123
02   b@c.co  XYZ

now, I need a way to store the block relations information. If x has blocked y then y can't see x on the user's list.

If I add an array in user y which contains all user id who has blocked them so would it be a good solution? or is there any other alternative?

Users

id   email   password   blockedBy
01   a@b.co  123        []
02   b@c.co  XYZ        [01, 07]

now, I'll show all the users to user 02 except 01 and 07.


texts: Auditing sysadmin logons with Extended Events

texts:
The goal is to audit sysadmin logons as efficiently and safely as possible without auditing logons of other users, so as not to be logging excessive data.

I have found the information on auditing sysadmin logons by using a logon trigger, and in general that would accommodate our needs.  However it does have the drawback of being synchronous, which means that users can't log on if the trigger fails for some reason, and it will slow down their logon a tiny bit.

With the logon trigger, I can use IF IS_SRVROLEMEMBER ('sysadmin') = 1 to dynamically determine if the user is a sysadmin, but something like this does not appear to be possible with audit objects.  It appears that audit filters have to be a simple server_principal_name = 'domain\username' (i.e., you can't use any SELECTs or functions to determine if the user is a sysadmin).

So the only thing left that might even be possible, as far as I've found, is using Extended Events, so I wanted to ask the community if this is something I should bother looking into.  The event would probably have to capture all logons (again, as the filters can't run code), and then we'd have to have something that consumes the event to filter and log the relevant activity.


texts: I only want one maximum value but it returns all the rows

texts:
I need help with this query. I'm trying to get a value for a customer that spends the most amount every month in 2018 but it doesn't work:

PROMPT 'Customer that has Spent the Most on Specific Month'
    ACCEPT MonthInput Varchar PROMPT 'Month Value:'

SELECT  E.cust_name,
        E.cust_ID,
        MAX(P.total_amount)
FROM    customer E,
        room_booking RB,
        payment P,
        booking_details B
WHERE   P.booking_id    = RB.booking_id
AND     RB.cust_id      = E.cust_id
AND     B.booking_id    = RB.booking_id
AND     B.check_in_date LIKE'%-&MonthInput-2018%'
GROUP BY E.cust_name, E.cust_ID

My tables :

Customers (
    cust_name,
    cust_ID
    )

room_booking (
    booking_ID,
    cust_ID*
    )

booking_details (
    booking_ID*
    )

payment (
    booking_ID*
    )


texts: How to set-up a distributed data storage system for The Real Junkfood Project

texts:
Context

The Real Junkfood Project consists of a network of pay as you feel (PAYF) cafes. We need to log the data from each of ~100 cafes, generating perhaps 50 rows each par day. I estimate that this could (if we expand) generate around 1 million rows per year. How best to store this data in a system that allows computer noobs to log their data in a way that is as easy and user-friendly as possible? That's the essence of my question. Details below.

Introduction to data storage

I know that the appropriate way to store data is described by the term 'tidy data'
[@Wickham2014]. The rules of data stored in this way are:

Each variable forms a column.
Each observation forms a row.
Each type of observational unit forms a table.

TRJFP data

The data collected by the TRJFP operates at several levels, so several
tables are needed. These are

Food items intercepted
Daily totals (e.g. meals served, finances)
Interception points
Cafes

The first 2 tables are usually collected daily by each cafe. The 3rd table
on 'interception points' (where food is picked-up from)
is also collected by each cafe but is only updated
when a new interception point is logged. The 5th table provides a global overview
of all the cafes and is updated every time a new cafe is added.

By far the largest table in the database will be 1,, which could store perhaps 50
new items per day. Assuming (optimistically) that in the future cafes are dilligent in
logging the data, we could log data from 100 cafes. That would be 5,000
rows per day or around 1.8 million rows of data per year. This is not really
Big Data, but it is worth thinking carefully about how it is stored.

The variables associated with each table are described below.
Critical to the useability of the data is that the datasets can be
linked. Therefore linking variables connecting each table are described.

Options

There are 3 main options for storing these tables, from simple to complex.

On Google Fusion Tables
As regularly backed-up .csv files managed through an online server system like Shiny
On a fully fledged database system, like postgres


texts: How to avoid losing information via UPDATEs due to database design issues

texts:
Business requirements
I have a database where information about documents and their related packages are kept. Each document can have multiple packages, and each package has its details and product list.
When documents become certified they cannot be further edited, but users are allowed to edit package details before attaching it to another document, but in certified documents old data should be kept.
Considerations
So my issue is that, when users update package details to create a new document, in front-end views package details get updated even in certified documents because I have only one row for package details in my database.
I am evaluating the possibility of keeping multiple rows for each update, so as to prevent losing the data retained before the updates in already-certified documents, but it will cause too much work. I want to know how to effectively design the relevant database in order to avoid this kind of issues.
My database diagram so far
This is my current design diagram.


texts:
Reading around on the topic hasn't got me closer to understanding best practices. I'm also relatively new and self-taught in programming, so might have some questions about how to actually execute the solution.

The app I'm building requires a lot of DOM elements to be generated based on database rows. These should be updatable by the user and at the minute I'm using the auto incrementing primary key as an html attribute id.

Obviously I don't want users to modify this, or in the associated javascript object, to then affect other users' data when saved or updated. I have considered the following options:

Not using auto incrementing but a uuid or guid, but I've read these are not all that advisable, especially for db performance.
Concealing the client side id by hashing it when served on the server side. This also doesn't sound particularly performant.
Having a 'lookup table' that is referenced and produces another, non-incremental id. Doesn't seem like a bad idea but I'm a bit unsure how best to go about generating this and how it should look.
Including an extra column in the tables that would be updated that contains for example the users name and would be combined with the primary key. This seems simple enough but the idea of adding this extra column to every table that might be updated sounds redundant and not normalized, etc.
Keeping the primary keys visible but producing a session table for the user that tracks what the which IDs were sent out and therefore which can be updated. New rows can be generated so long as other ones belonging to other users aren't updated.

Or, maybe I'm barking up the wrong tress here :) Thanks!


texts: How to execute system command in mysql stored procedure?

texts:
Here is the code,

delimiter //
create procedure trig ()
begin
    declare cmd varchar(50);
    set cmd = "sh /home/yogaraj/Documents/Maastry/firefox.sh";
    system cmd;
end //

delimiter ;

But I can't execute system command. Can somebody help me?


texts: "Syntax error or access violation" error with nested square brackets in column alias

texts:
When trying to create sp_BlitzIndex I got the error "Syntax error or access violation"

I narrowed this down to column alias names like:

SELECT
       ...,
       index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
       ...

Removing the nested square brackets solved this, but the "]]" quoting looks fine to me :(

I checked my version-control repository and the previous version also had the nested Square Brackets, so presumably it ran OK for me previously.

Not sure what has changed here that prevents this running now, but the sp_BlitzIndex scripts starts with a long list of SET options (they appear unchanged from previous version) so doesn't seem like it would be any of them

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Here's a trivial use-case which also fails in Query Analyser

SELECT 'xxx' AS [xxx xxx]] {xxx} xxx]
SELECT 'xxx' AS []]{}]

The double close square brackets AND the open and close curly-brackets are ALL required, if any single element of those is left out then QA works OK. As does changing the order - e.g. this works OK:

SELECT 'xxx' AS [{}]]]

My guess is that it's a bug in Query Analyser. The frustration is that the "Syntax error or access violation" error gives NO line number, so it is hard to find the cause (I located it by trying to execute the first half of the code block and then doing a binary-chop to narrow down the location of the culprit)


texts: What is the best way to find out the sum of a field from a table?

texts:
I am making a accounting software. I have a table debit_credit that contains double entry accounting records
Here is my example
data.Table is:
debit_credit_id | tran_id | ledger_head_id | debit | credit | dr_cr | commission | commission_type
--------------------------------------------------------------------------------------------------
1               | 2       |  5             | 500   | 0      | Dr    | null           | null

1               | 2       |  5             | 0     | 500    | Cr    | null           | null

This tables contains many rows and I have to run the following query several times:
'SELECT SUM(debit) - SUM(credit) FROM debit_credit WHERE ledger_head_id=$id'

But my project manager proposed, to make a  default view to making the sum. After that, when I need the sum just query from that view as
'SELECT debit - credit FROM debit_credit_view WHERE ledger_head_id=$id';

But I would prefer to use first method with stored procedure instead of the method proposed by my project manager.
Sql fiddle: http://sqlfiddle.com/#!9/cc594/1
What is the best way?


texts: Weird tables with numbers and underscores in PostgreSQL execution plans

texts:
I'm debugging performance problems with my DB and see some unusual entries in the DB log:
2021-04-21 01:07:33.857 UTC [14823] user@db LOG:  duration: 933645.469 ms  plan:
Query Text: select systemv0_.id as id1_11_0_, systemv0_.createdTs as createdT2_11_0_, systemv0_.systemType as syste3_11_0_, systemv0_.foundProblemsCount as foundPro4_11_0_, systemv0_.groupid as groupid8_11_0_,... (rest omitted for brevity)

What are these strange identifiers with numbers and underscores? Normally an execution plans has clear names and identifiers such as systemview.id.
My hypothesis is that there are several concurrent queries and the performance issues are due to the DB desperately trying to remain consistent under low memory conditions. I noticed a couple suspicious things in other queries - several Recheck Cond and Materialize tree nodes, which appear to be often happening due to insufficient size of some DB buffers (caused by low host memory?). It would be nice if someone could confirm or debunk my theory.


texts: How to concatenate a column after x rows?

texts:
I have a table with columns

object varchar(255),
frequency tinyint(3) unsigned,

I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as

.. query loop {
$object .= $row['object'].",";
$frequency = $frequency+$row['frequency'];

if($frequency > 20) { // 20 is a given value
    $mysqli->query("INSERT INTO table2 (concatenated_column) VALUES ('$object')");
    $frequency=0;
    $object='';
    }
}

Is it possible to do this with a native query of mysql instead of bringing the data into PHP and returning back to mysql?

Since the process is simple, I think mysql has enough functions to do so, but I do not know how to do so.

In fact, I want to group concatenate the entire column and split it when sum of frequency reaches the given value (i.e. 20). This needs a loop of INSERTs in which the sum of frequency reset to 0 upon reaching 20.

UPDATE: The process with do as:

table1:

object    frequency
object1   7
object2   5
object3   6
object4   8
object5   2

table2:

concatenated_column
object1,object2,object3,object4
object5,....


texts: Is worth change databases collation from Latin1_General to Modern spanish, migrating to a new server

texts:
We are migrating 3 databases tables from Sql Server 2000 to a new sqlserver 2014.

I know that for new databases the recommended Collation for spanish is Modern_Spanish. But what is the recommendation for migrating database to a new server? Create the new server with the same collation of the 3 Databases (Latin1_General)? Or migrate the current dabases to Modern_Spanish.

The current sql server 2000 where the 3 databases are running the default collati is Latin1_General as the 3 database.

Is it worth to create the new server with Modern Spanish collation, and migrate databases collation to Modern Spanish? Or it's better to continue with Latin1_General ?

Thanks


texts: How join two databases data, thats contains auto increment ids, into a new database

texts:
My company is evolving a database schema from several databases, one for each customer, to an unique one. We was facing a lot of problems using separated databases. them we decided to start using a centralized data base.

My concern is about the auto incremented ids from the databases.

for example, we have products: 1, 2 and 3 in one database and other products: 1,2 and 3 in other database (with same ids). Those ids are also used as references for another tables.

How can i join those databases in just one and does not mess with the already inserted Ids?


texts: MariaDB Galera cluster: claster stalls with lots of “Query End”

texts:
I have cluster consists of 2 physical nodes (2x Intel Xeon E5-2670 2.6Ghz , 192Gb, RAID1 SSD, ОС Debian 8, mariadb-server-10.2) + arbitrator (on Proxmox). I write to only one node at time. My cluster works fine from 1 hour to several days and stalls. It doesn't show any errors in syslog or mysql-error.log (sometime but not always shows something like)

  Aug 3 10:43:32 ru-tul-dc01-db-galera-node1 mysqld[1122]: 2017-08-03
  10:43:32 139707425306368 [Warning] Aborted connection 2905 to db:
  'basename' user: 'username' host: 'ip' (Got an error reading
  communication packets)

  Aug 3 10:43:32 ru-tul-dc01-db-galera-node1 mysqld[1122]: 2017-08-03
  10:43:32 139707424700160 [Warning] Aborted connection 2919 to db:
  'basename' user: 'username' host' host: 'ip' (Got an error reading
  communication packets) or lots of

  Aug 3 14:35:08 ru-tul-dc01-db-galera-node1 mysqld[1115]: 2017-08-03
  14:35:08 140310822119168 [Warning] WSREP: certification interval for
  trx source: dcdccd42-7837-17837-11e7-a045-2a82ddbaac7a version: 3
  local: 1 state: CERTIFYING flags: 1 conn_id: 1010 trx_id: 10244940
  seqnos (l: 152638, g: 5113465, s: 5088151, d: -1, ts: 3445:
  3445393337346) exceeds the limit of 16384) PROCESSLIST shows some
  queries in "Query End". It cannot be killed (it just changes status
  from Query End to Killed. mysql -u root -e "SHOW STATUS LIKE 'wsrep_%'
  " shows that nodes are Synced, wsrep_local_recv_queue = 0 and so on.

The only way back it to work is to restart node.

Please help me to realise what's the problem and how to fix it.


texts: I couldn't change root user password on mysql with --skip-grant-tables

texts:
I am very new at mysql and have struggled to change root user password in mysql.
(I have never set root password but it asks me root password.)

The below is what I have done.

One thing I suspect is that flush privileges results 0 rows affected.

Could you give me a suggestion?

$ sudo service mysql stop
$ sudo mysqld_safe --skip-grant-tables &
[1] 5395
sangmin@ubuntu:~$ 170201 00:32:55 mysqld_safe Logging to syslog.
170201 00:32:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

$ ps -e | grep mysql
  5396 pts/6    00:00:00 mysqld_safe
  5545 pts/6    00:00:00 mysqld

$ mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.29-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> update user set password=PASSWORD("root") where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> quit
Bye

$ sudo service mysql restart
[1]+  Done                    sudo mysqld_safe --skip-grant-tables
$ mysql -uroot -proot
ERROR 1698 (28000): Access denied for user 'root'@'localhost'


texts:
I´m testing around with MongoDB and NodeJS. I´m stuck with the problem that I can create max 4899 connections to MongoDB. I´ve set the poolSize in the NodeJS Mongo driver to 40000.
Also I´ve set the needed ulimits of the mongo server.

Does anyone have an idea why the mongo server refuses new connections as soon
as 4899 are reached? The problem is definitely on th DB server side and not on client side.

I´m using the newest versions of all components (nodeJS V8, Mongo, Node mongo native driver)

limits configuration:

/etc/sysctl.conf
net.ipv4.ip_local_port_range = 1024 65000

/etc/security/limits.conf
mongodb         hard    nofile          1048576
mongodb         soft    nofile          1048576
mongodb         soft    nproc           1048576
mongodb         hard    nproc           1048576
mongodb         hard    stack           16384
mongodb         soft    stack           16384
mongodb         hard    memlock         unlimited
mongodb         soft    memlock         unlimited

/lib/systemd/system/mongodb.service
# (file size)
LimitFSIZE=infinity
# (cpu time)
LimitCPU=infinity
# (virtual memory size)
LimitAS=infinity
# (open files)
LimitNOFILE=500000
# (processes/threads)
LimitNPROC=500000

mongo serverstats:

db.serverStatus().connections
{
        "current" : 4899,
        "available" : 395101,
        "totalCreated" : NumberLong(5279)
}


texts: WITH RECURSIVE with union among the recursive term

texts:
Is it possible to use WITH RECURSIVE queries in PostgreSQL where the recursive term is a union of different tables? My usecase is to extract some tuples from a table and some logical rules:

And I would like to exploit recursive queries having the following syntax:

WITH RECURSIVE cte_name(
    CTE_query_definition -- non-recursive term
    UNION
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

where p1 and p3 can be tuples from different possible types. This means that my non-recursive term will be:

SELECT * from TUPLES

While the recursive term generates new tuples from our data but does not have to generate tuples that already exist (CTE with UNION without ALL).

SELECT
       'ex' AS type_event,
       t1.arg2 AS arg1,
       t1.arg6 AS arg2,
       null AS arg3,
       null AS arg4,
       null AS arg5,
       null AS arg6
    FROM cte_name t1
    WHERE t1.type_event = 'Transfering'
      AND t1.arg2 = t2.arg1
      AND t1.arg6 = t2.arg2

UNION ALL

       'ex' AS type_event,
       t1.arg2 AS arg1,
       t1.arg6 AS arg2,
       null AS arg3,
       null AS arg4,
       null AS arg5,
       null AS arg6
    FROM cte_name t1
    WHERE t1.type_event = 'Replacement'
      AND t1.arg2 = t2.arg1
      AND t1.arg6 = t2.arg2
UNION ALL

       'ex' AS type_event,
       t1.arg2 AS arg1,
       t1.arg1 AS arg2,
       null AS arg3,
       null AS arg4,
       null AS arg5,
       null AS arg6
    FROM cte_name t1
    WHERE t1.type_event = 'ex'

By the way, WITH RECURSIVE does not allow to express recursive statements where the recursive element is a union of different selects. Is there an efficient solution that does not involve to programmatically implement this in C++/Java/Python, so that all the data "stays" in the database and I do not need to serialize and desirialize the data multiple times?

Example of input data for TUPLES:

+-------------+------+------+------+------+------+------+
| type_event  | arg1 | arg2 | arg3 | arg4 | arg5 | arg6 |
+-------------+------+------+------+------+------+------+
| ex          | A    | B    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| Transfering | A    | B    | C    | D    | E    | F    |
+-------------+------+------+------+------+------+------+
| Replacement | G    | H    | I    | J    | K    | L    |
+-------------+------+------+------+------+------+------+

Expected output after the third and final iteration:

+-------------+------+------+------+------+------+------+
| type_event  | arg1 | arg2 | arg3 | arg4 | arg5 | arg6 |
+-------------+------+------+------+------+------+------+
| ex          | A    | B    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| Transfering | A    | B    | C    | D    | E    | F    |
+-------------+------+------+------+------+------+------+
| Replacement | G    | H    | I    | J    | K    | L    |
+-------------+------+------+------+------+------+------+
| ex          | B    | F    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | H    | L    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | B    | A    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | F    | B    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | L    | H    | null | null | null | null |
+-------------+------+------+------+------+------+------+


texts: Migration of create statement from SQL Server to MySQL

texts:
I have a table that have the following table structure

CREATE TABLE [dbo].[ASAT](
  [ID] [bigint] IDENTITY(1,1) NOT NULL,
  [ASATID]  AS ('ASAT'+right('00000000000000000000'
  +CONVERT([varchar](19),[ID],0),(20))) PERSISTED NOT NULL,
  [Created] [nvarchar](8) NOT NULL DEFAULT (CONVERT([varchar],dateadd(year,
  (0),getdate()),(112)))
) ON [PRIMARY]

When I used the MySQL Migration Wizard to convert the above Microsoft database table structure, I got errors that both datatype for my fields ASATID and Created was not supported (in MySQL).

What are the recommended ways to proceed such data type differences when performing a database migration from SQL Server to MySQL?


texts: Create constraint for stopping mass update to a table

texts: I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.