texts: Slower subquery performance on older MySQL

texts:
Same query

SELECT z.userid, z.usermeno, z.userpriezvisko, z.userfirma, z.useriban
       (SELECT cis_faktury FROM hlavicka_faktury
        WHERE userid = z.userid ORDER BY cis DESC LIMIT 1) as cis_faktury
FROM zmluvy z;

But a lot slower executed on mysql  Ver 14.14 Distrib 5.5.44 (32 seconds)

The faster runs under mysql  Ver 15.1 Distrib 10.1.24-MariaDB (0.3 seconds)

CREATE TABLE `hlavicka_faktury` (
  `cis` int(11) NOT NULL AUTO_INCREMENT,
  `cis_fakturacii` int(2) NOT NULL,
  `cis_faktury` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `datum_vyst` int(11) NOT NULL,
  `datum_spl` int(11) NOT NULL,
  `datum_dod` int(11) NOT NULL,
  `zaklad` double(14,4) NOT NULL,
  `dph` double(12,4) NOT NULL,
  `zaokruhlenie` double(10,4) NOT NULL,
  `celkom` double(14,4) NOT NULL,
  `sposob_uhrady` int(2) NOT NULL,
  `uhradene` tinyint(1) NOT NULL,
  `stornovane` int(10) NOT NULL,
  `stornofa` tinyint(1) NOT NULL,
  `opakovanie` int(11) NOT NULL,
  `odoslane` tinyint(1) NOT NULL,
  `fo` tinyint(1) NOT NULL,
  `mena` varchar(3) COLLATE utf8_bin NOT NULL DEFAULT 'EUR',
  PRIMARY KEY (`cis`),
  KEY `cis_fakturacii` (`cis_fakturacii`),
  KEY `cis_faktury` (`cis_faktury`),
  KEY `userid` (`userid`),
  KEY `datum_spl` (`datum_spl`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `zmluvy` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `userpass` varchar(15) COLLATE utf8_bin NOT NULL,
  `useraktiv` int(1) NOT NULL,
  `usermeno` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `userpriezvisko` varchar(50) COLLATE utf8_bin NOT NULL,
  `useremail1` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `userfirma` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `userico` varchar(11) COLLATE utf8_bin DEFAULT NULL,
  `userdic` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `usericdph` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `userbanka` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `usercu` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `useriban` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `old_userid` int(11) NOT NULL,
  `user_platdisciplina` double(10,2) NOT NULL,
  `user_efaktura` enum('0','1') COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Any suggestion how to improve without MySQL upgrade?


texts:
SYSTEM:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

PROBLEM:
Today I encountered an "orphaned transaction" (SPID -2) with a Unit of work ID of 00000000-0000-0000-0000-000000000000

I tried executing kill '00000000-0000-0000-0000-000000000000', but got a message that this could not be done. (Sorry, I failed to capture the exact message)

There were two transactions open in the MS DTC, but they were not set to in-doubt for a status, so I could not kill them from there.

At length, I resolved this by restarting the MS DTC.

This is a development system, so absolutely anything might have happened to cause this.

QUESTIONS:
I was just wondering if anyone had seen it, and might have any insights into:

A) What might cause it
   B) If there is a more elegant way to resolve


texts: Merge replication issue : conflicts with new data

texts:
I set up merge replication with SQL Server 2008, I am having issues when creating new data on clients and trying to synchronize with the remote server.

Pulling data from the server works fine. But when adding new data from the clients and synchronizing, I get conflicts for the publication updates with error in conflict manager:

  Upload inserts failed.

  This failure can be caused by a constraint violation. Invalid object name 'dbo.MSmerge_repl_view_CCEBB5FB95354A40940D433C94AAAF3E_FF89BB83CC734065B74175EFC3B6E922'.

I found that dbo.MSmerge_repl_view_... is a system view but I am guessing it is from the client since I cannot find it on the server.

I don't get why this is happening. Is the data being uploaded to the view (which doesn't exist on the server) ? if someone can explain why this might happen it would be great !


texts:
I have a query like this:

SELECT "btr_orders".*
FROM "btr_orders"
WHERE "btr_orders"."user_id" = 40000000
  AND (
    premium_until > now()
    OR intermediate_premium_until > now()
  )
  AND (
    active_since IS NULL
    OR active_since <= now()
  )
  AND "btr_orders"."temporary" = false
ORDER BY "btr_orders"."id" DESC
LIMIT 1;

this query takes around one two seconds to run, and we have at least 10 to 20 clients running this query over and over

the output of the EXPLAIN ANALYZE is this:

Limit  (cost=6192.97..6192.98 rows=1 width=244) (actual time=1086.744..1086.744 rows=0 loops=1)
  ->  Sort  (cost=6192.97..6192.98 rows=1 width=244) (actual time=1086.743..1086.743 rows=0 loops=1)
        Sort Key: id
        Sort Method: quicksort  Memory: 25kB
        ->  Seq Scan on btr_orders  (cost=0.00..6192.96 rows=1 width=244) (actual time=1086.736..1086.736 rows=0 loops=1)
              Filter: ((NOT temporary) AND ((active_since IS NULL) OR (active_since <= '2017-06-13 14:59:29.528395'::timestamp without time zone)) AND (user_id = 40000000) AND ((premium_until > '2017-06-13 14:59:29.527964'::timestamp without time zone) OR (intermediate_premium_until > '2017-06-13 14:59:29.527964'::timestamp without time zone)))
              Rows Removed by Filter: 154734
Total runtime: 1086.789 ms

although the rows filtered is actually 1, this query is really slow, probably because we are doing seq. scan. I tried to create an index for this query:

 create index CONCURRENTLY btr_orders_search_index
   on btr_orders (temporary, user_id, active_since, premium_until, intermediate_premium_until)
   where temporary = false;

but of course didn't worked. I have a very limited knowledge on postgres, actually in database administration (I'm a backend developer) and we do not have a DBA.
My question is: how can I create an index that fits this query?

Edit

\d btr_orders

zenguard_api=# \d btr_orders
                                             Table "public.btr_orders"
           Column           |            Type             |                        Modifiers
----------------------------+-----------------------------+---------------------------------------------------------
 id                         | integer                     | not null default nextval('btr_orders_id_seq'::regclass)
 active_since               | timestamp without time zone |
 affiliate_cookies          | hstore                      |
 affiliate_net_amount       | integer                     |
 affiliate_partner_id       | integer                     |
 closed_since               | timestamp without time zone |
 country_code               | character varying(255)      |
 created_at                 | timestamp without time zone |
 discount_id                | integer                     |
 first_charge_at_activation | boolean                     | default false
 intermediate_premium_until | timestamp without time zone |
 ip_country_code            | character varying(255)      |
 merchant_account           | character varying(255)      | default 'ZenGuard'::character varying
 order_reference            | character varying(255)      |
 paid_premium_until         | timestamp without time zone |
 premium_since              | timestamp without time zone |
 premium_until              | timestamp without time zone |
 price_id                   | integer                     |
 product_id                 | integer                     |
 purchase_platform          | character varying(255)      |
 temporary                  | boolean                     | default false
 updated_at                 | timestamp without time zone |
 user_id                    | integer                     |
 migrated_from_old          | boolean                     | default false
Indexes:
    "btr_orders_pkey" PRIMARY KEY, btree (id)


texts: Postgres : Relation does not exist error

texts:
I used pg_restore to load my postgres db with a dump file.
I connected to my db with my user :

sudo -u arajguru psql dump

select current_user;
 current_user
--------------
 arajguru

Now I was able to see all the newly created tables:

dump=> \dt
               List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | Approvals         | table | arajguru
 public | Approvers         | table | arajguru
 public | Conditions        | table | arajguru
 public | Entities          | table | arajguru
 public | EntityDefinitions | table | arajguru
 public | Projects          | table | arajguru
 public | Rules             | table | arajguru
 public | run_history       | table | arajguru
(8 rows)

But when I try to fire a select * query, it gave me this error:

dump=> select * from Approvals;
ERROR:  relation "approvals" does not exist
LINE 1: select * from Approvals;

What can be the reason for this error? Please help.


texts: Is it OK to use SQL Server availability groups on VMware installations?

texts: Can I use SQL Server availability groups on VMware installations or should I use a hardware installation for the Windows Server Cluster?


texts: Import daily csv with varying available headers

texts:
I receive a csv daily from an external company capturing feedback for us.
Unhelpfully the csv headings vary. There could be any combination of headers.
For example:
day1: heading1, heading2, heading3, heading4, heading5
day2: heading1, heading2, heading4, heading6
day3: heading1, heading2, heading7, heading8
day4: heading1, heading2, heading3, heading4, heading5
We have 18 months (no one has done anything with this data until now) of backlog files and so far I have found 22 different combinations...
I know what all the potential headings will be so I can have an SQL table that contains all the appropriate columns but I am struggling to handle the variance for import.
Is there a way to handle this in SSIS?
I have looked at bcp and openrowset but I can't seem to make it work.
As this file is received daily I want an sql job that imports them to my table for reporting. I can only create an SSIS package that will import a fixed flat file but I need it to be dynamic. Worst case scenario would be to have 22+ different SSIS packages and run a specific 1 for the given headings available but then I would need a way of automatically reading the structure of the csv to decide which package to use.
I'm lost, so does anyone have any direction?
SQL 2016 (13.0.5622.0)
Many thanks


texts: Does ActualLogicalReads info in query execution plan reports reads generated by scalar udf?

texts:
Sql Server 2016 and 2014 SP2 introduced new performance information on query execution plan nodes. MSDN: https://blogs.msdn.microsoft.com/sql_server_team/added-per-operator-level-performance-stats-for-query-processing/

One of introduced performance statistics is ActualLogicalReads. This is information which was available earlier with SET STATISTICS IO option. Unfortunately SET STATISTICS IO doesn't take into account reads generated by
user defined scalar functions (link).

I would like to know:

if SET STATISTICS IO and information included in query execution plan returns the same values?
if reads generated by user defined scalar functions are reported in logical reads presented in query execution plan?


texts: Can't install SQL SERVER 2017 on Centos 7.4 - mssql-conf setup error

texts:
I'm trying to install the lastest SQL Server 2017 on a Centos 7.4 VM (Vmware). I get the following error when I try running:

sudo /opt/mssql/bin/mssql-conf setup

