texts: MySQL Rows to Columns to HBase (using Sqoop)

texts:
I have a table that looks like this in MySQL:

id | date | num | avg

I have found that I can use this for rows to columns and it works so far:

SELECT
    CONCAT(id, '-', date),
     MAX(IF(`num` = 0, avg, NULL)) num0
FROM table
GROUP BY
    id,
    date;

The problem is I have 4000 'num' values (values are integers from 0 to 4000) and I do not want to write 4000 MAX statements manually. Is there anyway I can put a loop in there somehow? Ideally, I would like to have 4030 columns...

Edit:

I have edited my post to give some more context to the problem I am facing. I am sorry as I should have probably done this earlier.

Context of problem: I have to migrate data from MySQL to HBase. I want to import the values so that each day (which would be represented by a row key along with the id) have many columns (num) which have a value (avg). The column family is avg. I was originally planning on selecting Sqoop's "Select statement" but I understand I may be pushing the boundaries regarding MySQL's columns and I am not sure what the max is. If anyone knows a better way of doing this and can share with me, that would be great.


texts: Automatically have Nonclustered indexes in a Different Filegroup

texts:
Is there a way to automatically move clustered indexes A on one filegroup, and all nonclustered indexes to a different filegroup B upon DDL creation? We have developers constantly creating tables/indexes, and would like a way to automate this in dev environment. We have an old SAN Hard drive, and in some cases separating indexes on different filegroups may optimize performance, according to our DBA.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017


texts: How to run SQL Profiler to monitor 1 specific stored procedure?

texts:
I have an issue where a stored procedure will run properly when executed from SQL Management Studio, but when the stored procedure is called from an application, it doesn't return the results to the application.  This application regularly utilizes stored procedures as part of it's operation, so why this specific SP isn't returning results is odd.

Since the application works fine otherwise and since the stored procedure works just fine when run in SQL, what I'd like to do is setup sql profiler to monitor just that stored procedure, while I try to execute/call it from the application.

I'm hoping to avoid both the extra load on the system and sifting through a ton of irrelevant data that a generic profiler run would provided on a busy database like this one.

Thank you in advance for any advice on this.


texts: Oracle EBS 12 Installation Error : Usage ShelExec Document File

texts:
I am trying to install Oracle e-Business Suite r12 for windows 64-bit
I have downloaded all files from oracle website (V46243-01)
When I try to run ShelExec.exe I get this error message Usage: ShelExec Document File

Note: I dont have CD's for installation so how can I install directly from folder/directory


texts: query_cache_type = 0 vs query_cache_type = 1

texts:
Recently, I moved from standard MySQL to Percona, and used the Percona Wizard to generate my.cnf.

However, I  can see that, by default, the generated settings for my.cnf use query_cache_type = 0. (query cache is disabled).

The only thing I run on the server is a Wordpress blog. My questions are:

May I enable query cache?
There are some Wordpress plugins that offer database cache. Is the result similar of enabling query cache?


texts: Why is an index I created not being used in a query execution?

texts:
Background: this question stemmed from another question I asked, so you can get additional DDL/indexes from there, if need be.

I have a query inside an indexed view, like this:

SELECT      [a].[ListingId],
            -- lots of other columns from [a], [b], [c], [d] etc
FROM        [dbo].[Listings][a]
INNER JOIN  [dbo].[ListingFeatures][b] ON [a].[ListingId] = [b].[ListingId]
INNER JOIN [dbo].[ListingBuildingDetails][c] ON [a].[ListingId] = [c].[ListingId]
INNER JOIN [dbo].[ListingLandDetails][d] ON [a].[ListingId] = [d].[ListingId]
WHERE       [a].[IsVisible] = 1
AND         [a].[IsLive] = 1
AND         [a].[AgencyCompanyId] IS NOT NULL

Note the 3 predicates.

I created a covering index for the predicates, and included the ListingId column, hoping the query optimizer would use the index then do a KEY LOOKUP to fetch the other columns from that table, then do a clustered index seek to get the other table data.

Here's the index definition:

CREATE NONCLUSTERED INDEX [IX_Listings_ForListingSearchView] ON [dbo].[Listings]
(
    [IsVisible] ASC,
    [IsLive] ASC,
    [AgencyCompanyId] ASC
)
INCLUDE (   [ListingId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Quite the contrary, here's the estimated execution plan:

My questions:

Why isn't the optimizer using my covering index? Is it because it thinks it's cheaper to scan the whole table?
I noticed if i change the query to just fetch a.ListingId and not the rest of the columns from a, it uses my index (and the reads/cost seems much lower, due to avoiding the index scan). Should I use this in a subquery then?
Why is the query optimizer doing an clustered index scan and not a seek?
This query is the one existing in an index view. When the index view needs to be updated, is this the actual query that runs to update itself? My thinking was yes, which is why I'm trying to optimize it. If the index view update doesn't use this query, then I won't bother?


texts:
Let say, that I have principal server named P, and mirror server named M. I've got 7 databases that are in server P, and 7 mirror databases on server M. Whole engine works fine when we are talking about server failover. But sometimes I've got events, when only one database is broken ( for example data consistency problem ). Then this one database is switched to mirror server, and rest stay on principal server. What's wrong with this solution ?

On databases there are procedures and views, that use full-name object calling, for example:

SELECT * FROM DATABASE1.dbo.TABLE1
  LEFT JOIN DATABASE2.dbo.TABLE2

And now, when only DATABASE1 is switched, and when SP from DATABASE1 tries to select TABLE2 from DATABASE2 - it is impossible. DATABASE2 is in restoring mode.

My idea is to switch all database to MIRROR server whenever one database is broken. Is there a way to detect database failover ? (maybe trigger) Maybe You've got better ideas to solve this problem ?


texts: Deny drop permissions for all other users except actual administrator - SQL Server 2008

texts:
I have a database "myDB" running in a Windows Server machine. Users (coming under Administrators group) remotely access this server and log-in to this database using Windows Authentication mechanism.

I want to deny the drop object permissions for all these users, except for the actual Administrator in this machine

P.S - All the users whose drop permission is to be revoked comes under the Administrators user group.


texts:
If I have a script like this...

SET ECHO ON

SELECT 'I want the person reading the spool to see this whole query.'
  FROM DUAL
 WHERE interesting_logic_ = 'DEBUGGABLE';

SET ECHO OFF

PROMPT I only want the terminal to show this text, not the PROMPT command.
SELECT '...and this text, without the query' FROM DUAL;
HOST cmd /c echo ...and this text, without the command line

SET ECHO ON

How do I keep SET ECHO OFF from echoing?

In other words, how do I achieve the equivalent of @ECHO OFF from DOS and Windows batch scripting?


texts: Best tool/approach to report on table contents over time

texts:
I've got a table in SQL Server full of data that is a working DB of data collection reports. This table only stores the current state (meaning it doesn't keep historic anything).

There are a few key metrics that I'd like to begin tracking over time from this table. Most notably the table has a user's name, their assignments (one row for each), status values (in progress, scheduled, etc.) and last activity date.

Because the table itself doesn't have any history information, I'm looking for the best way to sample the data on some periodic basis recording the current state values and timestamping it.  Once this is done, I'll be able to ask questions via reporting like "How many reports was Joe working on on tuesday and what were their statuses?"

What would be the best way to do this, short of creating a SQL Server table to house the information, writing scripts to do the collection and a report/query interface for all of this?

My google-fu is usually really strong but the general concepts are colliding way too firmly with proper names of things that are completely unrelated, thus I can't find any good info out there.

Anyone got experience with such a process?  What can you tell me to get me on the right direction?

Edit: I do not currently collect the aggregate data, nor do I currently have a place to store it, though that could be as simple as a SQL Server table. I'm not going to be able to spend as much time as I'd like supporting this reporting capability, so I was hoping for some type of a packaged solution that some admins can maintain with minimal development efforts.


texts: Oracle Import problem caused by different character sets

texts:
I'm trying to import an Oracle 11 export into Oracle 11 XE.

I get the following messages:

  import in XE fehlerhaft import done in WE8MSWIN1252 character set and
  AL16UTF16 NCHAR character set
  import server uses AL32UTF8 character set (possible charset conversion)

Any ideas, how I can import this dump into Oracle 11 XE ?

Edit:

Given a table

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3)  NOT NULL,
    Name                  VARCHAR2(60) NOT NULL,
    Abkuerzung            VARCHAR2(5)  NOT NULL
);

I get errors like this

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "BDATA"."ARTIKEL"."ABKUERZUNG" (actual: 6, maximum: 5)
Column 1 ABL
Column 2 Aufbewahrungslösung
Column 3 AfbLö

Some rows are missing from the import.


texts: get count of subjects with at least one record in posts table

texts:
I have two tables: posts and subjects.

posts may have subjects, but not all subjects may have been assigned to some posts. Now I want to count only those subjects with at least one post related to them.

subject columns are: id, name
posts columns are: id, title, subject_id


texts: Why does SSRS not update a report's description?

texts:
I created a SSRS 2016 solution and deploy my reports.
Each report has a "DESCRIPTION" property, that gets visible in the report's properties in report manager. There in the report manager, this property can be modified as well.

Unfortunately this description property will NOT BE changed after it was changed in visual studio and re-deployment of the report.

I checked the report server database to exclude any caching issues. There the value of the description column also stays untouched by/after re-deployment:

USE ReportServer;
select [Description], * from dbo.[Catalog] where Path like '/myReports/Demo';

Since this property is part of the report, I expect it to get modified by deployment. Is there anything we can config or do I miss anything? Why does this not work?

Environment:

ReportServer: SQL-Server 2016 Developer Edition
Microsoft Visual Studio Enterprise 2015
    Version 14.0.25424.00 Update 3
    Microsoft .NET Framework
    Version 4.6.01055
    SQL Server Data Tools   14.0.60812.0
    Microsoft SQL Server Data Tools
    SQL Server Reporting Services   13.0.1700.68
    Designer für Microsoft SQL Server Reporting Services
    Version 13.0.1700.68


texts:
The Problem

My SQL Server database contains a view that looks like the following:

+-------------+-----------+------+-------+------+------+
| CAR_ID      | MAKE      |COLOR | Miles | Seats| MODEL|
+-------------+-----------+------+-------+------+------+
| 1           | FORD      | RED  | 100   | 4    | CAR  |
| 2           | TOYOTA    | RED  | 500   | 2    | TRK  |
| 3           | FORD      | BLACK| 10    | 4    | CAR  |
| 4           | HYUNDAI   | BLUE | 150   | 4    | VAN  |
| ...         | ...       | ...  | ...   | ...  | ...  |
| 1000        | TOYOTA    | GRN  | 200   | 8    | CAN  |
+-------------+-----------+------+-------+------+------+

I would like to create custom groupings from this table. My desired output might look like the following-