using Bash shell.  The error:

  sqlservr: The configuration file '/var/opt/mssql/mssql.conf' failed to load (error: The INI file could not be opened. Errno [2] Filename [mssql.conf]).
  Initial setup of Microsoft SQL Server failed. Please consult the ERRORLOG
  in /var/opt/mssql/log for more information.

I've modified my path, checked permissions and even tried to create the mssql.conf file manually by using mssql-conf setup and mssql-conf validate.

I do not get a log file in /var/opt/mssql/log and I've even tried changing the log file location but I still don't get anything.  Has this error been reported before?


texts: What does standard SQL or any of the non-PostgreSQL SQLs do instead of RETURNING?

texts:
Common code for me:

if ($new_id = dbcall('INSERT INTO table (column) VALUES ($1) RETURNING id', 'meow'))

$new_id then contains the new id for the freshly inserted record.

According to https://www.postgresql.org/docs/current/sql-insert.html :

  INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension

That makes me feel bad about using RETURNING, and it also makes me wonder what standard SQL and non-PG SQL databases do to let the user "grab" the id in a safe manner.

I remember doing things like:

SELECT lastval();

directly after the INSERT, but that could easily turn into a "race condition" and thus give the wrong id! That's why I started doing RETURNING, after having it recommended to me by PG guys, but since it's nonstandard, I don't feel good about using it.

On the other hand, I have given up on ever writing "standard SQL" because such a thing doesn't seem to exist outside of the meeting room at some organization that charges money for this massive SQL standard which they somehow make money from even though nobody follows it.


texts: Online Hostel Management System ER Diagram

texts: I have created an ER diagram for an online hostel management system.  Please advise me if this diagram makes sense or needs improvement.


texts: When does setting the SORT_IN_TEMPDB option actually improve performance?

texts:
My question is related to index rebuilding, mainly the SORT_IN_TEMPDB  option.

BOL states that:

  Even if other users are using the database and accessing separate disk addresses, the overall pattern of reads and writes are more efficient when SORT_IN_TEMPDB is specified than when it is not.

On the other hand, one of the users states:

  When rebuilding an index you would need twice the space of the index + 20% for the sorting. So in general to rebuild every index in your db you only need 120% of your biggest index in your DB. If you use SORT_IN_TEMPDB, you only win 20%, you still need an aditional 100% in your data file. Further more, using sort in tempdb increases your IO load drastically, since instead of Writing the index one time to the datafile, you now write it one time to the tempdb and then write it to the data file. So that is not always ideal.

Would you like to share your own experience about this option? Have you ever had to use this option while rebuilding indexes? What was the performance result?


texts: SQL Server 2012 Licensing on a VMware Infrastructure

texts:
I am looking to license SQL Server 2012 Standard on my VMware Vsphere server farm. I currently have 3 hosts in the farm with the following spec:

2x 6core Xeon CPU (12 cores total)
128GB RAM

According to the documentation released by Microsoft I would need to license each core on the host. This documentation does not clearly explain if I need to license every core on every host on my server farm. It also appears I will need SA to allow me to vMotion the servers across the farm.

Can someone please clarify how many core licenses I will need to correctly license my farm? And confirm that I also need SA to use vMotion.

Thanks


texts: Cann't connect from another server to Oracle

texts:
I can connect locally like normal :

sqlplus sys as sysdba

I can also connect

sqlplus sys/pass@hostname:1521/servicename as sysdba

the service I get from when I do lsrnctl status but when I try to connect this from other system where oracle is running I get following error:

ORA-12543: TNS: Destination host unreachable

the command I used is

sqlplus sys/pass@hostname:1521/servicename as sysdba

which with the same I can connect locally but not from remove server. I try to check

telnet 192.1368.0.15 1521

given me below error

telnet: connnect to address hostname: no route to host:

I try to disable the iptables from the target host where oracle is installed no luck. What is wrong with this? Everything works locally I can connect and even ping the TNS.

One thing I notice my SID is orcl but the service instance that shows in lsnrctl status it shows as orclXDB


texts: How to automatically Reset Query Cache for improve table cache hit in MySQL?

texts:
I have a VPS running CentOS 6.4 and 2.5GB RAM, Intel(R) Xeon(R) CPU. Its MySQL version is  Server version: 5.5.35-cell. I have two active wordpress sites and two inactive sites (inactive sites use for some test and one for personal use) in this server.

This VPS MySQL configuration file has following query cache.

thread_cache_size = 16

query_cache_type = 1
query_cache_size = 30M
query_cache_limit = 1M

After 30 minutes left, my table cache hit rate goes to the lower level. After 1h 49m 0s MySQL up-time, it's 4% (334 open / 7K opened).

I think to overcome this issue, I have to reset query cache, rather than the flush query cache. Therefore, how do I automatically Reset Query Cache for optimize MySQL table caching?


texts:
I am having trouble connecting to my SQL Server database through a jtds connection:

Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/MnA;instance=SQLEXPRESS", "sa","");

SQL Server is enabled, TCP/IP is enabled, Mixed server authentication is enabled

Any ideas?

java.sql.SQLException: Login failed for user 'sa'.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:603)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:345)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC3.(ConnectionJDBC3.java:50)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at DB.dbConnect(testConnection.java:17)
    at testConnection.main(testConnection.java:7)


texts:
Table 1 - users

------------
id  | name
------------
 1  | foobar
 2  | barfoo

Table 2 - locations

-------------
id | loc_name
-------------
 1 | Location1
 2 | Location2
 3 | Location3
 4 | Location4

Table 3 - users_location

-----------------
user_id | loc_id
-----------------
 1      | 3
 1      | 4
 2      | 1
 2      | 2

The output that I'm looking for is this one

-------------------------------
name    | loc_name
-------------------------------
 foobar | Location3,Location4
 barfoo | Location1,Location2

Currently here is the code I got

SELECT users.name,locations.loc_name
FROM users
JOIN users_location ON users_location.user_id = users.id
JOIN locations ON locations.id = users_location.loc_id

Here is my current output:

----------------------
 name   | loc_name
----------------------
 foobar | Location3
 foobar | Location4
 barfoo | Location1
 barfoo | Location2


texts:
I have the results of a select that I am joining on that I need to get a slightly complex sum of
a column from.

I need the sum of result grouped on id PLUS the sum of result of any row where parent_id = id.

The data from the join:

cat_id   parent_id   name    result
------   ---------   -----   ------
1        2           name1   7
1        2           name1   1
2        4           name2   2

For example from the above I need a result like:

cat_id   name    sum(result)
------   -----   -----------
1        name1   8
2        name2   10

Any ideas much appreciated


texts:
I have a simple Postgres table which port numbers to listen on. If one port is used it can't be used again.

However due to multiple worker/threads they many times read simultaneously. I want that only one reader should be allowed at a time and then update the column value.

select port from socketport where available = 't'
update socketport set available = 'f' where port = port (from above)

Can above select statement be locked and then updated without releasing lock?


texts:
I am successfully able to set up master-slave replication of database with MySQL. So, whenever I am updating my database on master, It will automatically replicated on the slave. Now, I have situation like, it is also possible that database might be updated on the slave, and it should be replicated on master. Basically, I want the Replication in two direction.

Please, suggest me some possible ways


texts: MySQL and AUTO_INCREMENT columns in multiple-column index for InnoDB tables: support status?

texts:
Missing support for AUTO_INCREMENT columns in multiple-column index for InnoDB tables is a widely known limitation, as is the trigger workaround for when it is needed (see e.g this post). Yesterday, however, I ran into this thread claiming support for this feature was added as early as 5.1. I tried the following statement on my 5.7 install (mysql-community-server-5.7.19-1.el7.x86_64), and it appears to work flawlessly:

CREATE TABLE `tbl1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `data` datetime NOT NULL,
  PRIMARY KEY (`id`,`data`)
 ) ENGINE=InnoDB;

My problem is that, for the life of me, I cannot find a positive statement of support of the feature on MySQL's documentation site - or elsewhere. I am rather wary of deploying in production something unannounced  (which might be experimentally present) so I hope somebody can point me to the official status of this.

Edit: It turns out that the manual contains, in parts unrelated, this rather oblique statement:

“…an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.”

which suggests, but does not state, support.

Edit 2: Using information from @rolandoMYSQLDBA below, I also located this additional snippet: "An AUTO_INCREMENT column must appear as the first column in an index on an InnoDB table.". I am therefore accepting his answer.

And in case someone is wondering, the multiple column index thing is needed for table partitioning - the customer has auto_increment PK on all tables and they want to partition on a date column.


texts: Solutions to RLS (Row Level Security) in PostgreSQL for Desktop Application?

texts:
The Situation:

I have a desktop application that currently uses a database which is stored locally on the computer that is running the application, and I am starting to implement a server DB so that some of the information in this application can be shared between users on different machines. The local DB schema was not designed to support RLS, or anything close to it. I'm now trying to design a schema that holds the same data, but restricts access to it based on a users 'role' or 'group', etc.

My Understanding:

I would ideally like to use PostgreSQL, but others are fine, and saw that they have RLS support, but I'm unsure how to proceed. I'm thinking of using something like a RL_Groups and Users table, with a junction table like RL_Group_Users to determine row level access instead. I'm not sure of performance / security implications of either option, or even the alternative options. I've attached a simplified diagram to give some context to my current thoughts.

Supporting Info:

The local database is SQLite 3.
There are roughly 100 tables at the moment.
These 100 tables will all need some kind of RLS to restrict information to users and user_groups.

If I missed anything or you have any questions please comment so I can get back to you.
NOTE: The attached diagram has been simplified, data has been removed, etc. but it should be plenty to illustrate the problem.


texts: Do I need to use NVARCHAR rather than VARCHAR to properly store UTF8 characters in Oracle?

texts: I want my Oracle database to properly store ALL possible UTF8 characters. If NLS_CHARSET is set to AL32UTF8 is that all I need to do? Or do I need to also make sure I use NCHAR and NVARCHAR rather than CHAR and VARCHAR to properly store UTf8 characters?


texts:
I've had my server CPU spiked to 100% for about 20 minutes. The database was partially unresponsive in certain parts.( i know this because users complained that they cant use certain features, but they could login, and use other areas, only not specific). Before I could check current queries it was gone. I want to see what was the cause of it. Does anyone have some kind of instructions on this? How do I actually get to the root cause of this. How to see what query caused this?  I am using MSSQL
edit:
I found in RING BUFFER LOGS which record id and at what time CPU spiked. Is this something that can bring me to root of issue ?


texts:
I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would save a ton of time if it works.

I have two tables:

items: ~40,000 Rows
inventory: ~1,000,000 Rows

I have a query that matches up the inventory with the items the way the old system did, and updates the itemid column in the inventory table, to the id of the matched row in the items table (to work with the new system).

The problem is that the queries are taking forever, and I have very similar queries that I have to run in order to prioritize similar matches so waiting this amount of time isn't very great as the data in the old database is not consistent at all, so I have to continuously tweak, rerun, tweak, rerun, etc. It was taking a little over 40 minutes per query when there was only 250,000 rows in the inventory table.

In a nutshell, I need to make this query faster:

UPDATE inventory r
JOIN items t ON (FIND_IN_SET(r.orderNumber,
                REPLACE(
                    REPLACE(
                        REPLACE(t.orderNumber,'/',','),
                    ' ', ''),
                '[]',',')
            )
            AND r.selected_number != '99999'
            AND r.guideid = t.guideid
            AND (r.itemid IS NULL OR r.itemid = '')
                 )
SET r.itemid = t.id

My indices are (comma delimited obviously being composite, please excuse the names they were auto-generated by SQLYog):

items table:

PRIMARY => `id`
guideid => `guideid`, `orderNumber`
guideid_2 => `guideid`
orderNumber => `orderNumber`

inventory table:

PRIMARY => `id`
guideid => `guideid`, `orderNumber`
temp_usNum => `temp_usNum`
orderNumber => `orderNumber`
selected_number => `selected_number`
guideid_2 => `guideid`
itemid => `itemid`
itemid_2 => `itemid`, `guideid`, `orderNumber`, `selected_number`

Here is the result of the explain query:

I have access to all server settings, as it is on my local machine.


texts: How to Remedy Inconsistent MySQL Replication SYSDATE Value

texts:
When using master-master/master-slave replication in a MySQL environment, there's a possibility of creating inconsistent data when SQL statements use the sysdate instead of a hardcoded time in their SQL statements.

The issue that could potentially crop up is that if replication is halted for whatever reason (network blip, rebooting one of the hosts, replication thread halted, etc.) is that the SQL that involves sysdate will be turned into a real time at the time of the SQL being applied to the database. If replication is halted for meaningful time (1 hour to 1 day), the 2 databases will no longer be entirely synchronous. A row with a timestamp may begin to escape application workflows based on date-time boundaries.

What potential workarounds/remedies are there for finding such rows and updating them accordingly as a post-step to restoring replication between the databases? I'm almost certain we could use Veridata but that would require quite a bit of overhead, and I'm looking for a more elegant/simple solution to the issue.


texts:
I have a function that accepts as parameter an array like:

CREATE OR REPLACE FUNCTION my_func(some_data custom_datum[]) RETURNS VOID AS $$
    BEGIN
        create table foo_table as (

           select
           coalesce(foo_ind, bar_ind, ter_ind) as foobarter,
           import_date,

           -- do some stuff here

           from unnest (some_data) as T
           group by grouping sets ((foo_ind, import_date), (bar_ind, import_date), (ter_ind, import_date))
        );
    END
$$ LANGUAGE plpgsql;

The input array is generated by another function foo. So I call everything in this way:

select my_func(array(select foo()));

where the function foo is:

CREATE OR REPLACE FUNCTION foo() RETURNS SETOF custom_datum

The problem is that for a big amount of data array(select foo()) returns:

  ERROR:  array size exceeds the maximum allowed (1073741823)

What I am trying to workaround is the lack of possibility to pass different functions, as at the moment, the input array gets generated by different functions:

select my_func(array(select foo()));
select my_func(array(select bar()));
select my_func(array(select ter()));
.... etc

How can I workaround this problem?


texts: Recovering a lost datafile with no backup available

texts:
How do you recover a lost datafile if:

the database is running in archivelog mode
there is no backup is available


texts: Index Reorganize Generates a Non-Primary Filegroup is Full Error

texts:
First chance at a new job to do some much needed index maintenance on a system.
2-node cluster, W2K12R2, 512GB RAM, 64 logical cores, Fusion-IO, NetApp
SQL Server 2012 Ent SP1
3.5TB db
4 filegroups spread across 91 files.
4 of the 91 files were added 3 days ago to the filegroup called C below.
The 4 new files all show ONLINE in state_desc of sys.master_files
Very heavy CRUD operations 24/7- no complaints or tickets from NOC.
sp_whoisactive does not show any blocking or anything out of the ordinary.
RDP into active node and server shows plenty of disk space is available.
No major entries in SQL log file except the error(s) below.
No entries in msdb.dbo.suspect_pages
Other defrag operations on other small tables has worked fine.
When trying to defragment a 14MB table using reorganize against the CI, I receive the following and can reproduce the problem:

Error: 1105, Severity: 17, State: 2
Could not allocate space for object 'A' in database 'B' because the 'C' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the file group.

Any ideas? Thanks! --Todd


texts: Geo Route - Best way/db to store route path

texts:
We are developing an app in that UI need to show a path where a truck driver drives the truck.
Driver mobile will continuously send (example every 10 seconds) their live location.
Which database is more suitable for storing location path (polylines).
Our team has experience of Mongo (with Geodata), Redis, MySQL
Is there any best database to store this kind of Geodata?


texts: Appropriate Action for Dead Locks / Blocking

texts: I've setup my databases so that anytime a procedure is blocked for more than 45 seconds the database notifies the DBA email.  Is it bad practice to setup a way to auto kill the process that is doing the blocking?  I'm assuming yes; however, waiting until an off hours DBA can get to a computer and fix the change seams problematic as well.  Is there a better way to handle the blocking processes?


texts: I want to add a user/login to role in another database without switching databases

texts:
I have a role / login in a 'new' database mynewdb with a role "emailsender". I have scripted a profile for sp_send_dbmail and am now trying to script adding the the login to the DatabaseMailUserRole in msdb and seem to be going around in circles. Importantly I want to run my update script in mynewdb. I know I can use SSMS to do this. The question is:

How to script adding the user to the role in T-SQL from the mynewdb database without switching to msdb?


texts: Implementing older version of mySQL on RH9

texts:
I'm building a virtual machine running Red Hat Linux 9 (Shrike) and MySQL. This is to virtualize a current production system that needs new hardware.  Questions:

Can I copy the existing mysql daemon executables to the new machine from the old one and if so, where are they located, and what is involved?

Are their archived packages of the early mySQL packages? I was hoping to find the exact version running on my old server, to do an install, and the copy the database file.

Update:
Hi..... many thanks for the ideas.  I'm pretty sure the copy is the way to go.... (thanks Tometzky), but it is somewhat fraught, as the old hardware has a software RAID array, so I'm not sure I can do a bitcopy and have it work. And, the original mySQL is 3.23 (just about the first production version....). Earliest source I've found was at SkyServer which started at version 4.x.
A dd from one /dev/sda to /dev/sdb seems to work fine (simulated in VirtualBox).  So far an /dev/md0 (from a RAID 1 drive) doesn't work....but I'm going to try copying partitions individually, and then see if that will work.
Our original mySQL is 3.23 (just about the first production version....). Earliest packaged binaries at SkyServer are 4.1
Obviously, this is a temporary move to buy us some time while we re-write the applications that query the mySQL.


texts:
I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same index.

I am experiencing performance issues and I can't get avg_page_space_used_in_percent higher than 70% (leaf page count 1200, fill factor 80).

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)
    Apr 22 2011 19:23:43
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )


texts: Change Permissions of MSSQL Server generated files

texts:
I am routinely taking a BAK file and restoring it. As a result of this action, an MDF file is generated from it by MSSQL.

My problem is that after the detaching the database I need to be able to move the MDF file programmatically and the user that is going to do this needs to not be in the Administrators group or to be the Default SQL server account. Is it possible to configure MSSQL server to that when it creates an MDF file such as this the permissions of the file include a group of my own arbitrary choosing?

It seems logical that this an MSSQL server setting somewhere, that there is a list of permissions that get added to files when they are created but I am having a great deal of trouble getting Google to distinguish between this case and other similar but unrelated cases.


texts:
I'm storing user preferences for sort order in a table:

$queryHistory = "SELECT orderby,ascdesc FROM user WHERE UserID= ".$id;

$resultHistory = doQuery($connection, $queryHistory);
$numHistory = mysql_num_rows($resultHistory);
if ($numHistory > 0) {
  while($rowHistory = fetchRow($resultHistory, $queryHistory)) {
    $orderBy = getResult($rowHistory, $resultHistory, 1);
    $sortBy = getResult($rowHistory, $resultHistory, 2);
  }
}

And constructing a query based on that which looks like:

$otherQuery = "SELECT *
               FROM  user
               WHERE user.UserID = $id
               ORDER BY ".$orderBy." ".$sortBy;

Is it possible to do this with a single query, rather than first reading the preferences then running the actual query?


texts:
I use indexes regularly but it's still hard for me in certain conditions to know if they are helping or hurting. There are a few guidelines I follow, but I am not sure if they are good nor am I sure of their justification.

It is better to create an index on a narrow datatype than it is on a wide datatype (e.g. INT over DATETIME).
It is better to create an index on multiple columns than it is on a single column.
It is better to create an index on a column that is never (or rarely) updated than it is to index frequently changing columns.

Are these good guidelines? Since I'm not entirely sure why I follow these guidelines, can you help explain what is the justification for each and when would they not apply?


texts: What allows SQL Server to trade an object name for a string passed to a system procedure

texts:
What causes it to be legal to pass an object name to the system stored procedure sp_helptext?

What mechanism converts the object name to a string?

e.g.

-- works
sp_helptext myproc
sp_helptext [myproc]
sp_helptext [dbo.myproc]
-- and behaves the same as a string
sp_helptext 'myproc'
sp_helptext 'dbo.myproc'

-- does not work
sp_helptext dbo.myproc -- Msg 102, Level 15, State 1, Line 1 incorrect syntax near '.'
-- an additional case that does not work.
sp_helptext [dbo].[myproc] -- Msg 102, Level 15, State 1, Line 1 incorrect syntax

It seems odd that I'm not required to single quote valid proc names, unless it has a . separating schema name and procedure name. I'm looking for an explanation of how it gets auto converted from a quoted name to a string literal to be passed as the value of the parameter.

I don't have a specific issue to resolve; I am simply inquisitive about things that aren't documented.


texts:
We have an UPDATE in our 2am nightly load that has on two occasions pegged CPU at 100% and never completed after many hours. I strongly suspect that this is happening because of inaccurate statistics causing nested loops in a join. I am not able to reproduce it in lower environments with the same hardware specs and postgres configuration.

I need to prove that the nested loops are occurring before I can put in an RFC to address the problem (e.g. adjusting analyze and autovacuum settings). I have setup auto_explain but this only seems to capture plans for queries that actually finish. How can I log plans for queries that do not finish?

In SQL Server I would be able to do this by running sp_whoisactive with @get_plans=1. I am looking for something similar in Postgres. The only idea I've had so far would be to run EXPLAINs using cron, but this seems very hacked together.


texts: Why Regenerate Snapshots For Merge Replication

texts:
I am in the process of implementing merge replication in SQL 2012 with web sync.

I am wondering two things,

Why is it suggested to regenerate the snapshots every 14 days by
default?
With web sync you are an anonymous subscriber, so how can replication know when to clean up metadata for a user who never
syncs

So the first question there. If a person syncs every day, and they are pulling back about the same amount of changes specifically why would we have to regenerate the snapshot? I'm not sure why a sync such as this would become slower?

Is it a case of knowing when it can clean up metadata? The user with the oldest snapshot has just regenerated a new snapshot, so it means we can clean up the metadata up to the next oldest snapshot?

This leads to the second question. If I have a user who I have given a merge replication solution to as a demo. It turns out that they never use the system and have only synced it once at the start to test it out. They may have even removed it from their computer.

If their shapshot job has been turned off and they never sync does it mean we get stuck with a whole bunch of metadata that replication cannot clean up? Does replication at some point conclude that that person is not using the system and block them out?

The reason I ask this is I am using anonymous subscribers. When we have normal subscribers and we delete the subscription the server is connected to directly and the subscription is removed from the publication. This does not happen for web sync.


texts: What indexing to be used over string columns(text type) with string length around 3000 characters

texts:
I am using open source columnar dbms (postgresql+Cstore).

I am trying to speed up the response time of queries. My database has tables with strings mostly. String columns contain huge data (up to 3000 characters per value). A linear look up will take it too long to give results. My queries vary from simple '=' to like, contain and wildcards.

I am unable to conclude on what indexing should I use on string columns. I have hash table, trie structure, B+ tree index in mind.

The problem with hash table is collisions and even if use perfect hashing, there are chances of returning false positives. Moreover, hash index cannot solve like, contains and wild card queries. Apart from that, my data have duplicate values in columns like company name etc., and building perfect hash is getting difficult over values with duplicates in them.

Trie and B+ tree: Trie seems useful for all the queries, but as I mentioned, my strings are of huge length. Can trie be scaled to that extent? I have the same question regarding B+ Tree also.

So, to sum up, I have lengthy string columns and I need speed in query execution. Which indexing should I use, or is there any other method to speed up searches, apart from what I've mentioned?


texts: Restoring and upgrading multiple databases

texts:
I have a SQL Server 2005 instance on my PROD server. I have been given a task to migrate databases from that instance to a different box (SQL Server 2008 R2 on Windows 2008 R2). The current instance has a maintenance plan set up, which backs up 8 different databases every night to a single .bak file.

How do I take that bak file and restore it onto the new SQL Server 2008 R2 instance? I have tried using SQL Server 2008 R2 SSMS, and set the restore from option to device and point it to that .bak file, but what should I target restore to since I have multiple databases to be restored in the new instance?

What is the best approach to migrate all databases in single shot to the new instance without doing one at a time?


texts:
Some RDBMS's seem to allow a query without a FROM clause, eg:

postgres=# SELECT 'foo' bar;
┌─────┐
│ bar │
├─────┤
│ foo │
└─────┘

But for those that allow it, why would SELECT count(*) not return 0?

postgres=# SELECT count(*);
┌───────┐
│ count │
├───────┤
│     1 │
└───────┘


texts: AWS RDS : Setting Create and other grants only for single Database is not working

texts:
We are using AWS RDS Mysql, and we are trying to give SELECT access to all databases i.e RO only, but for a single database, we want to give all permissions i.e CRUD. It's not working out unfortunately with Create, user is not able to create a table.

user data-team-user

Commands I tried :

GRANT SELECT ON *.* TO 'data-team-user'@'%';

GRANT ALL PRIVILEGES ON `api-gateway-copy`.* TO 'data-team-user';

GRANT CREATE, SELECT,INSERT,UPDATE,DELETE ON `api-gateway-copy` TO 'data-team-user'@'%';

GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON *.* TO 'data-team-user'@'%';
SHOW GRANTS FOR 'data-team-user';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, REFERENCES, TRIGGER ON `api-gateway-copy`.* TO 'data-team-user';
flush privileges;

GRANT CREATE ON `api-gateway-copy`.* TO 'data-team-user'@'%';

Still :

 SHOW GRANTS FOR 'data-team-user';
+--------------------------------------------------------------------------------------+
| Grants for data-team-user@%                                                          |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'data-team-user'@'%'                                           |
| GRANT SELECT ON `tmp`.* TO 'data-team-user'@'%'                                      |
| GRANT SELECT ON `sys`.* TO 'data-team-user'@'%'                                      |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                            |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                         |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                 |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                              |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                   |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                    |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                   |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                              |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                           |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                   |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                              |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `api-gateway-copy`.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'


texts: Oracle Enterprise Manager - Where do I find the error logs?

texts:
I'm not familiar with oracle management. The problem is when I try to connect to the enterprise manager through the Web browser I get this error:

In english: "Internal Error has occurred. Check the log file for details."

Where to find the log file to see what's the problem?

I'm using Windows 7 and Oracle 11gR2 EE.


texts: How can I set up a read only odbc connection?

texts:
I have a user that needs to do queries on an SQL Server 2008 database that I administrate. They have rights to edit the data, but I'd rather they weren't able to do it directly against the table (i.e., I'd prefer they'd use the application that they were given).

What I'd like to do is give them a Read Only ODBC connection, and hang an MS Access MDB/ACCDB off of it, and let them query to their heart's content without accidentally "updating" data.

Is there a way to do this, or am I stuck having to create a RO user for them to use, and then threaten them with much pain if they connect using their regular RW account? In which case why not threaten them with much pain for editing data outside of the application?


texts: What is the impact of SHOW STATUS, and how often is acceptable to call it?

texts:
I am setting up a monitoring thread in my application that should use the SHOW STATUS call of mysql to periodically calculate system / db runtime statistics.

Being relatively unfamiliar with datbase backends, my question is

  What is the impact of running SHOW STATUS against the database? And how often is acceptable to call it? Would this cause any significant db slow downs?

The rest of my application collects statistics once per minute.


texts: How to get same value in a unique column but differentiated by other column values?

texts:
I have a table like this

 ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID
---------------------------------------------------
  1 |   VITAMIN A  |       1         |      1
  2 |  PARACETAMOL |       2         |      1

PRODUCT_NAME must be unique for same manufacturer and same package ID, BUT it can be duplicate if manufacturer ID or Product ID are different.
I tried using UNIQUE but it is rejecting same values with different Manufacturer ID or Package ID

My desired output would be like :

 ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID
---------------------------------------------------
  1 |   VITAMIN A  |       1         |      1
  2 |  PARACETAMOL |       2         |      1
  3 |   VITAMIN A  |       2         |      1

Notice third value of PRODUCT_NAME 'VITAMIN A' is same but MANUFACTURER_ID is different.
Do we have any work-around for this other than UNIQUE?


texts: Select data from a table to insert/update another similar table

texts:
I have two tables (in two databases):
create table catalog_product_entity_text
(
    value_id int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id smallint unsigned default 0 not null comment 'Store ID',
    entity_id int unsigned default 0 not null comment 'Entity ID',
    value longtext null comment 'Value',
    constraint CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
        unique (entity_id, attribute_id, store_id),
    constraint CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID_STORE_STORE_ID
        foreign key (store_id) references store (store_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_TEXT_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references eav_attribute (attribute_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_TEXT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references catalog_product_entity (entity_id)
            on delete cascade
)

create table catalog_product_entity_varchar
(
    value_id int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id smallint unsigned default 0 not null comment 'Store ID',
    entity_id int unsigned default 0 not null comment 'Entity ID',
    value varchar(255) null comment 'Value',
    constraint CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
        unique (entity_id, attribute_id, store_id),
    constraint CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID_STORE_STORE_ID
        foreign key (store_id) references store (store_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references eav_attribute (attribute_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references catalog_product_entity (entity_id)
            on delete cascade
)

My _text table contains some data that I would like to transfer to the _varchar table, but there's no correlation with the primary keys. The primary key is just auto increment.
Logically the tables are connected by the set (attribute_id, store_id, entity_id).
I would like to perform two actions:

Select all the values from _text table for specific attribute ids, e.g.
select cpet.attribute_id, cpet.store_id, cpet.entity_id, cpet.value
from catalog_product_entity_text cpet
where cpet.attribute_id in
      (<myids>)
  and cpet.value is not null;

Now for each row in the above results I'd like to:

If there's corresponding (attribute_id, store_id, entity_id) in the _varchar table, update that row.

If there's no corresponding (attribute_id, store_id, entity_id) in the _varchar table, add a new row with the data from the source row.

If I had a common primary key, I'd use REPLACE INTO, but in this case I don't think it's suitable.
How can I build such a query? It doesn't need to be a single query.


texts: RMAN restore of primary database - impact on standby database

texts:
I've just restored an 11g primary DB that was destroyed. Restored the controlfile from tape and performed a full restore, recovery, and open resetlogs. Looks fine now. How will my physical standby (using dataguard) handle this? When I 'show configuration' in dgmgrl I see:
Configuration - DG_Config

  Protection Mode: MaxPerformance
  Databases:
    TESTDB11  - Primary database
    TESTDB11A - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

How can I repair this standby after a restore of the primary?
Thanks.


texts: composite clustered index with partitioning

texts:
I have a table with hundreds of millions of rows.  Data is regularly retrieved via various queries and inserted.
There is a clustered index on

LookupID (int)
TypeID (int)
Created (datetime)

(These 3 ensure uniqueness for each row.)

There is also a unique ID (int) which could be used as a clustered index.

I tried moving the clustered index to the unique ID and created a non clustered index on the existing composite; my thinking was it would be a more efficient index as the numbers are sequential.  It didn't seem to make a huge difference to query performance, but there are so many use cases further testing would be required to definitively answer this.

My question is 2 fold - if I introduce partitioning based on Created (for the export of data for archiving), would it be better to have the composite as a non clustered index?  I have just started reading about partitioning in detail and haven't quite got to terms with 'partition alignment'.
The second question is, am I wrong in my assumption that changing these indexes would be a worthwhile endeavour?

Apologies if this is too broad or should be in stackoverflow.


texts: Indexing Multiple Possibly Missing Columns

texts:
I'm working with a particularly large database, and am not particularly in tune with some of the intricacies of RDBMS's as I work primarily in PHP/WP and with smaller databases. Either way, I have created a table of data based on the aggregate combination of four other tables with about 100 rows each, producing about 122m rows.

The aggregate data contains the row IDs of the source row from each table in columns, and I need the ability to remove IDs from each of these.

Sources: (include head, chest, arms, legs)

CREATE TABLE head_data (
    id smallint(2) unsigned NOT NULL auto_increment,
    ...
    PRIMARY KEY  (id)
)

Combinations Table

CREATE TABLE combinations (
    id bigint(20) unsigned NOT NULL auto_increment,
    head_id smallint(2) NOT NULL,
    chest_id smallint(2) NOT NULL,
    arms_id smallint(2) NOT NULL,
    legs_id smallint(2) NOT NULL,
    weight decimal(5,1) NOT NULL,
    height smallint(3) NOT NULL,
    PRIMARY KEY  (id)
)

What I'm doing is getting the maximum height for all items less than a certain weight and returning the head_id, chest_id, arms_id and legs_id and populating other attributes for the combination based on those.

The Query:

SELECT *
    FROM combinations
    WHERE id = (
        SELECT id
        FROM combinations
        WHERE height = (
            SELECT max(height)
            FROM combinations
            WHERE weight < $weight
        )
        LIMIT 1
    );

This was the most efficient indexing scheme I tried (height,id and height,weight). Now I need to add functionality to this query that will allow me to remove items from "head" or "arms" from the list. My -- probably horrible -- thought is a dynamically built query depending on what the user selects such as this:

SELECT *
    FROM combinations
    WHERE id = (
        SELECT id
        FROM combinations
        WHERE height = (
            SELECT max(height)
            FROM combinations
            WHERE weight < $weight
            AND id NOT IN (
                SELECT id
                FROM combinations
                WHERE head_id IN (##,##,##)
                   OR chest_id IN (##,##,##)
                   OR arms_id IN (##,##,##)
                   OR legs_id IN (##,##,##)
            )
        )
        LIMIT 1
    );

However, at this point, I'm not sure how to build the index for those ids. The size of an index for all possible use cases would be incredible (there are 122m rows):

head,chest,arms,legs
head,arms,legs
head,legs
chest,arms,legs
chest,legs
arms,legs
legs

Therefore, is there a way to set the query to have null values that would take into account the index (head,chest,arms,legs) if for example chest was not specified but legs were? For example, will "WHERE chest_id IS NOT NULL" allow the index to be used? Or do I need to make a relations table and index that in this case?

Also, if there is a more efficient way to rewrite that rather large SQL query, I'm all ears. I tried using the following with the index (height,weight,id), but it took 3x as long:

SELECT c.*
FROM (
    SELECT id FROM combinations
        WHERE weight < 20 ORDER BY height DESC limit 1
     ) ids
JOIN combinations AS c USING(id)


texts:
I have tried my hand at building a shell script to backup our wordpress database using tar balls and chron jobs on a Debian environment.
The problem I am facing now is that our wp database after exporting, using the script, and then importing, using phpmyadmin, loses some special characters. Most noteably the € sign.
Phpmyadmin lists the database as latin1_swedish_ci and that is the collation setting I am selecting in the Import tab of phpmyadmin (iso-8859-1).
I am using the following script to export the database:

  mysqldump --user=user --password=password --default-character-set=latin1 --skip-set-charset databasename -r wp-db.sql

    tar -cpzf /home/backups/wp-backups/website-wp-$(date +"%d-%m-%Y--%H-%M").tar.gz wp-db.sql && rm wp-db.sql

My best guess is that there must be some issue with collation but I am not aware where some type of conversion happens in the steps that I am taking. At some point the € must be dropped.
When inspecting the wp-db.sql using file it is described as iso-8859-1, hence I wonder where conversion takes place.
I have since tried to test some things and discovered that I am not capable of converting a UTF-8file to iso-8859-1if it includes a € sign. At least not using

  iconv -f UTF-8 -t ISO_8859-1 test

From what I gather mysql runs in UTF-8 natively, might that be the issue?

If I do not bother with any of this it will completely ruin things. Pretty much everything that isnt wp files but stored in the database will be displayed incorrectly or will be completely missing. By adding these two arguments to my script I have eliminated most of the issues apart from special character problems.


texts: Database design for large volumes of data

texts:
My application needs to support multiple users. Each user has a large volume (millions) of data entries. The application needs to select entries based on the current logged-in user.

What is the best SQL database design for such a requirement? To put everything into a single table with an additional user field? Or to create a table for each user?

Is NoSQL better in this case?


texts: mysqldump with LIMIT and --order-by-primary

texts:
I want to use mysqldump to retrieve X first rows from a database (where "first" are ordered by primary key).

I'm trying to use mysqldump the following way:

mysqldump [connection-info] -where"1=1 LIMIT 40" --order-by-primary database table >> file.sql

(this is inside a script who runs different configurations for different tables)

Now, the problem, that command creates the following query:

SELECT /*!40001 SQL_NO_CACHE */ * FROMtableWHERE 1=1 LIMIT 40 ORDER BY primary_key

Is an invalid query, I need the LIMIT 40 to be placed after the ORDER BY statement.


texts: How to run MongoDB as a non root user in Linux?

texts:
I can start MongoDB as $sudo service mongod start. When I start without sudo it gives me this error:

/etc/init.d/mongod: line 58: ulimit: open files: cannot modify limit: Operation not permitted
/etc/init.d/mongod: line 60: ulimit: max user processes: cannot modify limit: Operation not permitted
Starting mongod: runuser: may not be used by non-root users

1) Now, I've changed ownership to the non root user on all mongo directores I could find i.e. /var/lib/mongo, var/log/mongodb, data/db, var/run/mongodb

$sudo chown -R nonRootUser:nonRootUser [directory]

2) I've deleted mongod.lock files

3) I've run --repair too

It still gives me the same error.

I also tried

$mongod --fork --logpath /var/log/mongodb.log

about to fork child process, waiting until server is ready for connections.
forked process: 18566
ERROR: child process failed, exited with error number 1

mongod.log file says this:

2016-03-17T15:03:49.053+0000 I NETWORK  [initandlisten] waiting for connections on port 27017
2016-03-17T15:03:54.144+0000 I CONTROL  [signalProcessingThread] got signal 15 (Terminated), will terminate after current cmd ends

It's Amazon Linux. I am able to start it like this $mongod, but I want to run it as daemon so it runs continuously.

The nonRootUser is a new user I created in addition to ec2-user. Maybe there are some config issues relating to running daemon processes if you're not ec2-user?

UPDATE: Changed ownership on everything to ec2-user, still getting exactly the same errors as before.


texts: Get value increment during an hour of a day

texts:
I query YouTube Data Api for a list of most popular videos on a channel and then get their statistics, 4 times per hour (each 15 minutes, by cron). The data is stored in Postgres, but dumping it and loading into another SQL DB wouldn't be a trouble.
Now I have following table of data:

 video_id| views_count | likes_count | timestamp
---------+-------------+-------------+---------------------
     foo | 100         | 1           | 2018-12-01 12:01:03
     foo | 101         | 1           | 2018-12-01 12:16:06
     foo | 105         | 1           | 2018-12-01 12:31:01
     bar | 199         | 0           | 2018-12-01 12:01:02
     bar | 200         | 0           | 2018-12-01 12:16:08
     bar | 301         | 5           | 2018-12-01 12:31:02
     ... | ...

UPD: Here's the schema (pasted to sqlfiddle):

CREATE TABLE video_statistics
(
  video_id TEXT not null,
  views_count INTEGER not null,
  likes_count INTEGER not null,
  timestamp TIMESTAMPTZ not null
);

How should I query that data in order to get increments by hour in view_counts and likes_count columns, grouped by video?
To clarify what I want to get:

hour_of_day|video_id|views_increment|likes_increment
-----------+--------+---------------+---------------
     ...   | ...
     11    | foo    | 4             | 0
     12    | foo    | 5             | 1
     ...   | ...
     11    | bar    | 73            | 0
     12    | bar    | 102           | 5
     ...   | ...

In other words, it's a "best time to post video" based on historical data, taking into account data during many weeks and months.
Should I rather dump the data into some timeseries DB or other, more appropriate for such cases DB, and query it there? Or should I just resort to calculating this in code?


texts:
I am looking for an equivalent functionalities of JSON_VALUE in SQL Server 2014. Can any of you post an alternate of the below code which is supported in SQL Server 2014

SELECT JSON_VALUE(ERROR_MSG, '$[0].applicationName') AS APPLICATION_NAME
FROM tablename

It errors out as below when run SQL 2014

  'JSON_VALUE' is not a recognized built-in function name.


texts: Permissions required to alter a database file

texts:
Totally at the beginning in administering a database.

Using EMS SQL Manager ... latest version.  How do I over come below

ALTER DATABASE joneslocker2
MODIFY FILE (
    NAME = joneslocker2,
    MAXSIZE = UNLIMITED )
GO

which results in an error:

  User does not have permission to alter database 'joneslocker2', the
  database does not exist, or the database is not in a state that allows
  access checks.

I don't have a clue what to set next.


texts:
I have a big query (Entity Framework generated one) and I'm using a Calculated Field. Performance has already improved by adjusting the Clustered Index to include the [On] (datetime) column.

The sort on the calculated function is responsible for 100% of the performance slowness according to the Query Plan.

The function estimated the order of the item and ranks it according to the votes/comments it received and gives newer items a higher ranking. So the score changes every hour + every vote on item/comment (similar to Reddit).

In short, this is what it ranks by

CAST( [Project4].[C1] AS float) / (POWER( CAST( DATEDIFF (hour, [Project4].[On], '2018-05-01 00:00:00.0000000') AS float) + cast(2 as float(53)), cast(1.8 as float(53)))) AS [C1],

The problem is, when I sort by [On], I have immediate results. When I sort by the mentioned calculation, it takes 12-16 seconds.

The literal '2018-05-01 00:00:00.0000000' is dependent on current time.

How would I improve this?

Edit:
I didn't think it was useful to post the query, because it's created by EF and very big (also for Stackoverflow, see this pastebin )

More useful: pastetheplan


texts: How to control reindexing that results in high log send queue

texts:
During reindexing using Ola's IndexOptimize stored procedure, the log send queues for remote asynchronous replicas can grow quite large if there are a lot of large index defrags, requiring gigantic transaction logs.  There is an @Delay parameter in the script that can be used to insert a delay between each index operation, but it doesn't provide the ability to delay only when the log send queues are high.

A more intelligent throttling mechanism is required to control the log send queue sizes during heavy reindexing.

And yes, I know that reindexing isn't really necessary with our enterprise SSDs, but we do it to appease the third-party support organizations that will blame fragmentation for any performance issue.


texts: Doing a comparison on two csv files with no Primary Key

texts:
I'm wondering if in SSIS there is a way to compare two csv files(with the same structure) with no reference key to make a record unique.

To give you a better picture from what I mean; In initial load we load data from a csv file into a table, in the subsequent loads, we will get another csv file (same format) that can have different records. We should compare what we have in the table vs. what exist in the new version of the csv file and only load the changed sub set. If there is a new records, it should be inserted, if update, it should be updated, if record deleted in the source, it should be marked as inactive in the table.

Now my questions are:

Is there any way to compare two csv files in SSIS, while there is no key to make the
records unique?
How can we compare two tables in SQL Server, while there is no key to make
records unique?

The volume of data in the csv file is quite high, more than 20 millions records!

Any idea is appreciated.

Thank you,

Nazila


texts:
I'm having a problem specifying a value within an OPTIMIZE FOR statement. I'd like to optimize the query with the value as a string, but I must be doing something wrong, because SQL gives the following error:

  The value specified for the variable "@test" in the OPTIMIZE FOR
  clause could not be implicitly converted to that variable's type.

The example below is slightly contrived but the problem is the same as the one I'm having with my actual query.

declare @TEMP table(asWord nvarchar(max), asNumber int)

insert into @TEMP (asWord, asNumber) values (
    'one',
    1
), (
    'two',
    2
)

DECLARE @test nvarchar(max)
SET @test = 'one'

select * from @TEMP
where asWord = @test
OPTION (OPTIMIZE FOR(@test = 'one'))

What am I doing wrong?


texts: Why would the Parallelism (Repartition Streams) Operator Reduce Row Estimates to 1?

texts:
I'm using SQL Server 2012 Enterprise. I've come across a SQL Plan that is exhibiting some behavior I don't find entirely intuitive. After a heavy Parallel Index Scan operation, a Parallelism (Repartition Streams) operation occurs, but it is killing the row estimates being returned by the Index Scan (Object10.Index2), reducing the estimate to 1. I've done some searching, but haven't come across anything that explains this behavior. The query is quite simple, though each of the tables contain records in the low millions. This is part of a DWH load process and this intermediate data set is touched a few times throughout, but the question I have is related to the row estimates in particular. Can someone explain why accurate row estimates go to 1 within the Parallelism (Repartition Strems) Operator? Also, is this something I should be concerned with in this particular situation?

I've posted the full plan to Paste the Plan.

Here's the operation in question:

Including the Plan Tree in case that adds any more context:

Could I be running into some variation of this Connect item filed by Paul White (further in-depth explination on his blog here)?  At least it's the only thing I've found that seems to be even remotely close to what I'm running into even though there is no TOP operator in play.


texts: Why does MongoDB give me the cannot use 'w' > 1 when a host is not replicated Error message?

texts:
Here is the information about my development environment: •Microsoft Visual Studio Community 2015 •.NET Framework 4.6 •ASP.NET MVC assembly System.Web.Mvc Version=5.2.3.0 •MongoDB.Driver 2.0.1.27 •Mongodb 3.0.6

From MongoDB client using DOS command prompt, I got the following:

C:\Program Files\MongoDB\Server\3.0\bin>mongo
 MongoDB shell version: 3.0.6
 connecting to: test
 > use foo
 switched to db foo
  > db.runCommand( { getLastError: 1, w: 2, wtimeout:5000 } )
 {
       "connectionId" : 6,
       "n" : 0,
        "badGLE" : {
                "getLastError" : 1,
                "w" : 2,
                "wtimeout" : 5000
               },
           "ok" : 0,
            "errmsg" : "cannot use 'w' > 1 when a host is not replicated",
           "code" : 2
       }
       >

-Is there something wrong with the way I configured mongoDB database on my development computer? -When I read the error message: "cannot use 'w' > 1 when a host is not replicated" it seems to me that mongoDB by default forces developers to have replica set. Is that correct?

I'm only using MongoDB for development on my local computer. I don't want to be forced to use a replica set. Is there any way to resolve the "cannot use 'w' > 1 when a host is not replicated" error message?


texts: How to change MySQL InnoDB cluster from single primary mode to multi primary mode?

texts:
My goal is to setup, in a lab environment, a MySQL InnoDB cluster in multi-primary mode.

I have read much, but I cannot get this setup to work.

Ubuntu 16.04.3 server LTS x 3
SQL0 - SQL2

Ubuntu 16.04.3 server LTS x 2
REP0 - REP1

All server instances are in Virtual machine except REP0 is on bare metal and REP1 is off line.

Installed MySQL server 8.0 from Oracle APT repo using mysql-apt-config_0.8.10-1_all.deb.

These servers have been up and down with many config changes trying to find a setup that works.

Set MySQL server 8.0 with Legacy Authentication Method on all boxes.

My current /etc/mysql/my.cnf is based on Justin Ellingwood @ DigitalOcean.

This is /etc/mysql/my.cnf from SQL0. This machine was at one time the primary in a single-primary setup that did not work correctly.

The other /etc/mysql/my.cnf file are very similar to this one with the # Host specific replication configuration section changed.

The error I am trying to resolve now is from /var/log/mysql/error.log.

[ERROR] Plugin group_replication reported: 'It is not allowed to run single primary mode with 'enforce_update_everywhere_checks' enabled.'

Yes I do have these set in my.cnf because I want multi-primary mode. I do not want to run in single-primary mode.

loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

Where, How, What else do I need to change for these to be in multi-primary mode and not error out?


texts:
Can you change recovery mode from simple to full with users working on the server?
While the server is changing the mode is the work of the users interrupted? If so, for how long.
Version: SQL server 2016
Thank you very much


texts:
I'm currently dabbling in ER modeling and wondering if it is possible to create a chain of is-a relationships. Example:

Here I'm basically trying to model specific in-stock items available for rent which are products in general and and a product type in particular.

Would this ER model be legal and is there a better way to express such a relationship?

Thank you in advance.

Matt


texts: Pull entire row based on MAX(Column) MySQL

texts:
I am trying to pull whole record from database for the highest "Score" column.

TestTable structure

------------------
ID | Name | Score|
------------------
1  | john | 10
2  | mark | 20
3  | ted  | 15

So when I run

SELECT ID, Name, MAX(Score)
FROM TestTable

So I want the query to return entire row 2 because this is the highest value.

This is what I am expecting:

2  | mark | 20

But it does not necessarily return correct row. It always return MAX(Score) but other values are not necessarily from the same row.

This is what I am getting:

3  | ted  | 20

Is there a better way of pulling this data?
I am using MySQL.

Also I am not interested in sub-query such as

SELECT ID, Name, MAX(Score)
FROM TestTable
WHERE ID = (
    SELECT ID From TestTable WHERE Score = MAX(Score);
)

Because I need it for big query using a lot of data, and it needs to perform good.

Please help :)


texts:
I have a query to find something in a long text by a dynamic parameter
this is my query:
declare @searchTerm nvarchar(100)
set @searchTerm='Product/IndexShowProduct/'
select
REPLACE(product.name, ' ', '-') as lnkName,
product.*,ProductCategory.Name as ProductCategory,Shop.Name as shopname
,isnull(Picture.PicturePath,'DefaultProduct.jpg') as PicturePath
 from Product
 left join ProductCategory  on ProductCategory.ID =Product.ID_ProductCategory
 left join Picture on Product.ID_pic=Picture.ID
 left join Shop on shop.id=product.ID_Shop
 LEFT JOIN
  (select P.ID as POrdered from Product P , Article A
 where  A.ID=@IDArticle
 and **A.SiteContent like '%'+ltrim(rtrim(@searchTerm))+ltrim(rtrim(str(P.id)))+'%')**
  P
 ON P.POrdered=Product.ID

 where Product.ID_ProductCategory=case when @ID_ProductCategory=0 then ID_ProductCategory else @ID_ProductCategory end
order by P.POrdered desc

you know this part has low performance so I would like to use fulltextsearch in this part like this phrase:
contains(A.SiteContent,ltrim(rtrim(@searchTerm))+ltrim(rtrim(str(P.id))))
but it sounds that the function "contains" doesn't like dynamic parameter "p.id"!!!! I can't use the static text for search.is there any problem with that?
I look forward to hearing from you.
best regards.


texts: How to find out ALL the users that can connect to SSAS?

texts:
I have an user called mycompany\aives that is not listed in the list of administrators in the SSAS, not belong to any of the AD (active directory) groups listed there,

however,

he can connect to the cubes in this server (called SASBI01).

if you see , however, the second picture, mycompany\aives is listed as one of the administrators on the box (SASBI01)

Where does it say that the administrators on the box can (or not) connect to the cubes?

How can I find out (preferably via script), a list of users that can connect to the cubes and their permissions?


texts:
Am I misunderstanding something here? I have two tables, table1 and table2. Table2 has a foreign key that references a field in table1 with ON DELETE SET NULL associated with the foreign key. Table2 also has a TIMESTAMP field that has ON UPDATE CURRENT_TIMESTAMP set.

Modifying an entry in table2 successfully updates the timestamp field.
Deleting the linked entry in table1 successfully sets the foreign key field in table2 to NULL, but does not update the TIMESTAMP field in table2.

When the field in in table2 gets set to NULL, isn't that considered an UPDATE, and the timestamp should get updated too?


texts: Is there any risk associated with stopping mysqldump incomplete?

texts:
I started to backup 30GB table at wrong time and now I want to stop it because it is estimated to take 7.5hours.

I used following command and options:
mysqldump -u db -p user table --hex-blob | gzip> 25Nov-table.sql.gz

My question is how to stop mysqldump now (leaving backup incomplete) without inflicting any damage to my database (though I think there will be no risk)?


texts: How often should we use Update Statistcs.(More frequent or Not)

texts:
We usually use Ola Hallengren's solution that takes care of Update Statistics which is inside the Index optimize job that we have. We have it scheduled for every Sunday midnight in all servers (1500+) as a standard practice.

Now, there are a few servers where there are a lot of performance troubles during the middle of the week and we run sp_updatestats for that particular database when an incident is raised for performance issues. We have a huge environment with all versions in use, 2000 to 2016. It is the 2012 & 2014 versions where we have been running the update stats manually for specific database mid-week.

Recently, we also had to schedule sp_updatestats for every day for some very active databases due to recurring troubles.

My questions :

How often should we have it scheduled?
What are the drawbacks for scheduling it too often?
Is there a way of evading the drawbacks with the regular updates running so frequently as I heard it takes more time to compile and might degrade performance for sometime?

Please help me with the experience you had with this.


texts:
How do I truncate the transaction log in a SQL Server 2008 database?

What are possible best ways?

I tried this from a blog as follows:

  1) From the setting database to simple recovery, shrinking the file
  and once again setting in full recovery, you are in fact losing your
  valuable log data and will be not able to restore point in time. Not
  only that, you will also not be able to use subsequent log files.

  2) Shrinking database file or database adds fragmentation.

  There are a lot of things you can do. First, start taking proper log backup using
  the following command instead of truncating them and
  losing them frequently.

BACKUP LOG [TestDb] TO  DISK = N'C:\Backup\TestDb.bak'
GO

  Remove the code of SHRINKING the file. If you are taking proper log
  backups, your log file usually (again usually, special cases are
  excluded) do not grow very big.


texts: SQLite Data Types In Context of Disk Space

texts:
I would like to better understand which data types in SQLite take up more disk space. I've looked through official documentation but could not find anything in context of storage space.

Any guide link or a quick explanation will do.


texts: Online Schema change when DB has existing triggers

texts: I'm doing some due diligence before attempting a MariaDB schema change that really needs to be performed online, but seems like I'm going to have issues with the existing DB triggers in using a tool like Percona online schema change, Github gh-ost and even Facebook's online schema change.  Are there any options that I am missing out there? Any resources to recommendations to minimize downtime?


texts: Do table locks get released after a query finishes when WAITFOR is used after that query within the same batch and transaction?

texts:
If I have a query that reads from a table, if that query is proceeded with the WAITFOR keyword (within the same batch and transaction) will the table lock of the preceding query be held until the WAITFOR completes and the transaction finishes? (I mean in the context of a Read Committed isolation level server.)

Example query:

BEGIN TRANSACTION

SELECT *
FROM ReallyLargeTable

WAITFOR DELAY '00:30';

COMMIT TRANSACTION

Does the WAITFOR DELAY cause the preceding query to hold its table lock against ReallyLargeTable for an additional 30 seconds until the delay is over and the transaction is committed?


texts:
I am dealing with a rather massive ETL processing which keeps throwing me the ORA-12549 error.

Have been looking for problems in the database so far and nothing. The DBA says the database is "fine" - has resources, hasn't reached maximum files to open or processes and have space on all tablespaces.

I start to wonder if this error could have been thrown by client. However it is a Java application using JDBC to connect and the heap size is of 16gb, also not nearly filled up according to the GC logs.

The process itself is rather simple: make a massive select into Oracle, apply some logic over the data and commit is periodically to another table. I have used commits from 5million lines to mere 100k (divided in 100 batches of 1k lines). The error is always about the same point.

Can someone give me some insight on what is happening? Where should I look for clues?

Client-side, ulimit is 1024 (on Oracle's server is is 65k), could it be related?


texts: The most efficient way to join huge tables

texts:
I have a table with 20M rows, and each row has 3 columns: time, id, and value. For each id and time, there is a value for the status. I want to know the values of the last and the next periods for a specific time and id, and have the following query to get the values:

update a1
set  a1.value_last = b1.value,
     a1.value_next = c1.value
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.period = b1.period + 1
left join tab1 c1
on a1.id = c1.id
and a1.period = c1.period - 1

It seems that the query takes forever and the log file increased by more than 10 GB. I'm wondering what's the most efficient way to write this query? I know using index will speed up the joining process, but how can I reduce the logging?

I'm using SQL Server 2016 on Win10 64bit.


texts: How to list connections in Secondary Nodes

texts:
In my cluster on Atlas, have a many connections in secondary nodes.
How to list this connections?

When execute this command, list the current connections in primary node.

db.currentOp(true).inprog.reduce(
  (accumulator, connection) => {
    ipaddress = connection.client ? connection.client.split(":")[0] : "unknown";
    accumulator[ipaddress] = (accumulator[ipaddress] || 0) + 1;
    accumulator["TOTAL_CONNECTION_COUNT"]++;
    return accumulator;
  },
  { TOTAL_CONNECTION_COUNT: 0 }
)

{
    "TOTAL_CONNECTION_COUNT" : 229,
}

But, I have 700 connection in replicaSet.

Thank's


texts: Update column value based on other column with manage duplicate value MySQL query

texts:
I need to update column value based on other column with adding separator and with manage duplicates values using sql query.

 For example :

 I have one database table A.

Here in it two columns :
1) locality
2) slug

Locality column already have data.
So need to update 'slug' column based on locality value.
Please review below screenshots.

Currently i have below table.

And i need to update 'slug' column like below screenshot.


texts: Can hardcoding parallelism in Oracle hints lead to sub-optimal performance

texts:
We have a large Oracle 11.2.0.4 database (data warehouse). Our developers hard code parallelism into their queries e.g. (PARALLEL(16)). Multiple queries can be running at once, all with large hard coded PARALLEL hints. Is this limiting Oracle's ability to optimize degree of parallelism based on available resources?

(Trying to figure out if we should stop this practice and let Oracle optimize/maximize for us).


texts: Create multiple field indexes with different types on postgres

texts:
I have the following query on an 18M records table (which grows daily by 5k):

SELECT * from marketing
where start_time < '2017-02-19 08:00:00'
AND NOT name LIKE '%MyWorld%'
AND product = 'Product2'

I am using postgres 9.3.1

This is a report table that is being updated in an offline process, but I need to return the results from the report online, quickly.

I thought to use gin index, however, since my query filters 3 columns, I believe that I need a composite index

CREATE INDEX marketing_name_start_date_product_gin_trgm_idx ON tbl USING gist
(name, start_date, product gin_trgm_ops);

However this seems like a waste for me to create a gin index for columns which don't use like in queries.

What do you suggest? What is the best approach?


texts: Need to create join on multiple ids on same cell


texts: MySQL - how to dynamically add columns with subquery in Select clause based on date range

texts:
I have the following statetment:

 Select a.Data AS Data_A,b.Data AS Data_B,
(Select Round(Sum(C.Value)) From Values c
  Where
  (Year(c.TimeStamp) = x) and
  (Month(c.TimeStamp) = y)
) AS MonthYear

From Table1 a  Left Join Table2 b on (a.ID = b.ID)

Ist there any way to add a column to the select clause based on my desired range of date? (where Year(c.TimeStamp) < 2019 and Month(c.TimeStamp) <= 12)

Example1 - i want to see the data for 1 month:

Select a.Data AS Data_A,b.Data AS Data_B,
(Select Round(Sum(C.Value)) From Values c
  Where
  (Year(c.TimeStamp) = 2018) and
  (Month(c.TimeStamp) = 1)
) AS Jan2018

From Table1 a  Left Join Table2 b on (a.ID = b.ID)

Example2: i want to see the data for 3 months

Select a.Data AS Data_A,b.Data AS Data_B,
(Select Round(Sum(C.Value)) From Values c
  Where
  (Year(c.TimeStamp) = 2018) and
  (Month(c.TimeStamp) = 1)
) AS Jan2018,
(Select Round(Sum(C.Value)) From Values c
  Where
  (Year(c.TimeStamp) = 2018) and
  (Month(c.TimeStamp) = 2)
) AS Feb2018,
(Select Round(Sum(C.Value)) From Values c
  Where
  (Year(c.TimeStamp) = 2018) and
  (Month(c.TimeStamp) = 3)
) AS Mar2018

From Table1 a  Left Join Table2 b on (a.ID = b.ID)

I hope my question is understandable. :)

Thank you for reading.


texts:
We have a 3rd party Payroll application which uses a SQL database on our server. On the database server we have weekly full backups, nightly differential and regular log backups. These are kept for a period of time to meet the business DR requirements.

The problem we have is that when a payroll is run, the payroll department require that a backup of the database be taken and kept for a specified period of time (5-7 years depending on the country for legal reasons). When they run the backup from the Payroll application it does a full backup. This obviously puts out our differential backups, as SQL now thinks it has a new full backup.

Is there a way that we could capture the backup script coming from the Payroll application and alter it to be Copy-Only, or is our only options to get the program changed and/or do the backup outside of the payroll program?

=======

Update to try and explain our goal a bit better:

What we want to do is intercept the backup statement coming out of the payroll program, add the command copy-only to it, and then pass it to SQL, and then return the results back to the payroll program.


texts: Won't start: InnoDB: Corrupted page ... Tablespace was not found

texts:
After a server crash (Ubuntu 16.04), when I try to start Mysql (Mysql 5.7) using innodb_force_recovery=0 it doesn't start and error.log shows:

InnoDB: Checksum mismatch in datafile: ./panel_financiero_v2/kpis_analytics.ibd, Space ID:93, Flags: 33. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
InnoDB: Corrupted page [page id: space=93, page number=0] of datafile './panel_financiero_v2/kpis_analytics.ibd' could not be found in the doublewrite buffer.
InnoDB: Tablespace 93 was not found at ./panel_financiero_v2/kpis_analytics.ibd.
InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
InnoDB: Cannot continue operation.

I just can start Mysql using innodb_force_recovery=6 (I can't start Mysql using innodb_force_recovery=1 as the error message advises).
The .ibd and .frm files of the troubled table are in the right directory (no empty files); I even tried (just in case, with no real hope) to delete these two files (moving them to another directory) but it doesn't work neither.

Any way to repair or recover the table (bearing in mind that innodb_force_recovery=6 starts Mysql in read-only mode and I even can't create a new table)? Any way, at least, to start mysql normally (with innodb_force_recovery=0) even losing the information of that troubled table?


texts:
I'm trying to understand the labyrinth that is the rules about AG backups.I've got Ola Hallengren's scripts installed on machines TST01/TST02.  02 is the preferred replica for backups. I have an AG with AdventureWorks in it and one database outside the AG - DBAutility.

I have the below in my SQL Agent job on TST01.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘User_Databases’, @CopyOnly = ‘Y’,@Directory = N’C:\MyDirectory\’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 168, @CheckSum = ‘Y’, @LogToTable = ‘Y'” -b

When I run the job on TST01 I get a backup of DBAUtility taken from TST01 and written to that machine.  I get no backup of AdventureWorks.  I'm taking this approach from a post from Brent Ozar.

Brent on Ola H and AG Backups

I know Ola has other parameters for AG backups, but I couldn't seem to get that to work as expected either.


texts: Privileges for database owner; application user

texts:
Quick version:

What command should I issue to enable a database owner to allow it to access tables in this database and can this be done from that owner's account?

Longer Version:

I am creating a database on RDS.  I have a 'root' user that I have configured with Amazon.

Amazon automatically creates the group role 'rds_superuser' which is very privileged, but not actually a superuser.

I am creating a database and user for the application as follows:

create database master_integration;
CREATE ROLE master_application LOGIN ENCRYPTED PASSWORD '...' VALID UNTIL 'infinity';
GRANT ALL ON DATABASE master_integration TO GROUP rds_superuser WITH GRANT OPTION;
GRANT ALL ON DATABASE master_integration TO GROUP master_application;

\c master_integration;
ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO rds_superuser;

I updated this script to reflect suggestions by Craig Ringer regarding how I should be handling this.

When the app connects (with the master_application credentials) it creates  (and therefore owns) the tables.

My issue is that I cannot use my administrative (rootish) log in to run queries because that user has no privileges on the table.

I have been able to solve this before by running the following from the application account:

GRANT ALL privileges ON ALL TABLES IN SCHEMA public to rds_superuser;

But it seems hacky to have a subordinate user grant privs back to an administrative user.

So...Is there a command that I can run before or after I create the tables from the application which will ensure that the owner of the database can access the tables within the database?

update after re-trying the alter default privilegs...

This still does not grant access to the tables; I see it being suggested elsewhere and it makes complete sense, but it is not working for me.  From a psql shell:

master_integration=> \ddp
                           Default access privileges
      Owner       | Schema | Type  |             Access privileges
------------------+--------+-------+-------------------------------------------
 integration_root |        | table | integration_root=arwdDxt/integration_root+
                  |        |       | rds_superuser=arwdDxt/integration_root
(1 row)

master_integration=> \dp users
                           Access privileges
 Schema | Name  | Type  | Access privileges | Column access privileges
--------+-------+-------+-------------------+--------------------------
 public | users | table |                   |
(1 row)

integration_root is my superuser-ish user and users is a table within my database.

Update

I got a fairly useless response from someone at Amazon.

They asked me to call ALTER DEFAULT PRIVILEGES from the master_application login.  While this would probably work, it would not answer my question (which is how do I make this happen solely from the rds_superuser account).

I asked them to clarify this and they went away.


texts:
I have a script that cross joins about 4 tables:

SELECT *
FROM Districts
CROSS JOIN Seasons
CROSS JOIN Sectors
CROSS JOIN Sites

The above query will combine all the tables into one, see a sample picture:

I want to add two more tables Group and Client, but not the entire tables, just the COUNT of both tables. so after the print column in the picture below, there should be two more columns, GroupCount for group table count and ClientCount for client table count. How do I do it?


texts:
I've changed my.ini with port=3308 but after restart the service mysql 5.6 is working in the same default port 3306.

I've tried in mysql 5.5 and it worked fine.

C:\Archivos de programa\MySQL\MySQL Server 5.6

my.ini

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
port = 3309
# server_id = .....

What am I missing? Is there any bug?

EDIT BY @RolandoMySQLDBA

Please run the following

cd C:\Archivos de programa\MySQL\MySQL Server 5.5
dir
cd C:\Archivos de programa\MySQL\MySQL Server 5.6
dir

and post the output in this question


texts: mongoimport error - Failed: error connecting to db server: no reachable servers

texts:
I am currently trying to learn MongoDB and I am having trouble finding a solution for this problem. When I run a mongoimport command it I get the following error:

~ mongoimport --host localhost --port 27017 --db test --collection people --file ~/Downloads/mongodb-consultas.json --jsonArray
2015-09-27T20:46:03.228-0600    [........................] test.people  0.0 B/684.2 KB (0.0%)
2015-09-27T20:46:03.745-0600    Failed: error connecting to db server: no reachable servers
2015-09-27T20:46:03.745-0600    imported 0 documents

When I substituted localhost for 127.0.0.1 I get the following error:

~  mongoimport --host 127.0.0.1 --port 27017 --db test --collection people --file ~/Downloads/mongodb-consultas.json --jsonArray
2015-09-28T15:15:42.047-0600    connected to: 127.0.0.1:27017
2015-09-28T15:15:42.049-0600    Failed: error reading separator after document #1: bad JSON array format - found no opening bracket '[' in input source
2015-09-28T15:15:42.049-0600    imported 0 documents

The document that I am trying to import is not corrupted as I got it from a MongoDB tutorial, and it is working for other users.

My MongoDB shell version is 3.0.6.

I have a MongoDB server by running mongod in the command line. The command mongo runs fine as well.

My firewall allows incoming connections for MongoDB.


texts: How to find the session id of running jobs

texts:
Trying to get the session id of executing jobs

;with JobDetails as
(
select Job_Id = left(intr1,charindex(':',intr1)-1),
       Step = substring(intr1,charindex(':',intr1)+1,charindex(')',intr1)-charindex(':',intr1)-1),
       SessionId = spid
    from master.dbo.sysprocesses x
    cross apply (select replace(x.program_name,'SQLAgent - TSQL JobStep (Job ','')) cs (intr1)
    where spid > 50 and x.program_name like 'SQLAgent - TSQL JobStep (Job %'
)
select *
    from msdb.dbo.sysjobs j
    inner join JobDetails jd on j.job_id = jd.Job_Id

but it throws the following error

  Msg 8169, Level 16, State 2, Line 47 Conversion failed when converting
  from a character string to uniqueidentifier.

I tried to cast the job_id to varbinary but it produces no result

;with JobDetails as
(
select Job_Id = left(intr1,charindex(':',intr1)-1),
       Step = substring(intr1,charindex(':',intr1)+1,charindex(')',intr1)-charindex(':',intr1)-1),
       SessionId = spid
    from master.dbo.sysprocesses x
    cross apply (select replace(x.program_name,'SQLAgent - TSQL JobStep (Job ','')) cs (intr1)
    where spid > 50 and x.program_name like 'SQLAgent - TSQL JobStep (Job %'
)
select *
    from msdb.dbo.sysjobs j
    inner join JobDetails jd on cast(j.job_id as varbinary) = jd.Job_Id

but when I copy paste a job_id from cte like this

select job_id, name
    from msdb..sysjobs
    where job_id = 0x128A47A31EAB8F4DA1AD852093D815F5

it works. Any idea how to fix this query


texts: SQL Data Collector - SSIS required on source server?

texts:
For SQL Data Collector:

Do you need SSIS (SQL Server Integration Services) installed on the server that you are collecting data from. Or can the packages be run from another server -- like the one that the MDW database is on.


texts: reducing migration time of a large size postgresql database

texts:
We have a postgresql 9.4 DB server that handles 2 DBs, each ~800GB. No replication is implemented yet.

We reduced pg_dump time with using -j & -Fd options to about 2 hours.

The problem is, we need to separate DBs, so we setup a fresh new server with Postgresql 9.6, but pg_restore takes a very long time (~2 days). I tried parallel pg_restore using -j (4, 8, 16, although VM has a 4 core CPU and 16GB of RAM) and tuned some parameters in posotgresql.conf like shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, wal_buffers and so forth, but they didn't have any significant effect on reducing time of restore.

Even, I tried to restore data and schema separately, but same results.
I think the problem is we have many tables with 15M records that all have index and indices will increase restore time but I'm not sure of that

We thought about replication, so we do not have any significant downtime (after sync is complete, we can stop old/master server and change replica/slave to acts as a master server), but as version of postgresql servers is not same, it didn't work out! Updating main server postgresql version to 9.6 and then start replication will take such a time!

Any advice and suggestions will be greatly appreciated!


texts: What is the purpose of session pool_mode in pgbouncer?

texts:
Since in this mode pgbouncer creates outbound connection for each inbound connection it seems to me that pgbouncer doesn't work as pooler.
It's just 1 to 1 mapping.

Where am I wrong?


texts: How to sort the data fetched from horizontal partitioned tables

texts:
I have a telco billing software system. In it there are daily logs of users' calls. The logs are horizontally partitioned by date (month). Each partition is stored in a separate database and may be spread over multiple instances.

In the UI the user will specify a date range. The data returned can be sorted on any field. The date range may span over multiple partitions. The application must support paging through the date range's data.

I cannot load too many records into memory for sorting. Putting sort inside the query only gives me sorted data inside one result-set.

So I need to sort data from multiple partitions which are each individually sorted. How can I return sorted records to the UI from multiple sorted result-sets?