+-------------+-----------+
| Group       | sum(Miles)|
+-------------+-----------+
| Red Cars    | 500       |
| Red Toyota  | 325       |
| Trucks      | 563       |
| Black Car   | 952       |
| ...         | ...       |
| Black Ford  | 100       |
+-------------+-----------+

Indeed, it looks like a simple "GROUP BY" query. However, in my case, the groups can become rather complex with many logical statements and the number of groups is also very large which makes a "group by" query intractable for me to use. My solution has been to use the WHERE clause to create custom groups. Some may call this Dynamic SQL.

My Solution

I have a created a C# script which I use to store the group definitions and the query itself. The group definitions are stored in a dictionary. I iterate through the dictionary, passing each grouping to the WHERE clause of a select statement, and then I send the SQL statement to the SQL server.

The server returns a Datatable with my custom grouping to my C# application. I then Bulk Insert the Datatable into another table on the SQL server. If I have 50 groupings, I query the SQL server 50 times and bulk insert 50 times. Here is an example:

Dictonary x = {GRP1, (COLOR = BLACK or Miles <50),
               GRP2, (Seats <5 AND MODEL = CAR)};

FOREACH(key in x)
{
  y = "SELECT * FROM CAR_TABLE WHERE " + x[key]
  Datatable dt = QueryServer(y);
  BulkInsertToDB(dt);
}

Question

There must be a better and more efficient way of doing this. I was thinking about throwing my query into a stored procedure on the server, but I would still have to make calls to the stored procedure for each grouping. I then thought perhaps there is a way to store these groupings server size and run all of the queries concurrently in a stored procedure. Any guidance would be helpful.

Clarifications:

One row could belong to many groups. I have sum of miles and sum of age of vehicles. I would use the key of the dictionary as the group name and pass the key to the SQL Server. I would update the datatable in c# with the group name.


texts: Site running with mysql replication, shall we shut down the site when replication broken?

texts:
let me illustrate the situation here:
An online store is setup with two replicated server: A(Asia) and G(Germany)

The setup is aimed to improve speed for users from both location.

On apache side, file replication is setup to sync files on both site.
DB replication with MYSQL is setup to sync data.

Here comes to problem when DB replication is broken.
during replication is down
Item X , available quantity is 2

Mr G order one item from G

Mr A order two item from A.

At the point, the   remain quantity should be -1

However, because of the broken replication, both users ordered successfully.

Can anybody advise if this is the case and people should shut down related application once the DB replication behind broken?


texts: What type of SQL Server Replication Allows Modifications

texts:
I have an application that uses a database for each instance of the application, one instance per client. Each client wants a database for reporting as we do not allow reporting from the production system, we use a simple script to backup and restore the database nightly for each client.

Now each client wants to modify their reporting copy of the database. They want to add tables and views to help with reporting. What type of replication would allow me to keep the data in sync from the publisher to the subscriber while also allowing the subscriber to be modified with new tables, views and sprocs? Also which type of replication would best fit this scenario of a reporting database?


texts: getitng difference of two consecutive rows

texts:
Hi i have a result like this from my query.

and i want to get the difference of a specific epmloyee break time so i need to minus the time (OUT-IN).

I have search the net but i see nothing related to my problem.

sample data below to make things more better.

ID  Date-TIme           Log-Type
1   2020-02-03 01:52:01   IN
2   2020-02-03 02:00:50   OUT
3   2020-02-03 03:12:14   IN
4   2020-02-03 03:48:55   OUT
5   2020-02-03 05:22:00   OUT
6   2020-02-03 21:59:52   IN
7   2020-02-03 21:59:57   IN
8   2020-02-03 22:20:21   OUT

expected result.

ID  Date-TIme           Log-Type       RESULT
1   2020-02-03 01:52:01   IN
2   2020-02-03 02:00:50   OUT
3   2020-02-03 03:12:14   IN           1:12
4   2020-02-03 03:48:55   OUT
5   2020-02-03 05:22:00   OUT
6   2020-02-03 21:59:52   IN           5:38
7   2020-02-03 21:59:57   IN
8   2020-02-03 22:20:21   OUT

My query on getting the result.

  SELECT L.NEVENTLOGIDN, LPAD (nuserid, 6, '0') nuserid, u.susername,
    TO_CHAR (TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + ( (ndatetime) / (60 * 60 * 24)), 'YYYY-MM-DD HH24:MI:SS')
    date_time, l.nreaderidn, r.sname,
    CASE WHEN l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription in(Select Distinct SDESCRIPTION from TB_READER where SDESCRIPTION is not null and SDESCRIPTION not Like '%IN%' and SDESCRIPTION not like '%OUT%') and upper(sname) like '%' || upper('OUT') || '%') THEN 'OUT'
    WHEN l.nreaderidn  IN (SELECT nreaderidn FROM tb_reader where sdescription in(Select Distinct SDESCRIPTION from TB_READER where SDESCRIPTION is not null and SDESCRIPTION not Like '%IN%' and SDESCRIPTION not like '%OUT%') and upper(sname) like '%' || upper('IN') || '%') THEN 'IN' END logtype
   FROM TB_EVENT_LOG l, TB_READER r, TB_USER u
   WHERE
   l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription in(Select Distinct SDESCRIPTION from TB_READER where SDESCRIPTION is not null and SDESCRIPTION not Like '%IN%' and SDESCRIPTION not like '%OUT%'))
   AND NDATETIME >= (trunc(sysdate -1) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60
   AND ndatetime <= (trunc(sysdate) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60
   AND l.nuserid = u.suserid
   AND l.nreaderidn = r.nreaderidn
   ORDER BY 2, 4;

It is normal in data to have a consecutive IN or OUT in that case i need to get the latest.

Hope someone help me out with this.
Thank you in advance


texts: SQL Server database backup(2012) restore on lower version(2008)

texts:
We want to restore 2012 *.bak to 2008 sql server, we don't want to prefer import/export logical backup.

Looking forward to know downgrade procedure.


texts: Matching left and right single-quotes used as apostophes

texts:
I have four columns containing names and want to search these using a LIKE in a Microsoft SQL Server environment.

The complication comes that names may include left and right single-quotes / angled apostrophes (i.e. ‘ and ’, char(145) and char(146) respectively), which should match a straight apostrophe (i.e. ', char(39))

Doing the following is very slow:

SELECT person_id
FROM person
WHERE REPLACE(
          REPLACE(
              person_name,
              CHAR(145),
              CHAR(39)
          ),
          CHAR(146),
          CHAR(39)
      ) LIKE '{USER_INPUT}'

As explained in SQL replace statement too slow on Stack Overflow, this is because the use of REPLACE makes the statement unsargable.

Is there a way that SQL Server can handle situations like this in a better way?

One solution which has been proposed is to have the application generate a 'searchable' value which concatenates all of the fields (person_name, person_surname, person_nickname, etc.) and converts the problematic characters at the point of editing.  This could be effectively indexed and searched.  Storing this data in a separate SQL table/column would require less application rewrite than implementing a full NoSQL solution like Lucene.

The example above is a simplification: the query doesn't literally get built as I explained above and we do implement SQL injection (and other) protections.

The question is how to replace the angled-apostrophes with straight ones in the table data. To clarify:

User supplies O‘Malley - this should match both O‘Malley or O'Malley
User supplies O'Malley - this should match both O‘Malley or O'Malley

We need to replace the SQL data, not the user's input.  We can convert the user input on the way through the application so that if they input angled apostrophes we change them to simple apostrophes before passing in to SQL.  It's the data in SQL we need to standardise.

Unfortunately the data must stay in the database as the correct angled bracket, but when we do the search we need to match them all against straight apostrophes.


texts: Why the query optimizer choose this bad execution plan?

texts:
We have a mariadb table (stories) with above 1TB of data, periodically running a query that fetches recently added rows for indexing somewhere else.

innodb_version: 5.6.36-82.1
version       : 10.1.26-MariaDB-0+deb9u1

The query works just fine when the query optimizer decides on using the secondary index to do a range walk through (in buckets of a 1000)

explain extended     SELECT  stories.item_guid
    FROM  `stories`
    WHERE  (updated_at >= '2018-09-21 15:00:00')
      AND  (updated_at <= '2018-09-22 05:30:00')
    ORDER BY  `stories`.`id` ASC
    LIMIT  1000;

+------+-------------+---------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+---------------------------------------+
| id   | select_type | table   | type  | possible_keys               | key                         | key_len | ref  | rows   | filtered | Extra                                 |
+------+-------------+---------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+---------------------------------------+
|    1 | SIMPLE      | stories | range | index_stories_on_updated_at | index_stories_on_updated_at | 5       | NULL | 192912 |   100.00 | Using index condition; Using filesort |
+------+-------------+---------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

But occasionally, even with small differences in the data set (Note the second timestamp difference with the query above, worth to mention that the whole table holds data for several years and holds several dozens millions of rows) decides to use the primary key index

explain extended     SELECT  stories.item_guid
    FROM  `stories`
    WHERE  (updated_at >= '2018-09-21 15:00:00')
      AND  (updated_at <= '2018-09-22 06:30:00')
    ORDER BY  `stories`.`id` ASC
    LIMIT  1000;

+------+-------------+---------+-------+-----------------------------+---------+---------+------+--------+----------+-------------+
| id   | select_type | table   | type  | possible_keys               | key     | key_len | ref  | rows   | filtered | Extra       |
+------+-------------+---------+-------+-----------------------------+---------+---------+------+--------+----------+-------------+
|    1 | SIMPLE      | stories | index | index_stories_on_updated_at | PRIMARY | 8       | NULL | 240259 |    83.81 | Using where |
+------+-------------+---------+-------+-----------------------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Causing it to walk through the whole primary key index (I guess sequentially) to then filter on updated_at field taking several hours to complete instead.

The query was created by the ORM ActiveRecord and is probably far from ideal, as a workaround solution we came up with a couple of manually crafted queries, that moves the ORDER BY stories.id out, and/or using use/force index to avoid the PK since we are really filtering our data set by updated_at.

What I'm interested in understanding here is how/why the query optimizer chooses that execution plan, I understand that query optimizer uses index/table statistics for taking that decision but in this case, and If my understanding is correct about how innodb works, this is pretty clear that walking through a huge PK while not using any PK id for filtering is not ideal.

I'm essentially trying to understand where that "bad" decision comes from, what statistics or unknowns variables are used not to end up with the good plan (the one that is often chosen and which is orders of magnitude faster)

feel free to correct any of my assumptions as I'm definitively not a DBA expert,

Thanks in advance!


texts: DynamoDB: How to model data that's shared and queried by all users?

texts:
I am very intrigued by DynamoDB, and it works incredibly well when I model the data for my main use case for my application. That being said, there is one specific use case that I can't wrap my head around.

Let's say I have users in a table, with the user id being the primary key. Most information is specific to the user. I want to be able to communicate with my users so I want the ability to make announcements to them. These announcements are shared across all the users. I can store user specific information about announcements in their own attributes like read and unread announcements.

The problem (if it's not clear already) is that there is only one set of announcements but they will be queried by every user frequently, leading to an anti-pattern of DynamoDB and potential throttling.

My initial thoughts are to make k copies of announcements and label the keys announcement_copy_1, announcement_copy_2 ... announcement_copy_k, and then on the query to check for new announcements, I would randomly assign an integer 1-k to query the announcements. Each announcement copy would be the partition key and I would have sort keys with the date of the specific announcement, and attributes with the text and type of announcement.

I'm not confident if this is the best approach to this problem, or if I'm missing something. Also I am looking at going serverless with AWS Lambdas if that affects anything.

Thank you in advance for any suggestions or advice!


texts: Grant permissions to only specific tables in a Schema

texts:
I am having a schema where I wanted to grant permissions in the following manner. What is the right way of doing this? To keep this simple, I am putting an example.
SCHEMA - HR
TABLES:

GEO_EMPLOYEE
GEO_SALARY
REGION_EMPLOYEE
REGION_SALARY

Now, I have two roles HR_EXEC and HR_GEN. The HR_EXEC role will have access to the entire schema which I kind of grant at the schema level. But for the HR_GEN role, I would like to inherit from HR_EXEC role and at the same time have access to schema and all the tables except denied to 'GEO_' tables. How do I do it in a better way? Should I be creating some DENY statements for each 'GEO_' tables in this case? I have 100 tables like this.
I am looking specifically for a SnowSQL based approach here but I guess this concept would be same for any SQL environment.


texts: Microsoft SQL Server 2008 R2 Subscriptions

texts:
I have an installation of SQL Server 2008 R2.

In the subscriptions section I do not see an icon or button to create data driven subscriptions; in the previous installation of SQL Server 2008, I do see this option.

Can anyone tell me where I can create data driven subscriptions?


texts: Can you Mirror an Microsoft SQL compact Edition database

texts: We have a PLC inputting summary data into a Microsoft Compact SQlce Edition database. We would like to run queries to on this data, but are unable due to the type of database it is, compact. It, as far as I understand, exposes nothing to the network so we're unable to connect with ODBC connection, or anything of that sort. The company that put it in suggested a database mirror to an existing SQL server database we already have. Which seems plausible except for the extra overhead, which might cause some latency issues. Is it possible to Do a database mirror with the SQLce as the principle database? I have seen nothing on-line that makes me think this can be done, in fact I've seen hints that it can't. I am not set on Database mirror we just need a way to get the information off this device.


texts:
PostgreSQL 9.2

I'm trying to understand the difference between Hash Semi Join and just Hash Join.

Here're two queries:

I

EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT
customerid FROM customers WHERE state='MD');

Hash Semi Join  (cost=740.34..994.61 rows=249 width=30) (actual time=2.684..4.520 rows=120 loops=1)
  Hash Cond: (orders.customerid = customers.customerid)
  ->  Seq Scan on orders  (cost=0.00..220.00 rows=12000 width=30) (actual time=0.004..0.743 rows=12000 loops=1)
  ->  Hash  (cost=738.00..738.00 rows=187 width=4) (actual time=2.664..2.664 rows=187 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 7kB
        ->  Seq Scan on customers  (cost=0.00..738.00 rows=187 width=4) (actual time=0.018..2.638 rows=187 loops=1)
              Filter: ((state)::text = 'MD'::text)
              Rows Removed by Filter: 19813

II

EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customerid = c.customerid WHERE c.state = 'MD'

Hash Join  (cost=740.34..1006.46 rows=112 width=298) (actual time=2.831..4.762 rows=120 loops=1)
  Hash Cond: (o.customerid = c.customerid)
  ->  Seq Scan on orders o  (cost=0.00..220.00 rows=12000 width=30) (actual time=0.004..0.768 rows=12000 loops=1)
  ->  Hash  (cost=738.00..738.00 rows=187 width=268) (actual time=2.807..2.807 rows=187 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 37kB
        ->  Seq Scan on customers c  (cost=0.00..738.00 rows=187 width=268) (actual time=0.018..2.777 rows=187 loops=1)
              Filter: ((state)::text = 'MD'::text)
              Rows Removed by Filter: 19813

As can be seen, the only difference in the plans is that in the first case, the hastable consumes 7kB, but in the second 37kB and that the node is Hash Semi Join.

But I don't understand the difference in the hashtable size. The Hash node uses perfectly the same Seq Scan node having the same Filter. Why is there the difference?


texts:
I was previously using SQLite for a personal project and due to a constraint of having it available online, I decided to make the switch to MySQL. I converted my database to the MySQL equivalent but I just noticed that performance is VERY poor. This is a 70 mb database with around 600k records total. The query I am running is an INNER JOIN that executes in less than 500 ms using SQLite but the same query using MySQL takes 15 minutes.
SELECT has.tag_id, has.image_id
FROM has
INNER JOIN image ON image.image_id = has.image_id
INNER JOIN person ON person.person_id = image.person_id
WHERE person.name="Random Person"

has table has 80k records
image table has 290k records
person table has 500 records

Here is the structure of the three tables:
create table media.person
(
    person_id int auto_increment
        primary key,
    name         text not null
)
    collate = utf8_unicode_ci;

create table media.image
(
    id           int auto_increment,
    image_id     int  not null,
    person_id int  not null,
    link         text not null,
    checksum     text null,
    constraint id
        unique (id)
)
    collate = utf8_unicode_ci;

alter table media.image
    add primary key (id);

create table media.has
(
    id       int auto_increment
        primary key,
    tag_id   int not null,
    image_id int not null
)
    collate = utf8_unicode_ci;

Note that I added a primary key to the has table because I suspected it might have been the source of the problem, but it isn't and SQLite was doing fine without that primary key.
The database uses the InnoDB engine. Here is the output of the mysql --version command:
mysql Ver 14.14 Distrib 5.7.30

Where could the problem come from? I can understand a small loss of performance because MySQL is heavier than SQLite but certainly not to the point of going from 500 ms to 15 minutes for such a simple query.
EDIT:
I just ran the query again, this time it took 7 minutes. I used slow_query_logs but it doesn't tell me much.
# Time: 2020-07-13T07:18:22.526915Z
# User@Host: media[media] @ localhost [127.0.0.1]  Id:   205
# Query_time: 429.434270  Lock_time: 0.000751 Rows_sent: 501  Rows_examined: 223451
use media;
SET timestamp=1594624702;
/* ApplicationName=DataGrip 2020.1.3 */ SELECT has.tag_id, has.image_id
                                FROM has
                                INNER JOIN image ON image.image_id = has.image_id
                                INNER JOIN person ON person.person_id = image.person_id
                                WHERE person.name="Random Person";


texts:
My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL:

declare @startDate int
select @startDate = 20110501

And I get this error:

declare @startDate int
select @startDate = 20110501
Error at line 1
ORA-06550: line 1, column 9:
PLS-00103: Encountered the symbol "@" when expecting one of the following:

   begin function package pragma procedure subtype type use
   <an identifier> <a double-quoted delimited-identifier> form
   current cursor

How do I declare and use variables in Oracle?


texts:
We are running a few Mysql server on EC2 instances and RDS instances on AWS. From time to time they suddenly spike in CPU, and/or memory usage but when we login into the instances, it's too late and we can't troubleshoot the reason. Sometimes I am fast enough to see problem and run

show full processlist;

but it doesn't show any suspicious activity. slow logs and general logs show 0 info, and the mysqld log on /var/log/mysqld.log hasn't been updated in days, so I can't get any information from there as well.
so I would like to ask:

If I am currently seeing the spike on live, is there anything I can do to see what is exactly causing the problem? (maybe a query stucked, a locked table, or anything else that may happen)
If it's too late, and the spike happened when I was not seeing the monitoring, is there anyway besides the slow/regular logs where I could look?
I am replacing the last sysadmin who knew a lot about DBA on mysql, and I am not that familiar with mysql administration, so if you could recommend something for me to read/watch/listen, I really appreciate that.


texts:
I'm setting up a backup strategy based on WAL files archiving and transmission via rsync.

As

DB activity will propably be very low (less than 100 inserts/updates/deletes per day), at least in the beginning
I'm planning to backup remotely once a day

I wonder if I should decrease WAL file size to decrease storage consumption, because from my understanding Postgres switches to another WAL file and archives the current one once it reaches 16MB size, which in my case could normally take more than one day (maybe more than one week); in this case I would end up loosing the small number of operations happened in between. Alternatively I could force WAL file switch via select pg_switch_xlog() (which in any case I will use to backup once a day) but would end up having lots of 16MB files where probably 1MB files would be more than enough.

Am I missing something? If not how can I reduce WAL file size?

I've also been suggested to use streaming replication but I must use a NAS as my backup device and don't know if it allows such strategy.


texts: How to build a database that contain only the delta from yesterday

texts:
I need to know what has been changed on my database since last night.
Is it possible to extract this data from the LDF file and to build a new Database that contains the delta?

For example, let say I have a table for users and now, a new user was added and one of the users update his home address. I need to be able to build a new database that users table will contain two records
1. The new user (and to add a new column to know if it’s new or update field)
2.  The user that update his record (it will be nice to know which record has been update)?

BTW,
I have to SQL servers that I can use (2008 and 2012)

Thanks In Advance


texts: Forcing uncommitted read access via view?

texts:
I have an application (which I'm only the user of, i.e. I cannot change it, but I do have control over the database) that performs relatively simple, but potentially very frequent concurrent look-ups on a table using prepared statements like the following on a DB2 LUW 10.5 system:

SELECT A, B, C FROM SCHEMA.TABLENAME WHERE D = ?

Particularly it does not seem to apply uncommitted read isolation. There are no updates on the table while the application reads from it and each read usually only returns a single row.

My questions are:

Can I assume there can be performance improvements by adding WITH UR to the statement?
If so: Can I expect similar performance improvements by creating a view like CREATE VIEW VIEWNAME AS SELECT * FROM SCHEMA.TABLENAME WITH UR and point the application at that view instead of the original table?

Or more generally: Can I use a view (or other DB-side facilities) to trick the application into using uncommitted read, provided that results in better performance in my scenario?


texts: SQL Server 2017 log file growth with memory optimized table

texts:
I'm start to using the memory optimized table, the problem that I have is the log file growth also with the database put in simple mode.

On my maintenance schedule I checkpoint and shrink, but still has the issue. If I revert back the tables to normal disk table, everything looks fine.

Did somebody know the reason?

The database has the column LOG_REUSE_WAIT_DESC to 'nothing' in sys.databases.

All the mem opt tables are in SCHEMA_ONLY modality, but still full logging.

SQL Server 2017 version 14.0.3037

I also see these messages in the SQL Server error log:

  spid38s,Unknown,[INFO] Database ID: [5]. Deleting unrecoverable checkpoint table row (id: 669)
  spid102,Unknown,[INFO] HkHostFreezeCkptTrimming(). Database ID: [5]. HkTrimLSN Frozen: 1
  spid102,Unknown,[INFO] getMaxUnrecoverableCheckpointId(). Database ID: [5]. Start of Log LSN: 00001714:000004E0:0002 used to trim unrecoverable checkpoint files tables during full backup

Sizes situation with memory optimized tables:

Sizes situation with disk based table:


texts:
There is something in the MySQL console that drives me nuts. When I hit ctrl+c to cancel the current command being typed, the terminal exits.

In every terminal I know (*nix terminals, Python, PostgreSQL), ctrl+c cancels the current command and ctrl+d exits the terminal.

This issue has been reported at 2003, and bumped several times since.

Is there a way to:

Change this behaviour, or
Convince the MySQL dev team that this is really annoying?


texts: InnoDB Error - InnoDB: Error: trying to remove tablespace 264 from the cache but InnoDB: it is not there

texts:
Im using AWS aurora which is MySQL 5,6 Recently I can see an error like below.

2017-02-14 20:42:34 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 264 from the cache but
InnoDB: it is not there.
2017-02-14 20:42:34 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 264 from the cache but
InnoDB: it is not there.
2017-02-14 20:49:56 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 378 from the cache but
InnoDB: it is not there.
2017-02-14 20:49:56 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 378 from the cache but
InnoDB: it is not there.
2017-02-14 20:50:41 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 361 from the cache but
InnoDB: it is not there.
2017-02-14 20:50:41 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 361 from the cache but
InnoDB: it is not there.
2017-02-14 20:51:10 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 393 from the cache but
InnoDB: it is not there.
2017-02-14 20:51:10 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 393 from the cache but
InnoDB: it is not there.

I don't know why its coming, Can anyone tell what its actually trying to say?


texts:
When I try to install SQL Server Management Studio, Version 17.0, I receive the following error message:

  "The Generally Available (GA) version of SQL Server Management Studio
  (SSMS) v17.0 cannot be installed because a pre-release version of SSMS
  is installed on your computer. Uninstall the pre-release version of
  SSMS from 'Add/Remove Programs' in the Control Panel and run SSMS
  setup again."

I uninstalled the pre-release version, but still have the issue. When I look at the log file, the condition that is causing the problem is

  [1118:18D4][2017-05-09T16:05:55]i052: Condition 'NOT RCBitsInstalled
  OR (RCBitsInstalled > "2016.140.17030.0" OR RCBitsInstalled <
  "2016.140.0.0")' evaluates to false.

But looking into the log file further, that value seems fine.

  [1118:18D4][2017-05-09T16:05:55]i000: Setting version variable
  'RCBitsInstalled' to value '2016.140.16000.64'

Anyone know a way around this? Thanks!


texts: How to run recurring tasks on Postgresql without an external cron-like tool?

texts:
I would like to call a stored procedure on a regular basis. On Oracle, I would create a job for this. I have found that Postgresql can mimic this well by using an external tool (cron etc) and PgAgent.

Do you know of an "internal" alternative which wouldn't involve the external tool ?

I want to avoid security concerns with the password stored on the command line of the pgAgent.
I want to avoid any additional system configuration for hiding the password (~/.pgpass).

Postgresql 8.3
Linux RedHat 64bit


texts: See actual results of SQL UPDATE command

texts: Is there a way to get the actual results of SQL UPDATE command (ie the table rows themselves), not just get the number of rows (other than by converting the UPDATE command to a SELECT)? I'm using mainly Postgres and Informix, but I'd be interested to hear about other database systems that have this feature, or perhaps a particular client that supports this.


texts: How many deadlocks am i really getting? system health or deadlocks/sec?

texts:
I'm trying to look at deadlocks on a troublesome server, and when I look at the system_health xel file, I see 80 records for "xml_deadlock_report" in about 16 hours.  I assume these are all different deadlocks?

Perfmon and system_health don't really seem to match up.

which is more reliable, perfmon deadlocks/sec (hours * 60 * 60) * deadlocks/sec = @ 4000 or the 80 rows in the xml deadlock report? (I'm viewing the system_health xel via enterprise manager 2014)
is my method for the math totally wrong? I realize it is a generalization, but, really...that's a lot.

SQL 2012 Standard Edition
Windows 2012

I'm also using the following from Jon Kehayias
http://www.sqlservercentral.com/articles/deadlock/65658/

but it doesn't give me the time, and although the post is old I imagine it is pulling from the same .xel file as the system_diag so is missing events (as he mentions, if I'm reading his posts correctly)


texts: Find missing entries across several tables by SQL call

texts:
I was already browsing around here and on other platforms but could not find a match to my request. My knowledge of SQL is quite limited and my IT department is not willing to help unfortunately.
I have 7 different tables (‚Sys1, Sys2…Sys7‘), with columns 'name' and 'date'.
An 8. table (‚summary‘) also has columns ‚name‘, ‚date‘ and additionally ‚sys‘. ‚sys‘ can have values ‚Sys1‘ to ‚Sys7‘. I want to compare if entries in tables ‚Sys1-7‘ also appear in table ‚summary‘ and vice versa. The reference for it is values in the column ‚sys‘. 'Name' and 'date' are the same in both tables. However, it is possible that name-date-combinations appear twice in one table, it is also possible that the same name-date-combination appears in two or more ‚Sys‘ tables.
I am finally only interested to know, if there are entries that have NOT been made with name and date in both tables (Sys1-7 AND summary).
An example:
Table ‚Sys1‘:
| Name          | date     |
|---------------|----------|
|Max Mustermann |01.12.2020|

Table ‘Sys2’:
|Name           | date       |
|---------------|------------|
|Hans Wurst     | 01.12.2020 |
|Max Mustermann | 01.12.2020 |
|Donald Trump   | 01.12.2020 |

Table ‘summary’:
| Name          | date       | sys  |
|---------------|------------|------|
|Max Mustermann | 01.12.2020 | Sys1 |
|Hans Wurst     | 01.12.2020 | Sys2 |
|Max Mustermann | 01.12.2020 | Sys2 |
|Joe Biden      | 01.12.2020 | Sys1 |

So here I want to see that Donald Trump did not sign into table 'summary' and Joe Biden did not sign in table 'Sys1'.
Thanks for any help or link to answered questions I might have missed


texts: How to find out the account(s) under which the sql server is running?

texts:
I have just created a proxy and I need to grant access to the proxy to the account that runs the sql server agent. It will probably be a domain account.

How can I find it using T-SQL?


texts:
how to set password expire duration and maximum invalid login attempts for user under Cassandra DB ?

for example user test tried to login with incorrect password 5 times , that should should get blocked or write this info to logs.

password expire duration  how we can configure ?


texts: How to fix double-encoded UTF8 characters in postgres

texts:
I have a dataset (shapefile) with the same problem as the post below:
https://stackoverflow.com/questions/11436594/how-to-fix-double-encoded-utf8-characters-in-an-utf-8-table
"A previous LOAD DATA INFILE was run under the assumption that the CSV file is latin1-encoded. During this import the multibyte characters were interpreted as two single character and then encoded using utf-8 (again).
This double-encoding created anomalies like ÃƒÂ± instead of ñ."
However, the solution given is in mysql and not postgres, i tried it on postgres and it didn't work, just worked on mysql:
UPDATE tablename SET
    field = CONVERT(CAST(CONVERT(field USING latin1) AS BINARY) USING utf8);

I need to import and fix this shapefile using postgres because I will need to use postgis to do various spatial analyzes.
How can I solve this using postgis?


texts: 22046 "Impersonation error" running SQL Server Agent job

texts:
(New to SSIS. None of the similar questions mention this particular impersonation error.)

I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. The credential object is for an AD account. I'm able to log in with the password to other services, but attempting to run the Agent job errors out in the Execute job step. Running the job as SQL Agent service account worked, but it doesn't have access to the filesystem that the SSIS job is supposed to pull data from.

I checked the agent log file and it's this:

[298] SQLServer Error: 22046, Impersonation error. [SQLSTATE 42000]

I checked the Windows security log in Event Viewer:

Failure Information:
Failure Reason:     Unknown user name or bad password.
Status:         0xC000006D
Sub Status:     0xC000006A

I tried it with my (sysadmin) user ID and password in a different credential / proxy and got the same error. I know that account works.

We granted both SQL Agent and this account user rights to logon as batch job, and the agent account permission to impersonate. We also tried making the various accounts Windows admins and sysadmins, but nothing has helped.

This worked right out of the box in our QA domain.

Any suggestions how to get this to work? What domain/local/SQL Server security settings would enable or prevent the agent impersonating a user?

Windows 2016 + SQL Server 2016


texts:
We got a system with a Mysql database and a replication on a slave database.
We got huge uploads, which will do major changes to the master database. We want to keep the slave database the old version before the changes for a certain while.

Is it possible to set up the replication update to a certain time shift?


texts: Why did mongoDB reads from disk into cache suddenly increase?

texts:
This is a mongoDB replica set with 1 primary, 1 secondary and 1 arbiter.

Suddenly the reads from disk into cache and cache page evictions increased 2x-3x and stay elevated since then (all metrics below from primary unless noted otherwise):

Indices, queries, average document size are unchanged.
As far as I understand the theory:

Wired Tiger uses 1.5GB of 4GB RAM for internal cache
Eviction happens because the cache is at or beyond 80% of 1.5GB. (Not because the pages grow beyond the 8MB threshold, because the ops/s and network traffic are unchanged so there's not more data or ops; and not because the pages are too dirty as the evicted pages are unmodified)

I don't understand:

How can this happen so suddenly when all other metrics do not show any significant change?
Why do the metrics not go back to normal during off-peak times?

More metrics

Operations/s:

Index usage

CPU Usage:

Burst Credits CPU were used also before, but Burst Credits Disk did go up slightly due to the increased reads from disk:

Storage size did not increase significantly:

Memory usage:

Environment

hosted on mLab: Dedicated Cluster Standard AWS M2 plan
mongoDB 3.6.12 (WiredTiger)
4GB RAM
60 GB of SSD block storage (General Purpose EBS)

Update Oct 18, charts from secondary instance:

(environment changed on Oct 16, hence metrics go down)


texts: how many 'text' column a table could have in mysql? I have this error “Row size too large (> 8126).”

texts:
I'm working on a application which is kinda complex to explain how it works. But this is the situation I'm in,

------------------------------------------------------------------------------
FIELD1 (TEXT) FIELD2(TEXT) FIELD3(TEXT) .........................FIELD70(TEXT)
------------------------------------------------------------------------------

POSSIBLE DATA SIZE FOR A SINGLE FIELD: around 500 characters.

I have around 20 tables in a single database like this.

I know it's too bad idea to have this much column in a single table. But I have to go with this since It's went too far and can not modify number of columns.

Now I got some error like, "Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline."

WHAT I HAVE TRIED:

I altered the table as ROW_FORMAT=COMPRESSED. = didn't work out
I altered the table as ROW_FORMAT=DYNAMIC. = didn't work out
innodb_file_format = Barracuda = didn't work out
I altered the column to VARCHAR(500) = didn't work out
I altered the column to TEXT,BLOG,MEDIUMBLOG = didn't work out

What could be the actual error? I've seen plenty of the answers for this problem bur they are telling these above methods that I've tried and failed. PLEASE HELP ME TO SOLVE THIS


texts:
A table is given with the columns customer, entry_date, exit_date.
I need a query that shows me each customer and counts up the entry_date in 10 day intervals, but only up to the exit_date.

The entry for a customer who does not yet have an issue date has the entry date is in such format '0001-01-01'.

**Customer**   **Entry_date**    **Exit_date**
  Mike           2019-01-01        2019-01-30
  Henry          2019-01-10        0001-01-01
  Thomas         2019-02-08        0001-01-01

Output:

Customer  entry_10    entry_20    entry_30    Count_interval
Mike      2019-01-10  2019-01-10  2019-01-10  3
Henry     2019-01-20  2019-01-30  2019-02-09  3
Thomas    no_data     no_data     no_data     0

My query below doesn't achieve the result.

Select customer,

case when entry_date + 10 day < curdate() and entry_date < exit_date
     then entry_date + 10 day else 'no_data' end as 'entry_10',
case when entry_date + 20 day < curdate() and entry_date < exit_date
     then entry_date + 20 day else 'no_data' end as 'entry_20',
case when entry_date + 30day < curdate() and entry_date < exit_date
     then entry_date + 30 day else 'no_data' end as 'entry_30',

case
when entry_date + 30 day < curdate() and entry_date < exit_date
     then '3'
when entry_date + 20 day < curdate() and entry_date < exit_date
     then '2'
when entry_date + 10 day < curdate() and entry_date < exit_date
then '1'
else '0' end as 'Count_interval'

from customer_table

How could I improve my query? I appreciate your tips.
Thanks..


texts: Custom Sequence using Trigger & Sequence in Oracle SQL

texts:
I have a trigger which inserts a custom Sequence after every Parent Item is inserted. Now I have a sub-Item which is linked to the Parent Item through Parent Item Id (Sequence and Parent Item Id are 2 different column )and sub items can be more than 1. My trigger for Parent Item generates Sequence as "P2505" and I want my sub-Items Sequence should be "P2505-1", for 2nd Sub-Item it should be "P2505-2" and so on.

If the next parent Item has Sequence "P2506" then sub Item Sequence should be  "P2506-1" and so on.

This is my trigger for generating Parent Item's Sequence

create or replace trigger REF_NO_TRIGGER
    before insert on LOST_ITEM
    for each row
declare
    PREFIX varchar2(254);
begin
    select NAME into PREFIX
        from SUBCAT
        where SUBCAT.ID = :new.SUBCAT_ID;
    :new.SEQUENCE := substr(PREFIX,1,1)
      ||(:new.CATEGORY_ID)
      ||(:new.SUBCAT_ID)
      ||(:new.ID);
    end;

Parent Item Table

ID  NUMBER(15,0)
CATEGORY_ID NUMBER(3,0)
SUBCAT_ID    NUMBER(5,0)
NAME VARCHAR2(20)
SEQUENCE  VARCHAR2(22 BYTE)

Sub Item Table

ID  NUMBER(30,0)
PARENT_ITEM_ID  NUMBER(15,0)
NAME VARCHAR2(20)
SEQUENCE    VARCHAR2(22 BYTE)

CREATE TABLE FOR PARENT AND SUB ITEM TABLE

CREATE TABLE PARENT_ITEM(ID NUMBER(15) GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
CATEGORY_ID NUMBER(3) NOT NULL,
SUBCAT_ID NUMBER(5) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
SEQUENCE  VARCHAR2(22),
PRIMARY KEY (ID)
);

CREATE TABLE SUB_ITEM(ID NUMBER(30) GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
PARENT_ITEM_ID NUMBER(15) NOT NULL,
NAME VARCHAR2(20),
 SEQUENCE VARCHAR2(22 BYTE),
CONSTRAINT FK_LOST_IT_SUB_LOST_IT FOREIGN KEY (PARENT_ITEM_ID) REFERENCES PARENT_ITEM(ID),
PRIMARY KEY (ID)
);

INSERT INTO PARENT_ITEM(CATEGORY_ID, SUBCAT_ID, NAME) VALUES (2,5,'MAIN_ITEM');

The ID IS GENERATED automatically and Sequence for Parent Item is generated through Trigger.
For Sub Item I am only inserting Name and Parent Item ID and I need a Sequence for that.

As per my understanding, I need to check for the parent Item Sequence in Sub-Item table and see if there is any entry , if there is no entry with that sequence then the first sub item should be "ParentItemSequence-1" and then again count and append "-2", "-3" and so on

But I don't know how to achieve this functionality through a trigger because I have to submit all items including Parent and Sub Item at the same time.


texts: How to find the number of transactions per day or per hour in a SQL 2000 database

texts:
I am trying to find a query that I can run in order to find the number of transactions per day or per hour in a SQL 2000 database. The purpose is to get the number of hits on the database.

I have tried something similar to the query below but this seems to belong to newer versions of SQL Server not SQL server 2000.

 DECLARE @ctr bigint
 SELECT @ctr = cntr_value
 FROM sys.dm_os_performance_counters
 WHERE counter_name = 'transactions/sec'
 AND object_name = 'SQLServer:Databases'
 AND instance_name = 'SomeDBName'
 WAITFOR DELAY '00:00:01'
 SELECT cntr_value - @ctr
 FROM sys.dm_os_performance_counters
 WHERE counter_name = 'transactions/sec'
 AND object_name = 'SQLServer:Databases'
 AND instance_name = 'SomeDBName'


texts: Select query taking too much time in postgresql

texts:
I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU):

create table stat_data (
    name varchar NOT NULL,
    the_date date NOT NULL,
    uopen numeric,
    uhigh numeric,
    ulow numeric,
    uclose numeric,
    uvolume numeric,
    dividends numeric,
    splits numeric,
    aopen numeric,
    ahigh numeric,
    alow numeric,
    aclose numeric,
    avolume numeric,
    PRIMARY KEY(name, the_date)
);

It contains 3.2 mln rows.
Then I'm running the following script:

SELECT name, the_date, uclose, uvolume from stat_data;

Actual time spent to run it was: 6 seconds.

explain (analyze, buffers) on the same query gives:

Seq Scan on stat_data  (cost=0.00..82578.57 rows=3206057 width=20) (actual time=0.100..647.835 rows=3206057 loops=1)
  Buffers: shared hit=481 read=50037
Planning time: 0.061 ms
Execution time: 829.982 ms
(4 rows)

I also created two indexes for the_date and name. Showing list of indexes below:

CREATE INDEX stat_data_name_idx ON public.stat_data USING btree (name text_pattern_ops)
CREATE UNIQUE INDEX stat_data_pkey ON public.stat_data USING btree (name, the_date)
CREATE INDEX stat_data_the_date_idx ON public.stat_data USING btree (the_date)

Then running query:

SELECT name, the_date, uclose, uvolume
from stat_data
where the_date >= '2018-01-01'
order by name asc, the_date desc;

Explain shows:

explain (analyze , buffers) SELECT name, the_date, uclose, uvolume from stat_data where the_date >= '2018-01-01' order by name asc, the_date desc;

Sort  (cost=437046.14..445061.28 rows=3206057 width=20) (actual time=7227.767..7561.816 rows=3206057 loops=1)
   Sort Key: name, the_date DESC
   Sort Method: quicksort  Memory: 348755kB
   Buffers: shared hit=513 read=50005
   ->  Seq Scan on stat_data  (cost=0.00..90593.71 rows=3206057 width=20) (actual time=0.084..829.024 rows=3206057 loops=1)
         Filter: (the_date >= '2018-01-01'::date)
         Buffers: shared hit=513 read=50005
 Planning time: 0.141 ms
 Execution time: 7746.269 ms

Running this query without explain returns result after 12 seconds.

I tried increasing work_mem and shared_buffers, didn't help at all.
Also tried desc/asc indexes. Max I could get with them is 8 seconds. Which is still bad.

Maybe I am missing something here? Any serious mistakes?

EDIT 1:

Increased to 50% RAM of shared_buffers param (2GB) and got this:

Sort  (cost=438066.43..446099.34 rows=3213165 width=26) (actual time=7227.034..7562.172 rows=3214320 loops=1)
   Sort Key: name, the_date DESC
   Sort Method: quicksort  Memory: 349423kB
   Buffers: shared hit=50636
   ->  Seq Scan on stat_data  (cost=0.00..90794.56 rows=3213165 width=26) (actual time=0.010..908.545 rows=3214320 loops=1)
         Filter: (the_date >= '2018-01-01'::date)
         Buffers: shared hit=50630
 Planning time: 1.329 ms
 Execution time: 7748.418 ms

Seems like no more read (shared hits only). But still slow. Maybe CPU usage is 100%? Possible to reduce that?


texts: Export all tables in database using SQLCMD

texts:
I'm looking to export all tables from a SQL Server Standard Edition database with data into CSV files onto a remote linux server. I setup FreeTDS and am able to connect, do SQLCMD or bcp successfully.

I know we can use SQLCMD but I was wondering if there is a way that we can write each table into its individual CSV file using a single connection instead of looping through every table in the database and writing to a CSV file.

Any thoughts if its possible?

Thanks in advance,
simak


texts:
I want to select the name and date of birth of all living artists who have value more than 50. My tables are artist and picture and I wrote this SQL statement but it is not giving me the desired result. Please advise.

SELECT name, dateOfBirth
FROM artist
    JOIN exhibitions.picture
        ON exhibitions.picture.name
WHERE exhibitions.picture.value = '50'
    AND artist.dayOfDeath is NULL;


texts: Privilege to set default database in MySQL

texts:
It turns out that a user cannot set default database if it does not have global (mysql.user) or any database-level permissions (mysql.db):

MariaDB [(none)]> use mysql;
ERROR 1044 (42000): Access denied for user 'XXX'@'%' to database 'mysql'

However, using default database could be convenient, e. i. there is a lot old scripts relying on that.

The only workaround I've found so far, is to grant SHOW VIEW on the database: I do not want to grant even read-only access for all tables.

What do I miss? I don't know any SQL syntax that allows insert 'N' in all relevant columns in mysql.db, and don't want to do it with raw SQL (INSERT ...).


texts: Generate English sentence and omitting zeros

texts:
I try to generate an English sentence from two columns. My aim is to convert the table
| a | b |
|---|---|
| 0 | 1 |
| 2 | 3 |
| 4 | 0 |

into the following list
1 B
2 A and 3 B
4 A

I would like to know if there is a less verbose method than the following:
with tmp(a, b) as (
    values
        (0, 1),
        (2, 3),
        (4, 0)
)
select
    case
        when tmp.a > 0 and tmp.b > 0 then
            tmp.a || ' A and ' || tmp.b || ' B'
        else
            case
                when tmp.a > 0 then
                    tmp.a || ' A'
                else
                    tmp.b || ' B'
            end
    end
from tmp


texts: "Site Offline" MySQL server failing to start and stop

texts:
I'm hosting a few sites on linode and have encountered a strange problem. For the last two years the site has been running perfectly fine, and now randomly all the websites on the server go to the "site offline check the settings.php" page. No changes have been made to the website at all recently.

when i try go do mysqld stop it says busy for a while then finally says failed, while doing start results in an instant fail.

Many of the threads I googled suggest the hostname is off in the settings.php but since the website has been up for two years I don't think this can be the case.

I haven't had to troubleshoot mySQL before, but if i remember correctly the log file I should be looking at is Hostname.err. In there, it has a large chain of errors concerning InnoDB. The one listed as fatal error says:

InnoDB: Fatal error: cannot allocate memory for the buffer pool

I would appreciate any suggestions, and if there are log files that would help let me know.

Edit: Requested information.
1) When I examine the CNF, it appears that all lines involving innoDB are commented with #. This means that it has always been this way as I have not modified it.

2)mysql  Ver 14.14 Distrib 5.5.14, for Linux (i686) using readline 5.1

3) It looks like there is a giant jump in the IO rate every time I try to start the database / before the websites die. How do i go about clearing those cache tables if they have become too unmanageable and is there any risk of losing anything?

Third Edit (Now with datadir):

How much RAM is on the VM ? 512MB

How much space does datadir have ?

Size: 20G  Used: 19G   Available  0 Use Percent: 100%

What is the size of ibdata1 ( ls -lh /var/lib/mysql/ibdata1)? 114M

FYI: If anyone looks here with the same problem in the future, My issue was the the binary log files had consumed my entire disk space. See here http://systembash.com/content/mysql-binary-log-file-size-huge/


texts: Make sure date of departure is different than date of arrival?

texts:
I have the following tables:

Flights (id_flight)
Airports (id_airport)
Departures_Arrivals (id_flight, id_airport), which is the Junction Table between Flights and Airports and it has the following attributes:

gate
date
type, which can be either 0 or 1, where 0 stands for departure and 1 stands for arrival.

Is there any kind of constraint to make sure that the date of the arrival is greater than the date of the departure?


texts: On a dedicated database server, how much memory to reserve for the OS?

texts:
Assuming you have a dedicated server explicitly for database functions -- how much memory should you reserve for the operating system?

I realize this will probably vary somewhat depending on the specific OS, the specific database software, and so forth. But, as memory is so important to database performance, I want the database to have the maximum reasonable level of memory, without starving the host OS.

So

what's a good rule of thumb to start with?
what counters or performance indicators should we look at to determine if we've gone too far and the host OS is being starved somehow by the database?


texts: Slow CREATE table from subquery using SELECT .. INNER JOIN

texts:
This query takes ~90 seconds:

CREATE TABLE filtered
(
    SELECT DISTINCT
    f2.cityCode,
    f2.productCode,
    f2.productDesc,
    f2.inventoryDate,
    f2.inventoryLevel
    FROM
    etm
    INNER JOIN f2avail AS f2 ON
    etm.cityCode = f2.cityCode AND
    etm.tvlDate = f2.inventoryDate
);

but the subquery on its own takes ~0.07 seconds:

-- CREATE TABLE filtered
-- (
    SELECT DISTINCT
    f2.cityCode,
    f2.productCode,
    f2.productDesc,
    f2.inventoryDate,
    f2.inventoryLevel
    FROM
    etm
    INNER JOIN f2avail AS f2 ON
    etm.cityCode = f2.cityCode AND
    etm.tvlDate = f2.inventoryDate
-- );

Why is that?

Following this solution, I ran OPTIMIZE TABLE f2avail (the one with ~246,000 rows) and the create query ran about 25% faster, but still slow.

How can I speed up saving the subquery results? This is just one step in a series of filtering stages in an internal DB for reporting. Both tables are InnoDB.

Subquery explain results:

Update

The solution was to use composite indices on both tables.

-- Add the composite indices
ALTER TABLE `etm` ADD INDEX (`cityCode`, `tvlDate`);
ALTER TABLE `f2avail` ADD INDEX (`cityCode`, `inventoryDate`);

The query went from ~90s to about 6s.


texts: MySQL - grant all privileges on *.* brings back Access denied

texts:
GRANT ALL PRIVILEGES ON *.* TO  'username'@'localhost'  IDENTIFIED  BY  'somepassword';

Brings back error :

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

If I give a database name it works.

GRANT ALL PRIVILEGES ON dbname.* TO  'username'@'localhost'  IDENTIFIED  BY  'somepassword';

I did select * from user where user ='root'; and it has Y under every *_priv column.

I'm running MySQL Server version: 5.5.38. Does anyone know why this is happening?


texts: Indexes not appearing in sys tables on Azure Secondary

texts:
I'm running the following query on an AZure SQL Read Only secondary to determine which indexes are being used. But some of the indexes are not being returned in the query.  I must be missing something obvious but I can't see what part of the statement is filtering my results !

SELECT
  objects.name AS Table_name,
  indexes.name AS Index_name,
  dm_db_index_usage_stats.user_seeks,
  dm_db_index_usage_stats.user_scans,
  dm_db_index_usage_stats.user_updates
FROM
  sys.dm_db_index_usage_stats
INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID =
  objects.OBJECT_ID
INNER JOIN sys.indexes ON indexes.index_id =
  dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID =
indexes.OBJECT_ID
WHERE objects.name = 'BookingDiary'
ORDER BY
  objects.name , dm_db_index_usage_stats.user_updates DESC


texts: Is it possible to use a temp table in a data flow source?

texts:
I have a data flow in a 2012 SSIS package where I'm trying to use a temp table as the data source.  I'm using an OLE DB data source with a SQL Command Data access mode.

My code looks like this:

CREATE TABLE #Checksums (DBName sysname, CheckSum bigint)
-- Code that loads the Checksum table here
SELECT DBName, CheckSum FROM #Checksums

In order to get the data source to pull the list of columns I temporarily put the following:

SELECT CAST(NULL AS sysname) DBName, CAST(NULL as bigint) CheckSum

That got the columns created and I was able to map to my destination.

I have ValidateExternalMetadata set to false on the data source and DelayValidation set to true on the data flow.

My code works correctly in SSMS however every time I run the package it errors out with the following:

[OLE DB Source [39]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Invalid object name '#qtemp'.".
[OLE DB Source [39]] Error: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
[SSIS.Pipeline] Error: OLE DB Source failed the pre-execute phase and returned error code 0xC020204A.

Is it possible to use a temp table like this in a data source?  If so is there a trick I'm missing?


texts: MySQL - Counting multiple parameters from the same subquery

texts:
I have a very large MySQL (~10M rows) database, currently using the InnoDB engine, which stores log events. I am working on a front-end that allows searching through this log database, and one of the features of this front-end is the ability to filter by certain values of the event. For the visual, the filter is a select-multiple element that lists both the possible values and the number of events currently matching that value:

However the queries I'm using to count these values are causing a significant performance hit. Part of the issue I'm having is I currently have 7 different parameters I allow filtering on, as well as more general filters (e.g. time window), so for each of these 7 parameters I'm running something like:
SELECT parameter1, count(parameter1) FROM table WHERE log_time > 'Some Date' AND parameter2 = 'Something' AND parameter3 = ... GROUP BY parameter1
...where parameter 2, 3, etc. are other conditions that have already been set. So I'm applying the same where condition to all 7 count queries, which seems redundant to me. I know I can reformat this to use a subquery instead of the where condition, but is it possible to then count all 7 filters off the same subquery?
Another idea I had was to create a temporary table which gets populated with the results of the subquery, and is then interrogated for the 7 filter counts. In testing this did overall save some time, but seems like overkill to me (especially from a disk IOP perspective).
I also tried switching the table to MyISAM, since count() operations are supposed to be faster there, but didn't notice a significant difference in query time. I'm guessing this is because I'm using where conditions that force full table scans anyway.
== Edit 1 ==
Here is the table structure of one of my test tables (condensed version of the actual table, only including values that are filtered on):
Create Table: CREATE TABLE `temp_3` (
  `client` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `record_type` tinyint DEFAULT NULL,
  `creation_time` datetime DEFAULT NULL,
  `operation` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `result_status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_ip` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source_country_code` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source_organization` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  KEY `idx_creation_time` (`creation_time`),
  KEY `idx_client` (`client`),
  KEY `idx_record_type` (`record_type`),
  KEY `idx_operation` (`operation`),
  KEY `idx_result_status` (`result_status`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_client_ip` (`client_ip`),
  KEY `idx_source_country_code` (`source_country_code`),
  KEY `idx_source_organization` (`source_organization`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I was testing with and without index on all the counted values, they help but not massively (maybe 20-30% faster with indexes). I have not tried any composite indexes yet.


texts: High delay lag between master/slave in Postgres 9.3

texts:
I have a Postgres 9.3 database server with this configuration:

# grep archive_ postgresql.conf
archive_mode = on            # allows archiving to be done
archive_command = 'rsync -a %p postgres@backupserver:9.3/standby/archive/%f'            # command to use to archive a logfile segment
archive_timeout = 300            # force a logfile segment switch after this

and another server that follows in hot standby mode.

I expect a WAL packet to be sent when it becomes full or 5 minutes (300 seconds).

If I run this query in the hot standby:

SELECT CASE
WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END

Then I get a mean value of about 1500 seconds, that is about 5 times bigger than the configured 300 seconds.

Why?

In other servers with a lot of traffic the delay is small.

Related questions:
- When will PostgreSQL execute archive_command to archive wal files?


texts: Understanding when to remove Order By or Sort operator from Query/plan

texts:
While i was reading and per understanding one needs to avoid unwanted ORDER BY in SQL queries if matching index to support query has key columns sorted the same way.

For below DB test schema--

CREATE PARTITION FUNCTION DemoPartitionFunction (datetime)
AS RANGE RIGHT
FOR VALUES (DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -7),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -6),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -5),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -4),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -3),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -2),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -1),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 0),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 1),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 2),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 3),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 4),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 5),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 6),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 7));

CREATE PARTITION SCHEME DemoPartitionScheme
AS PARTITION DemoPartitionFunction
ALL TO ([DEFAULT]);

CREATE TABLE [dbo].[DemoPartitionedTable](
    [DemoID] [int] IDENTITY(1,1) NOT NULL,
    [SomeData] [sysname] NOT NULL,
    [Lastseen] [datetime] NULL,
    [DataKey1] [char] NOT NULL,
    [DataKey2] [char] NOT NULL,
    [RandomColumn] [nvarchar] NOT NULL,
    [CaptureDate] [datetime] NULL,
    CONSTRAINT [PK_DemoPartitionedTable] UNIQUE NONCLUSTERED
    (
        [DemoID] ASC,
        [CaptureDate] ASC
    )
    ON DemoPartitionScheme(CaptureDate)
) ON DemoPartitionScheme(CaptureDate);

If i run the Query with ORDER BY

SELECT [DemoID], [CaptureDate]
FROM [dbo].[DemoPartitionedTable]
WHERE CaptureDate>=CONVERT(datetime, '20190912', 112) AND
CaptureDate < CONVERT(datetime, '20191013', 112)
ORDER BY
DemoID,
CaptureDate

Below are the stats from IO and Time

  (95703 rows affected) Table 'Worktable'. Scan count 0, logical reads
  0, physical reads 0, read-ahead reads 117, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table
  'DemoPartitionedTable'. Scan count 16, logical reads 330, physical
  reads 9, read-ahead reads 262, lob logical reads 0, lob physical reads
  0, lob read-ahead reads 0.

  (1 row affected)

  SQL Server Execution Times:    CPU time = 297 ms,  elapsed time = 750
  ms.

As per my understanding in above scenario we do not need order by as index have those columns already sorted, so why do i see a bad plan for below

so if i run removing the sorting above

SELECT [DemoID], [CaptureDate]
FROM
[dbo].[DemoPartitionedTable]
WHERE CaptureDate>=CONVERT(datetime, '20190912', 112) AND
CaptureDate < CONVERT(datetime, '20191013', 112)
--ORDER BY
--DemoID,
--CaptureDate

  (95703 rows affected) Table 'DemoPartitionedTable'. Scan count 16,
  logical reads 330, physical reads 134, read-ahead reads 269, lob
  logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row affected)

  SQL Server Execution Times:    CPU time = 78 ms,  elapsed time = 2791
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Plan does not have an expensive sort but stats got worst, why?


texts: How to get readonly access to mysql files via g+w permission 750 or 640?

texts:
The data-dir itself has permissions 700 thereby limiting access to the mysql user.

I want other folks who I add to the mysql group to be able to read binlogs and download the *.frm, *.MYI, *.MYD files to their local computer.

My first thought is to run chmod g+rx data-dir.

The database directories and table/binlog files within data-dir have permissions with 770 / 660. So I have to chmod g-w on all of those to prevent accidental tampering or removal of live data.

Unfortunately, as new binlogs are created, and the create database or create table command is used, the g+w permission is automatically added. How can I change this functionality?


texts: mysql: counting number of tickets which are open per day basis

texts:
I have a table tabName with the following rows:

id(PK) .. Created(datetime)     Resolved(datetime) Status(new/resolved/open)
1          2015-05-10 00:00:00   1970-01-01 ..       open
2          2015-05-10 ..         1970-01-01 ..       new
3          2015-05-10            2015-05-12          resolved
4          2015-05-11 ..         1970-01-01 ..       open
5          2015-05-11 ..         1970-01-01 ..       new
6          2015-05-11            2015-05-11          resolved
7          2015-05-12 ..         1970-01-01 ..       open
8          2015-05-12 ..         1970-01-01 ..       new
9          2015-05-12            2015-05-12          resolved

NOTE:  1970-01-01 .. is default time for all the tickets which are still open.

I have to frame a query to get the count of tickets still open, date wise. Say like on 10 May I have 3 tickets opened. Noting that for id = 3, resolved date is not 10 but it's closed on 12 May.

I framed a query like

SELECT DATE_FORMAT(Created, '%d-%m') AS createdd, COUNT(1)
FROM tabName
WHERE DATE_FORMAT(Resolved, '%d-%m') > DATE_FORMAT(Created, '%d-%m')
      AND YEAR(Created) = '2015'
GROUP BY createdd;

The above query gets all the tickets whose Resolved date is greater than the date which is considered for the count. This may count newer tickets as well. And also if the older tickets which are not resolved yet is not counted.

Is there any other way to frame the required query? I have been juggling with this query from last 3 days. Not able to find out still.

Updated:
As suggested by Abhik, i have tried in sqlfiddle site.

Here is what i created:

CREATE TABLE test
   (idint,createddatetime,resolveddatetime, status varchar(15), PRIMARY KEY (id));
   INSERT INTO test
      (id,'created','resolved',status`)
VALUES
      (1,'2015-05-10 00:00:00','1970-01-01 00:00:00','open'),

  (2,'2015-05-10 00:00:00','1970-01-01 00:00:00','new'),

  (3,'2015-05-10 00:00:00','2015-05-12 00:00:00','resolved'),

  (4,'2015-05-11 00:00:00','1970-01-01 00:00:00','open'),

  (5,'2015-05-11 00:00:00','1970-01-01 00:00:00','new'),

  (6,'2015-05-11 00:00:00','2015-05-11 00:00:00','resolved'),

  (7,'2015-05-12 00:00:00','1970-01-01 00:00:00','open'),

  (8,'2015-05-12 00:00:00','1970-01-01 00:00:00','new'),

  (9,'2015-05-12 00:00:00','1970-01-01 00:00:00','open')

  ;
  `

When i run

`
select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select
 date_format(created,'%d-%m') as created_date ,

sum( case when status='open' then 1 else 0 end )+

sum( case when status='new' then 1 else 0 end )+

sum( case when status='resolved' and ( date(resolved) > date(created) or

date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,

sum(case when status = 'resolved' and date(resolved) = date(created) then 1

else 0 end) as resolved

from test

where

YEAR(Created) = '2015'

group by created_date
)x,

(select @open:=0)r order by created_date ; `

I got following output

created_date   open
10-05   3
11-05   5
12-05   8

Here count of still open tickets for 12-05 must be 7. The above query includes ticket id "3" , which is closed on 12-05. So is there any way to exclude this? If i am able to get this right, then i will have full fledged query.


texts: Resource Governor IOPs counters - what are they counting?

texts:
When profiling a resource group that is known to be write-heavy, I am seeing a number of the "IOps write" counters remain at zero or increment unexpectedly. specifically, the following counters...

select
    write_io_completed_total,
    write_io_stall_total_ms,
    write_bytes_total
from sys.dm_resource_governor_resource_pools;

It's worth noting that there appears to be a 1:1 correlation between dm_resource_governor_resource_pools and dm_os_performance_counters as below for some of these.

+-------------------------------------+------------------------------+
| dm_resource_governor_resource_pools | dm_os_performance_counters   |
+-------------------------------------+------------------------------+
| write_io_completed_total            | Avg Disk Write IO (ms) Base  |
+-------------------------------------+------------------------------+
| write_io_stall_total_ms             | Avg Disk Write IO (ms)       |
+-------------------------------------+------------------------------+

The erratic behavior is reproducible via the below script...

Behavior tested on 2017 (RTM) & 2016 (SP1 CU5)
Repro scripts assume you are running on a sandbox instance - or at least one where you can trash the settings without worry.

Setup

use [master]
go
create resource pool demo_pool;
create workload group demo_group using demo_pool;
create login rg_login_sa with password='Plz_Halp_Me_StackExchange';
alter server role sysadmin add member rg_login_sa;
go
create or alter function dbo.GetResourceGroup()
returns sysname
with schemabinding
as
begin;
    declare @rg sysname = 'default';
    if original_login() = 'rg_login_sa' set @rg = 'demo_group'
    return @rg;
end;
go
alter resource governor with (classifier_function = dbo.GetResourceGroup);
alter resource governor reconfigure;
alter resource governor reset statistics;

Execution

Opening a session with the login rg_login_sa, execute the below.

use Sandbox
go
create table demoRGtbl (
    id int not null identity primary key,
    words char(4000) default '',
    dt datetime2(0) not null default getutcdate()
);
go
with cte as (
    select i=1
    union all
    select i+1
    from cte
    where i<1000000
)
insert dbo.demoRGtbl(words)
select top(1000)
    convert(varchar(100), newid())
from cte
option (maxrecursion 0);

waitfor delay '00:00:00.2';
go 100

...monitoring the performance counters in a separate session will reveal incrementing values in the internal resource pool...

select name,
       write_bytes_total,
       write_io_completed_total,
       write_io_stall_total_ms,
       read_bytes_total
from   sys.dm_resource_governor_resource_pools;

...while the demo_pool values remain unchanged from zero. Interestingly, the read_bytes_total for the demo_pool will begin to increment.

Attempting from the same session to insert rows to the table from itself show the same behavior as above...

insert dbo.demoRGtbl(words)
select words
from dbo.demoRGtbl drg

... up until about the 100K-1MM row range - at which point the IOps counters begin incrementing to the demo_pool instance.

My Questions

How do I reliable track IOps by Resource Group?
Is this a bug or expected behavior? If expected, what are the parameters in which a operation will log to these counters?

In other words - what on earth am I currently tracking in my production environment by using these counters?

See also: Technet blog post announcing IO resource governance.

Repro Cleanup

alter resource governor with (classifier_function = null)
alter resource governor disable;
go
drop workload group demo_group;
drop resource pool demo_pool;
drop login rg_login_sa;
drop function if exists [master].dbo.GetResourceGroup;
drop table if exists Sandbox.dbo.demoRGtbl;
go


texts: How indexing on a date field that is constantly updated and or omitted affect performance?

texts:
In MongoDB, I have a collection with a date field called last_updated I would like to index. Reason being, I am often querying on a date range of that field in addition to sorting by that field and feel as though I can speed the process up. I would like to add as a side note: this collection does see a lot of writes and updates.

However, I would like to know the implications of doing so. My understanding is that updates are the heaviest transaction in a b-tree index structure and would poorly affect performance. Would anyone mind explaining whether it would or wouldn't and if not why it would be just as normal as indexing on another date field (one that is not updated).


texts:
I am attempting to write a query to show data for two quarters.  I have my query returning accurate data from my calculations, BUT it returns two rows of data instead of just a single row like I need.  TO further show this, see this screenshot

This is my sample DDL

Create Table Test
(
  saledate date
  ,value float
  ,admincharges1 float
  ,admincharges2 float
  ,admincharges3 float
  ,admincharges4 float
  ,admincharges5 float
  ,admincharges6 float
)

Insert Into Test
(saledate, value, admincharges1, admincharges2, admincharges3, admincharges4, admincharges5, admincharges6) Values
('01/01/2016', 200, 10, 20, 10, 10, 10, 10)
,('06/01/2016', 400, 20, 20, 20, 20, 20, 20)

And this is the query that I use to try to get the "I want Returned" from my image above.

Select
case when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 1
Then Sum([value])/Sum([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0)))
end As Q1
,case when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 2
Then Sum([value])/Sum([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0)))
end As Q2
FROM Test
Group by Datepart(Year, [saledate]), DATEPART(Quarter, [saledate])

What do I need to alter in my query in order to return my desired output?


texts: What happen if i start a Postgresql replication using tar

texts:
Hi i am following this tutorial every time that i need to set up a binary replication ,in fact i am using the second method called:
"Starting Replication with only a Quick Master Restart"
found here:
https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

That works fine.

In a test environment, i tried to use tar instead of this rsync:

 rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude postgresql.pid \
     data/*  192.168.0.2:/var/lib/postgresql/data/

Like this:

tar -cz data >data.tar.gz

Generating the .tar file with the data and uncompressing that on the slave, how bad can this be for the database?

In the logs, slave shows that it connects sucessfully to the master.


texts: How to get SQL Server Profiler to show amended tables

texts:
The ASP.Net application I'm maintaining at the moment has a lot of activity in the database when ever a postback occurs. For the example I'm currently looking at, Profiler shows a mass of activity just to refresh the UI but I know its also updating a couple of tables.

What would be the best trace filter to only show the sprocs/statements that are either inserting, updating, deleting?

Is there a way to just show the table that are I/U/D (but I doubt this is possible)?

Thanks for your help on this.


texts:
I have a user dbadmin, which has the highest permissions possible in AWS RDS.

Using dbadmin I created a database, foo, owned by fooadmin.

As fooadmin, I can CREATE SCHEMA in that database. As dbadmin, I cannot, I get this error:

create schema ff;
ERROR:  permission denied for database foo

My dbadmin user certainly has access to this database:

grant create on database foo to dbadmin;
WARNING:  no privileges were granted for "foo"
GRANT

This is sort of the inverse of this question, which was asking how to elevate privileges for fooadmin.

update 1 for EvanC:

select user;
 current_user
--------------
 dbadmin
(1 row)

sdm=> create schema ff;
ERROR:  permission denied for database foo

update 2 workaround:

Inspired by this question, if I change the database owner to the dbadmin account, I can at least create schemas as that user. Obviously it means fooadmin can't create schemas anymore. I suppose I could change the owner, create schemas, then change the owner back.


texts: SQL Server Alias won't work for my database instance

texts:
I have setup a SQL Server 2019 instance on my machine which has a computer name in a format like this:

AB-ABC1234ABAB

My SQL instance is named 'SQL2019', therefore I connect to my local SQL instance by supplying the following name:

AB-ABC1234ABAB\SQL2019

I have scripts which rely on server aliases, e.g.

Alias1
Alias2

To set these up, I navigated to the 'Sql Server Configuration Manager' and performed the following steps:

Navigate to 'Protocols for SQL2019' and enabled 'TCP/IP'
Navigated to the 'Aliases' for 'SQL Native Client 11.0 Configuration (32bit)'
Created the alias 'Alias1' pointing to my server name: AB-ABC1234ABAB\SQL2019 and protocol 'tcp'
Repeated above step for alias 2
Repeated above two steps for the 'SQL Native Client 11.0 Configuration' also.
Opened Windows Services and restarted the 'SQL Server (SQL2019)' service.

In SQL Server Management Studio, I have connected to my instance using Windows authentication, then I have created a new login and selected 'SQL Server authentication'. I have then been tested that login by logging in by specifying the computer name as the server (AB-ABC1234ABAB\SQL2019). Then I have disconnected and typed the alias name (e.g. Alias1) in the login box, selected SQL Server authentication and supplied the same login I used earlier.
When I do this, the connection attempt times out. It appears neither alias works for me.
Error I get is the standard one, e.g. "provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)".
What steps can I take to troubleshoot this issue? Could the hyphen in the server name be cause for concern?
I noticed that if I delete the SQL alias I get a slightly different error: "error: 40 - Could not open a connection to SQL Server - the network path was not found."


texts: Why is dropping a primary key expensive?

texts:
I have a table with a clustered PK. No foreign keys reference it. When I drop it I expect it to be more or less instantaneous, but it is instead very slow.

My assumption was that we'd just be removing some metadata and not having a constraint or clustering for future updates and inserts.

What is actually happening internally to make this expensive?


texts: Why is my SSIS package not getting updated?

texts:
I have a Integration Services project within SSDT that has multiple packages. When I right click a single package and choose deploy, SSDT says the operation completed successfully.

However, when I look at the internal.packages table within SSISDB on the target server, it does not contain the latest version that I just deployed. Neither the version_build field or the version_guid field match the values within my package. The SSIS Catalog also displays the incorrect information.

It looks to me as though it is not deploying the package at all - except the internal.projects table is clearly getting updated with a new time stamp on each deployment, and the internal.packages table adds a new row as well.

So, my question is - why is the package not getting updated?


texts: How to Index a JOIN result of two or more tables in order to improve the performance in SQL server?

texts:
I am new to indexing and gone through the basics of indexing. I can find a default clustered index for the primary key constraint within the indexes part of the corresponding table, but after creating a foreign key constraint i can't find any.

Now i've a requirement in which indexing should be implemented to improve performance. I've read about indexing the foreign key in order to improve the performance of a JOIN result.

Do i need to add the foreign key column to an additional non-clustered index or the foreign key has a default indexing?

How can i effectively implement indexing if my SQL table structure is as follows and i've a JOIN query with WHERE clause using t1_col3

               table1                       table2
               ------                       ------
               t1_col1(pk)                  t2_col1(pk)
               t1_col2                      t2_col2
               t1_col3                      t2_col3
               t1_col4                      t2_col4
               t2_col1(FK)


texts:
I have seen people use the comparison of Open_tables and Opened_tables to assess whether the table_cache is too small in MySQL. However, I believe that Opened_tables is cumulative across uptime, so this is not a valid comparison. The only caveat being that perhaps Opened_tables is only bumped on misses - although even then if the tables being opened per second is still small, it's probably not a problem for it to grow gradually.

If comparing Open_tables to Opened_tables is not valid, is there another way to get measured data for this?

This is on MySQL 5.0, but differences between version are welcome too.


texts: Strange SSIS-Package problem ( ASYNC_NETWORK_IO)

texts:
I've read a lot of posts here about this warning. Have been searching on the Internet, but not like this case.

We are having problem with integration services packages.

When I run the query (that shows me the async warning) in Management Studio on the server (that the software is installed), it runs fine.

When I run the query in Management Studio on my pc, connecting to the server, it runs fine.

When we execute the package in design mode (inside the Integration Sql), it runs fine.

But when we run the package with the software, the sp_whoisactive shows me the query and the above information:

  Wait Info: (1735ms)ASYNC_NETWORK_IO ( what I see, it resets every
  2000ms ) Status: Suspended

Why is this only happening inside the software? We can execute the query with no problems.

Query is this:

select
    T1.CodCliente, T3.CodAgrupamento, T5.CodTabulacaoItem,
    T1.Resposta,
    convert(varchar, convert(numeric, T1.Resposta)) as RespostaBuscar,
    T4.Weight, T4.WeightAM
from
    tblRespostaTabula T1 with (nolock)
inner join
    tblCliente T2 with (nolock) on T1.CodCliente = T2.CodCliente
inner join
    tblClienteAgrupamento T3 with (nolock) on T1.CodCliente = T3.CodCliente
inner join
    tblAgrupamento T4 with (nolock) on T3.CodAgrupamento = T4.CodAgrupamento and T2.CodOnda = T4.CodOnda
inner join
    tblTabulacaoItemQuestaoTabula T5 with (nolock) on T1.NrQuestaoTabula = T5.NrQuestaoTabula
inner join
    tblParticipanteTabulacaoItem T6 with (nolock) on T5.CodTabulacaoItem = T6.CodTabulacaoItem and T4.CodOnda = T6.CodOnda and T4.CodParticipante = T6.CodParticipante
where
    T2.CodOnda = @CodOnda
    and T4.CodParticipante = @CodParticipante
    and T3.CodAgrupamento in
        (select CodAgrupamento
         from tblTabulacaoSegmentoAgrupamento with (nolock)
         where CodSegmento = (select CodSegmento
                              from tblSegmento with (nolock)
                              where CodOnda = @CodOnda
                                and CodParticipante = @CodParticipante
                                and FlagTotal = 1))

What I see, also, with sp_who, there is some warnings (AWAITING COMMAND).

There is no deadlocks happening (trace is ok).

select *
from sys.dm_exec_requests

Shows me the SELECT is SUSPENDED. I can't say why is this being suspended. It runs in every environment.

EDIT 1:

With SP-WHO2 ACTIVE I can the that the problem is not the query.

There's a procedure running that query.


texts: MySQL replication - how to read from slaves? manually?

texts:
MySQL Manual - Replication:

  "Scale-out solutions - spreading the load among multiple slaves to
  improve performance. In this environment, all writes and updates must
  take place on the master server. Reads, however, may take place on one
  or more slaves. "

I see how to setup replication which looks relatively simple, but I haven't seen how an application should communicate to slaves.

I assume the application would have to determine which slave to read from? The application will also have to know which server is doing writes? Or is it possible to have the application send all queries to the Master and have reads proxied to slaves?


texts: Dropped Database Connections in SQL Server 2008R2

texts:
We are having an annoying intermittent problem where database connections from our client application on a variety of stations will get dropped.  We have applications that use ODBC, Borland Database Engine, and ADO and all get dropped.  When connections do get dropped, it isn't everyone - some remain connected while others get dropped.  Looking in the logs of one of the applications, I see messages like the following:

TCP Provider: The semaphore timeout period has expired
TCP Provider: An existing connection was forcibly closed by the remote host
Query timeout expired
TCP Provider: No such host is known

I don't see any error messages in the SQL error log or in the Windows events on either the client or the SQL Server.  Is there anyway to determine if this is a database or network issue from the SQL Server side of things?  For example, is there something I can turn on to monitor database connections and get reports when they are dropped?  This happens maybe once or twice a day but not at any specific time (not when backups run or something).

The issue seems to be network related and out of scope for this forum.


texts: MySQL 5.7 (Percona Server) PAM LDAP authentication from Workbench 8.0 on Windows

texts:
I have a server running Percona Server 5.7 and enabled PAM LDAP authentication.
I can connect with LDAP user from:

mysql client
MySQL Workbench 8.0 on Linux (Ubuntu) with dialog.so plugin copied from Percona Server 8.0 distribution.
MySQL Workbench 6.3 on Windows with dialog.dll plugin copied from MariaDB 10.5 distribution to this path: C:\Program Files\MySQL\MySQL Server 5.7\lib\plugin

But, I can not make it work with MySQL Workbench 8.0 on Windows.
With Procmon I can see that the plugin is expected to be found at: C:\Program Files\MySQL\MySQL Server 8.0\lib\plugin, but when I copy the dialog.dll file I get
Error: Authentication plugin 'dialog' can not be loaded: Incompatible client plugin interface
I have tried with dialog.dll file from MariaDB 10.2 - 10.5 distributions, none of them seems to work. I have tried also with the dialog.dll file from HeidiSQL distribution, but that one doesn't work either.
Percona doesn't have distribution for Windows.
Anyone have some solution?