texts: How to use a CASE expression in the WHERE clause?

texts:
I am using a CASE expression in my WHERE clause like this:

SELECT *
FROM    ASPECT.WR_AM_ADT_SUMM
INNER JOIN ASPECT.WR_AM_DLR_DTL
  ON    AS_CNTRY_CD = DD_CNTRY_CD AND
        AS_DLR_CD = DD_DLR_CD AND
        AS_YEAR = DD_YEAR
RIGHT JOIN ASPECT.DEALER_MASTER
  ON    COUNTRY_CD = AS_CNTRY_CD AND
        DEALER_CDE_VEGA = AS_DLR_CD
WHERE
        COUNTRY_CD = '81930' AND
        LANG_CD = '02' AND
        (CASE PARM_ADTR_ID
           WHEN 'ALL' THEN (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%')
           ELSE AS_ADTR_ID_P LIKE LOC_ADTR_ID
        END) AND
        DEALER_CDE_VEGA LIKE '8%'
        ;

In the above query when I pass 'ALL' in PARM_ADTR_ID input parameter then first condition should work, otherwise the second condition should work.

As of now it's giving the syntax error, could anyone please tell me how to do this?


texts: how to retrieve MAX ID from inner join SQL Query?

texts:
I am working on a search stored procedure which, the outcome of the procedure retrieving search rows in increments of 60. The values that this stored procedure is receiving is the search value, optional starting ID, optional ending ID.
The problem I am facing is retrieving the max ID within the search parameters along with retrieving records between the start and end.

My aim is to capture records between start-end (using max ID as marker to determine where to start getting records) for specific search value.

ALTER Procedure [dbo].[searchLog]
@searchValue varchar(150),
@StartLoc INT = NULL,
@EndLoc INT = NULL
AS
Declare @myquote varchar(10)
Declare @wildcard varchar(5)
set @myquote ='"'
set @wildcard ='*'
set @searchValue = @myquote + @searchValue + @wildcard +@myquote

SET NOCOUNT ON;

     IF @StartLoc IS NULL BEGIN
          SET @StartLoc = (SELECT MAX(o.ID), o.ID, o.Name, o.DateRan, s.Name AS Job,
                                  o.Start, o.[End], o.Status, o.Logs
                           FROM dbo.output AS o
                           LEFT OUTER JOIN dbo.jobs AS j
                                ON o.Job = j.ID
                           LEFT OUTER JOIN dbo.scripts AS s
                                ON j.Script = s.ID
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
                                 AND (o.DateRan < GETDATE())) - 60;
     END;

     IF @Endloc IS NULL BEGIN
           SELECT @EndLoc = (SELECT MAX(o.ID), o.ID, o.Name, o.DateRan, s.Name AS Job,
                                  o.Start, o.[End], o.Status, o.Logs
                           FROM dbo.output AS o
                           LEFT OUTER JOIN dbo.jobs AS j
                                ON o.Job = j.ID
                           LEFT OUTER JOIN dbo.scripts AS s
                                ON j.Script = s.ID
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
                                 AND (o.DateRan < GETDATE()));
     END;
BEGIN

SET NOCOUNT ON
SELECT   o.ID, o.Name, o.DateRan, s.Name AS Job, o.Start, o.[End], o.Status, o.Logs
FROM            dbo.output AS o LEFT OUTER JOIN
                         dbo.jobs AS j ON o.Job = j.ID LEFT OUTER JOIN
                         dbo.scripts AS s ON j.Script = s.ID
WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
AND
(o.DateRan < GETDATE())
AND
(o.ID Between @StartLoc and @EndLoc)
     ORDER BY o.ID DESC
END
RETURN


texts: Select a row from one table, if it doesn't exist, select from another table

texts:
How can I select a row from one table, but if it doesn't exist, fall back to selecting it from a secondary table? Here's a dumbed down version of what I'm trying to achieve
create table table_1 (
  person TEXT,
  favourite_number INT
  );

  create table table_2 (
    person TEXT,
    favourite_number INT
  );

  insert into table_1 (person, favourite_number) values
  ('Bob', 1),
  ('Fred', 2)
  ;

    insert into table_2 (person, favourite_number) values
  ('Bob', 30),
  ('Alice', 70)
  ;

I want to get the following result:
| person | favourite_number |
|--------|------------------|
| Bob    | 1                |
| Alice  | 70               |
| Fred   | 2                |

Notice how it picks up Bob and Fred from the first table. Even though Bob appears in the second, because we've already got him we take him from the first table. Alice only appears in the second table.
This is what I've tried so far, but I can't quite get all 3 returning back, please help.
select
  t1.*
from table_1 t1
where t1.person not in (select person from table_2)
union
select
  t2.*
from table_2 t2
where t2.person not in (select person from table_1)
;


texts:
I would like to say that as per my Organization requirement they want to Disable Windows Authentication in SQL Server.

My Environment is as follows:-

1) Microsoft Azure
2) Windows Server 2016
3) Microsoft SQL Server 2014 Standard
4) AD is in office 365 and also synchronize with Exchange Server

As I have gone through some SQL Server blog like How to Disable Windows Authentication in SQL Server but might be I have miss somewhere to disable windows authentication in SQL Server.

Any suggestion or help will be appreciated.


texts: Optimal PostgreSQL setup for Windows 2008 R2

texts: I have PostgreSQL 9.2 setup on Windows 2008 R2 64bit with 96 gigs of RAM and 8 cores. What would the optimal settings be for shared_buffers, effective_cache_size, work_mem, etc.? I realize that these values vary a lot between Linux and Windows so any help for best practices would be greatly appreciated!


texts:
I have a database, when I look for some tables in the database it shows error

ERROR:  could not read block 84 in file "base/16386/670007": Input/output error

I am using postgresql.

Whenever this error is encountered I have to reload the table again and again. But this is not a permanent solution. How I can resolve this error? I have tried to take backup using pg_dump but its also not working.


texts: Return json list of values in SQL Server 2016 using data from 2 columns

texts:
I have a database with 2 columns

A     B
--    --
X     1995
Y     2005
C     1962
D     2003

I'm trying to create a SQL statement that will take a string of comma delimited values and return a json list of values in B where any value in the string is in A

so if the comma delimited string was 'X,C' the json list would be [1995,1962]

I've been using json path to try this, but I can't get it exactly like I want it and I've been spinning my wheels for too long

This is what I've tried:

Select mt.B as json_list_b_values
 From  [dbo].[myTable] mt
Where  mt.A in (Select value From String_Split('X,C', ',')) for json path

This is the ouput:

[ {"json_list_b_values":"1995"}, {"json_list_b_values":"1962"} ]


texts: Is there a specific SQL Server 2008 R2 Permission to run DBCC CHECKIDENT?

texts:
MS Docs (Books online) states the caller must either own the containing schema or be a member of fixed DB roles. (sysadmin, db_owner, db_ddladmin).

Is there a specific permission or set of permissions, that could be granted to allow this in SQL Server 2008 R2?

I have a case where I need to give the permission but with the least amount of privilege and want to avoid creating a stored procedure (with execute as) that allows the specific set of tables they need to perform this against.

They also need TRUNCATE however that's covered with the ALTER permission.


texts: Reference apex_workspace_apex_users or wwv_flow_fnd_user in a foreign key constraint

texts:
What I'm trying to do is to extend authorisation upon APEX basic users to use it as conditions of pages rendering. I would like to create additional tables to store some workspace specific info about users. Is it possible to reference wwv_flow_fnd_user.user_name or apex_workspace_apex_users.user_name as a foreign key constraint? If yes, how to? If no, what's the alternative to bind APEX user info with my own additional info on database level?

I've tried:

CONSTRAINT FK_USER_NAME FOREIGN KEY (USER_NAME) REFERENCES apex_workspace_apex_users (USER_NAME) ON DELETE CASCADE
CONSTRAINT FK_USER_NAME FOREIGN KEY (USER_NAME) REFERENCES wwv_flow_fnd_user (USER_NAME) ON DELETE CASCADE
CONSTRAINT FK_USER_NAME FOREIGN KEY (USER_NAME) REFERENCES APEX_190100.wwv_flow_fnd_user (USER_NAME) ON DELETE CASCADE

All of this both as SYSDBA and as an owning schema of an APEX workspace. All I've got so far is a bunch of errors like insufficient priviliges and table or view does not exist.


texts: Postgres: Allowed connections from any ip but still can't connect from Github Actions

texts:
I'm having some trouble setting my Postgres database to allow connections from all origins, specifically I can't seem to get my API to connect to it while running Github Actions for testing purposes.
I've made sure to add the correct line to pg_hba.conf, this is how it looks at the moment:
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

# Allow all origins:
host    all             all             all                     md5

I've added the last line after searching on Stackoverflow, but I've also tested it with:
host    all             all             0.0.0.0/0                     md5

I've restarted the postgres service and tried to connect.
With that setting I can successfully:

Connect from my local development machine
Connect from my production api server

Unfortunately, as soon as I try testing my api on Github Actions I cannot connect.
After some googling I happened on this github docs page which suggests ips are changed weekly and they can be obtained from quering a specific endpoint. However I have no idea how I could use these to change my Postgres settings on the fly.
I know for sure that it's a problem with Postgres as I've tried to connect my API to a dummy database made with Heroku and that worked flawlessly.
Am I misunderstanding how to allow all origins? Any idea how I can fix this?


texts:
I have a process where user A creates a table and user B tries to create an index on the new table, owned by A. Users A and B are from the same group but user B is an automated process.

Is it possible to create an index from a user that is not the owner of the table, but is in the table's owner group?


texts: financial transactions with and without commissions

texts:
I'm building a gambling website. All financial transactions from and to external sources (credit cards,  money transfer systems) are subject to commission. Amount of the commission depends on the source selected. And moreover, it changes periodically. These external commissions go to the payment systems.

Also some inner transactions are subject to commission. Inner commissions depend on the type of the game user playing. These internal commissions go to gambling website, it is the income.

I'm looking for the best way to store the history of transactions and commissions. Should they be stored in one table TRANSACTIONS (with a connection between payment and commission) or should I have separate table COMMISSIONS.

The purpose of storing is to represent the history of transactions to the user and of course for internal bookkeeping (in order to always know how much have we paid to payment systems and how much have we earned ourselves).

Any advice greatly appreciated.


texts: configure sql developer for oracle using IPC fails with network adapter error

texts:
I have oracle 11 XE installed locally. I can connect on the same machine using sqlplus with sqlplus user/pass. I can also connect using sqlplus user/pass@XE (XE is defined in tnsnames.ora and this connection uses TCP (default). I added an entry in tnsnames that uses IPC (XE_IPC) and I can connect using sqlplus user/pass@XE_IPC. tnsnping XE_IPC succeeds.

SQL Developer connection, when I choose TNS and then XE_IPC from the dropdown, fails with a network adapter error.

My question: how do I configure SQL Developer such that the IPC connection works? Once that works, what is the JDBC connection string that will work?

Here is the rub: my company locks down ports like nobody else, so things like localhost and 127.0.0.1 do not work. This is why I'm using the IPC connection (or at least trying to).

TCP connections using sqlplus, JDBC, and SQL Developer work flawlessly when the server is on another machine.

I tried following Balazs Papp's suggestion. First issue was JDBC version was too low, so I copied ojdbc8.jar from SQL developer JDBC/lib directory to the oracle home JDBC/lib directory that had the other ojdbcX.jar. Passed that test.

Now my error is the native OCI library load failing:

Testing testing native OCI library load ... Failed:
  Error loading the native OCI library
  The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable PATH. Check it to verify that
  the expected native library directory C:\oraclexe\app\oracle\product\11.2.0\server\bin is present and precedes any other client installations.
  java.library.path = C:\usr\bin\sqldeveloper\sqldeveloper\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;c:\oraclexe\app\oracle\prod

There is no obvious way to set java.library.path. \windows\sun\java\bin doesn't exist and is added by SQL Developer. My path starts with C:\oraclexe\app\oracle\product\11.2.0\server\bin, so everything before that is added by SQL developer.

What file(s) is SQL Developer looking for to use the thick client?

When I ignore the error and try his next step, when I click TEST I get "Status: Failure - Test failed: no ocijdbc18 in java.library.path". Sadly, I don't see where ocijdbc18 can be downloaded, and it doesn't exist on my system (I do have ocijdbc11.dll in my server directory.)


texts: Grant execute on specific type in Oracle

texts:
I have created a package and defined a type for collections as follows:

create or replace package <schema>.<package> as

-------------------------------------
   type <type> is table of <something>;
-------------------------------------

...
   function <func>(
      ...
      <param> in <type>,
      ...
    )
      return pls_integer;
...

I have a user that has to call this function but can't because apparently they don't have the permission to execute type collection. I could solve it by

GRANT EXECUTE ANY TYPE TO <user>;

but I am bound by security protocols that says no user has to have access to any data, schema, blueprint or object unless they certainly have to. So I want to give the user explicit grant on specific type <type>, but calling the following statement results in error:

GRANT EXECUTE TYPE ON <schema>.<package>.<type> to <user>;

Error report -
SQL Error: ORA-00990: missing or invalid privilege
00990. 00000 -  "missing or invalid privilege"
*Cause:
*Action:


texts: Choosing the Most Recently Entered Non-Blank Value When Many Values Exist

texts:
I have run into a problem where some IDs have multiple names associated with them.  That is, if I have a location ID from one table and need to pull the location's name from another table based on the location ID, the query is returning multiple names.  I want to choose the newest name based on a timestamp, if that one is not blank (that is, it's not null, but many of the entries are just a blank space).

TABLE LOCATIONS:

LOCATION_ID   LOCATION_NAME    LOCATION_TS
  12345         'BEST AUTO'        4
  12345         'BESTAUTO'         3
  12345         ' '                5
  12347         'FETTS'            3
  12347         'FESTS'            2

TABLE LOCATION_DETAIL:

LOCATION_ID    LOCATION_TYPE
  12345         'AA'
  12347         'ZQ'

I am trying to get a result that looks like this:

LOCATION_ID   LOCATION_NAME    LOCATION_TYPE
  12345         'BEST AUTO'        'AA'
  12347         'FETTS'            'ZQ'

Here is an SQLfiddle.  I am using Oracle 11g.  I have been spinning my wheels on this for a while and I feel like I'm missing something obvious.  I should mention that the way my permissions are set up, pretty much the only thing I can do is query the database, not create views or tables, update data, etc.


texts:
I would like to view the account usage history. I know it can be viewed
via my.cnf through the general query log. But it will slow down the usage of the
DB significantly.

Is there a way to mitigate this or is there another way?


texts: Oracle AQ: History Table for Queue Table

texts: Is there a way to create a history table for a queue table, that will store all the events of the queues, for a defined period of time ?


texts: Execute a query but not return results to client

texts:
I am testing the performance a query and trying to fix a parameter sniffing issue.  Based on the parameter sometimes it returns 3 rows and sometimes it returns over 5K rows.  I want to test the impact of the change I am proposing on execution time.  But since the number of rows returned is so wide the execution time is skewed as it takes into account the time taken by the client to consume the rows.  I want to remove the time taken to return the rows and jut measure the actual execution time on the server.

I have looked at SET FMTONLY ON but it looks like it does not run the query.
I also tried dumping the rows into a table/temptable but that adds cost to the estimation and causes the plan to change in a direction that is not what I want.

Any other way we can achieve this?

Cheers


texts: Is there a standardised way to store auditing information constantly to a table for further analyse?


texts:
Some manual operations (writes) have been made on SLAVE instead of MASTER resulting in the replication being stopped.

Could not execute Delete_rows_v1 event on table xxx.newsletter;
Can't find record in 'newsletter', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log master-bin.000061, end_log_pos 30109220

I've manually recreated the row which has been deleted on the SLAVE.

Is there a way to easily "resume" it without stopping the master, exporting the DB, and starting the whole process again please ? (This happened months ago so the SLAVE is really out of date)

Running MariaDB 10.x
Thanks.


texts: Estimated Execution Plan SQL Server Sort?

texts:
I'm running a query and its taking a age to execute, looking at the execution plan I can see that 51% of the cost is in the SORT? when in the actual query i am not ORDERING BY anything.

Anyone got any ideas how to get this SORT percentage down or remove it all together?
Here is the query I'm running.

SELECT Time_ID,
       Site_Type_ID,
       Abandoned_ID,
       WorkType_ID,
       SUM (staging.dbo.measure.ring_time) AS Ring_Time,
       SUM (staging.dbo.measure.hold_time) AS Hold_Time,
       SUM (staging.dbo.measure.talk_time) AS Talk_Time,
       SUM (staging.dbo.measure.acw_time)  AS ACW_Time,
       COUNT(*)                            CallCount
FROM   measure
       INNER JOIN DataMartEnd.dbo.Time_Dim
         ON measure.StartTimeDate BETWEEN Time_Dim.Time_Start AND Time_Dim.Time_End
       INNER JOIN datamartend.dbo.Site_Type_Dim
         ON measure.DBID = Site_Type_Dim.Site_Type_Code
       INNER JOIN datamartend.dbo.Abandoned_Call_Dim
         ON measure.Abandoned = Abandoned_Call_Dim.abandoned_value
       INNER JOIN DataMartEnd.dbo.Work_Type_Dim
         ON measure.DBID = work_type_dim.MIG_Site_ID
            AND Work_Type_Dim.Work_Type_Code = measure.Queue
            AND measure.StartTimeDate BETWEEN Work_Type_Dim.DimEffectiveStartDtm AND Work_Type_Dim.DimEffectiveEndDtm
GROUP  BY Abandoned_ID,
          WorkType_ID,
          Site_Type_ID,
          time_id

Thanks for the help.


texts: Things to keep in mind while editing Mysql "my.cnf" file

texts:
Today I tried to boost mysql's(installed in my system Ubuntu 14.04) performance as I have many sites(PHP/Magento) installed in my system which relies on mysql as database server, but when I edited the lines like "query_cache_size", "query_cache_type" and other lines(only lines with numeric values which I increased), but instead of speeding it up, it decreased mysql's performance and also it timed-out when I tried to open multiple different pages in browser tabs.

So I need to know what to keep in mind when I edit the "my.cnf" the next time so it doesn't decrease performance but boost it.

What is the relation between those values like "query_cache_size", "max_connections" etc.? Are they inter-dependent ?


texts:
I have a bunch of SQL Servers installed on Windows2008R2.  Currently, each windows server has two NIC's, each connecting to separate networks.  In other words, each server appears on each network.  One network  allows the applications to access the database, the other network is intended to be used for large volumes of data being migrated between the servers without impacting the network the applications use.

I have a job that pulls in a large chunk of data from one SQL server to another.  I would like that job to take advantage of the second network as well, but I am stumped.

Is there a way to tell SQL to use a specific network device when communicating to another server?

Thanks!


texts: How can you use an entity-relationship diagram to document business processes?

texts:
Past exam question: How can you use an entity relationship diagram to document business processes?

As far as I know, I would use an entity-relationship diagram (ERD) to document the relationships between data entities when designing a database. I can't see how ERD's can be used for business processes as flow charts are more suited for this. I would really appreciate if someone could give an example of how this is done.

All the examples I've found online relating to ERD's are for databases and not business processes.


texts:
I am working on migrating a client from a really old SQL Server 2005 database server to a SQL Server 2016 install. Everything appears to be fine for database import, validation, testing...etc. I've even shifted one of their applications over to the new server for testing, all is good. I decided to capture about 30 minutes of trace data from their current 2005 install using the 'replay' template in the SQL Profiler. I then replayed this trace data on the SQL Server 2016 install. The results seem fine except for a single error that keeps popping up in the replay trace data:

[Microsoft][SQL Server Native Client 11.0][SQL Server]
Invalid object name 'xxxx'. (State 42S02) (Code 208)

With xxxx being the name of table. When I check the query that produces this message and run it, it works fine. I know from testing the application (where these queries occur) that it's working fine. The application is using ODBC.
Am I missing something here or can these errors be ignored? If they can, what is causing them?


texts: fundamental difference between Primary/Clustered and Secondary/Non-Clustered index

texts:
Are

Primary Index and Clustered Index similar in concept
Secondary Index and Non-Clustered Index similar in concept


texts: InnoDB IO Capacity on Amazon AWS EBS GP2 Volumes

texts:
I have MariaDB pointing to a data directory that's a mounted Amazon AWS EBS GP2 volume. No other process will be using that volume.

The AWS console lists the volume as having 120 / 3000 IOPS. My understanding is that 120 is the baseline, burstable to 3000 IOPS. My installation is currently using the MariaDB defaults:

MariaDB [(none)]> show global variables like 'innodb_io_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+
2 rows in set (0.00 sec)

Would updating those values to be innodb_io_capacity = 120 and innodb_io_capacity_max = 3000 be the right settings to get the full performance benefit?


texts: Can I parse a text value from a specific column in a query (MySQL)?

texts:
I was playing around with Sakila and I ran across the actor_info view and one of its columns is film_info which is a detail of all the movies one actor has worked in. For example a query would be:

select * from actor_info;
# actor_id | first_name | last_name | film_info
    1      | PENELOPE   |   GUINESS |  Animation: ANACONDA CONFESSIONS; Children: LANGUAGE COWBOY; Classics: COLOR PHILADELPHIA, WESTWARD SEABISCUIT; Comedy: VERTIGO NORTHWEST; Documentary: ACADEMY DINOSAUR; Family: KING EVOLUTION, SPLASH GUMP; Foreign: MULHOLLAND BEAST; Games: BULWORTH COMMANDMENTS, HUMAN GRAFFITI; Horror: ELEPHANT TROJAN, LADY STAGE, RULES HUMAN; Music: WIZARD COLDBLOODED; New: ANGELS LIFE, OKLAHOMA JUMANJI; Sci-Fi: CHEAPER CLYDE; Sports: GLEAMING JAWBREAKER

So I'd like to make a selection of actors who have worked in documentaries or animation but i don't know if this is possible. Sorry if the question is too basic. Thanks!


texts: MySQL FOREIGN KEY constraint is incorrectly formed

texts:
I have the following table definition:

CREATE TABLE `async_task` (
  `idasync_task` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `idasync_type` int(10) unsigned NOT NULL,
  `priority` tinyint(3) NOT NULL,
  `status` enum('todo','doing','failed') NOT NULL DEFAULT 'todo',
  `iduser` int(11) NOT NULL,
  `date_added` datetime NOT NULL,
  PRIMARY KEY (`idasync_task`),
  KEY `priority_id` (`priority`,`idasync_task`),
  KEY `status_type` (`status`,`idasync_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

I've added a column to the my notification table which I want to point to async_task:

ALTER TABLE `notification` ADD COLUMN `async_task_id` BIGINT(20)

And when I add the following foreign key:

ALTER TABLE `notification` ADD CONSTRAINT `fk_notification_async_task`
    FOREIGN KEY (`async_task_id`) REFERENCES `async_task`(`idasync_task`);

I get:

ERROR 1005 (HY000): Can't create table `my_database`.`#sql-182_2d`
(errno: 150 "Foreign key constraint is incorrectly formed")

I've looked elsewhere but only find the errors as being:

The table you're referencing is not created (not the case)
The table you're referencing is not InnoDB (not the case, both notification and async_task are InnoDB)
You're not referencing the entire primary key (not the case, the only primary key is the ID column).

What else could it be?


texts:
currently this query doesn't use a index even after I created one:

EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ;
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=149.14..149.18 rows=15 width=1156) (actual time=11.307..11.311 rows=20 loops=1)
   ->  Sort  (cost=149.14..149.18 rows=15 width=1156) (actual time=11.305..11.306 rows=20 loops=1)
         Sort Key: c.name
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Nested Loop  (cost=0.28..148.85 rows=15 width=1156) (actual time=0.022..6.403 rows=2977 loops=1)
               ->  Seq Scan on crm_customer c  (cost=0.00..96.24 rows=15 width=674) (actual time=0.009..1.184 rows=2977 loops=1)
                     Filter: (rating = 3)
                     Rows Removed by Filter: 2
               ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=498) (actual time=0.001..0.001 rows=1 loops=2977)
                     Index Cond: (id = c.address_id)
                     Heap Fetches: 0
 Planning time: 0.246 ms
 Execution time: 11.353 ms
(13 rows)

When turning seqscan off via set enable_seqscan = false; it will run correctly (ordered by name and is way faster):

EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ;
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..413.02 rows=15 width=1156) (actual time=0.028..0.103 rows=20 loops=1)
   ->  Nested Loop  (cost=0.56..413.02 rows=15 width=1156) (actual time=0.027..0.101 rows=20 loops=1)
         ->  Index Scan using crm_customer_name on crm_customer c  (cost=0.28..360.41 rows=15 width=674) (actual time=0.018..0.040 rows=20 loops=1)
               Filter: (rating = 3)
               Rows Removed by Filter: 2
         ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=498) (actual time=0.002..0.002 rows=1 loops=20)
               Index Cond: (id = c.address_id)
               Heap Fetches: 0
 Planning time: 0.190 ms
 Execution time: 0.133 ms
(10 rows)

My actual index is created with:
CREATE INDEX crm_customer_name_search_index ON crm_customer (name text_pattern_ops ASC NULLS FIRST );

currently I had a bigger index and thought that this way the problem, but however it's not. (indexing rating, too doesn't help)


texts:
I have a very big table and added a new NOT NULL column with DEFAULT on it. Not an unusual thing in DWH environment. It is an online operation, it works very quickly, and stores default in a table metadata, as we can learn there. It works perfectly, SELECTs return a default value. After some time I decided to change the default to a different value. And it looks like we can't achieve it by changing the default. I mean, we can drop old default and create new one, but all SELECTSs would show old default value. I'd try to avoid massive UPDATEs because they are long and painful. Is it possible?

--DROP TABLE #t

CREATE TABLE #t
(
  c1 int not null
)

INSERT INTO #t VALUES (1)

ALTER TABLE #t ADD c2 int not null CONSTRAINT t#c2def default -1
ALTER TABLE #t DROP CONSTRAINT t#c2def

SELECT * FROM #t
-- c2 = -1
ALTER TABLE #t ADD CONSTRAINT t#c2def DEFAULT -2 FOR c2

SELECT * FROM #t
-- still c2 = -1

And more generally, do we have more details on how this feature works? I'd expect that existing non-updated pages should show a new default. That's how I understand 'the default value is stored only in the metadata of the table and the value is looked up, as needed'. Unfortunately, it doesn't work like this. All rows show old default value. I'm checking it on SQL Server 2014.


texts: After mysql server restarts I cannot see my own database

texts:
By mistake I restarted MySQL server in Linux system. But MySQL server was unable to start.

I renamed my.cnf to my_bak.cnf. After that MySQL server started. But I can not see my project database.

I tried below command:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

But I can not see prj_db. But the prj_db folder is available in my Linux system.
I want to restore the database from prj_db.

The steps that I have done :

I made a change in max_allowed_packet=500M.
MySQL server is not starting after changing the max_allowed_packet.
I reverted back to my old my.cnf.
After that also server was not started.
Then I renamed the my.cnf to my_bak.cnf.Then server started.But i can not able to see my prj_db database.(Here i thought a new my.cnf will be created.But i can not see any my.cnf file under /etc folder.)
I moved my_bak.cnf file from /etc folder to another location. mysql service started, but I can not see any my.cnf file under /etc folder as well as my prj_db database also.
Please suggest me  how can i reload my prj_db database.

AND I HAVE .MYD,.frm,.MYI Files in my prj_db folder.

----------------------------------my.cnf-----------------------

[client]
port = 3306
socket = /DB/mysql/mysql.sock

#This is to handle unicode
default-character-set=utf8

[mysqld]
datadir=/DB/mysql
socket=/DB/mysql/mysql.sock
port = 3306
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

#skip-networking
#skip-external-locking

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = localhost

#Added by JV to store and retrive unicode (native language) data
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
#character_set_client=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

User=mysql
max_allowed_packet=500M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

But after that I reverted back to old my.cnf and then I moved the .cnf file from /etc folder to other backup place.


texts: Find total duration of each consecutive series of rows

texts:
MySQL Version

The code will run in MySQL 5.5

Background

I have a table like the following one

CREATE TABLE t
( id INT NOT NULL AUTO_INCREMENT
, patient_id INT NOT NULL
, bed_id INT NOT NULL
, ward_id INT NOT NULL
, admitted DATETIME NOT NULL
, discharged DATETIME
, PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

This table is about patients in a hospital and it stores the beds where each patient spent some time while being hospitalized.

Each ward may have multiple beds and each patient may move to a different bed within the same ward.

Objective

What I want to do is to find how much time each patient spent in a specific ward without having moved to a different ward. I.e I want to find the total duration of the consecutive time he spent within the same ward.

Test case

-- Let's assume that ward_id = 1 corresponds to ICU (Intensive Care Unit)
INSERT INTO t
  (patient_id, bed_id, ward_id, admitted, discharged)
VALUES

-- Patient 1 is in ICU, changes some beds, then he is moved
-- out of ICU, back in and finally he is out.
(1, 1, 1, '2015-01-06 06:05:00', '2015-01-07 06:04:00'),
(1, 2, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(1, 1, 1, '2015-01-07 07:08:00', '2015-01-08 08:11:00'),
(1, 4, 2, '2015-01-08 08:11:00', '2015-01-08 09:11:00'),
(1, 1, 1, '2015-01-08 09:11:00', '2015-01-08 10:11:00'),
(1, 3, 1, '2015-01-08 10:11:00', '2015-01-08 11:11:00'),
(1, 1, 2, '2015-01-08 11:11:00', '2015-01-08 12:11:00'),

-- Patient 2 is out of ICU, he gets inserted in ICU,
-- changes some beds and he is back out
(2, 1, 2, '2015-01-06 06:00:00', '2015-01-07 06:04:00'),
(2, 1, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(2, 3, 1, '2015-01-07 07:08:00', '2015-01-08 08:11:00'),
(2, 1, 2, '2015-01-08 08:11:00', '2015-01-08 09:11:00'),

-- Patient 3 is not inserted in ICU
(3, 1, 2, '2015-01-08 08:10:00', '2015-01-09 09:00:00'),
(3, 2, 2, '2015-01-09 09:00:00', '2015-01-10 10:01:00'),
(3, 3, 2, '2015-01-10 10:01:00', '2015-01-11 12:34:00'),
(3, 4, 2, '2015-01-11 12:34:00', NULL),

-- Patient 4 is out of ICU, he gets inserted in ICU without changing any beds
-- and goes back out.
(4, 1, 2, '2015-01-06 06:00:00', '2015-01-07 06:04:00'),
(4, 2, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(4, 1, 2, '2015-01-07 07:08:00', '2015-01-08 09:11:00'),

-- Patient 5 is out of ICU, he gets inserted in ICU without changing any beds
-- and he gets dismissed.
(5, 1, 2, '2015-01-06 06:00:00', '2015-01-07 06:04:00'),
(5, 3, 2, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(5, 1, 1, '2015-01-07 07:08:00', '2015-01-08 09:11:00'),

-- Patient 6 is inserted in ICU and he is still there
(6, 1, 1, '2015-01-11 12:34:00', NULL);

In the real table the rows are not consecutive but for each patient the discharge timestamp from one row == the admission timestamp of the next row.

SQLFiddle

http://sqlfiddle.com/#!2/b5fe5

Expected Result

I would like to write something like the following:

SELECT pid, ward_id, admitted, discharged
FROM  (....)
WHERE ward_id = 1;

(1, 1, '2015-01-06 06:05:00', '2015-01-08 08:11:00'),
(1, 1, '2015-01-08 09:11:00', '2015-01-09 11:11:00'),
(2, 1, '2015-01-07 06:04:00', '2015-01-08 08:11:00'),
(4, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(5, 1, '2015-01-07 07:08:00', '2015-01-08 09:11:00'),
(6, 1, '2015-01-11 12:34:00', NULL);

Please, do take note that we can't group by patient_id. We must retrieve a separate record for each ICU visit.

To put it more plainly, if a patient spends time in ICU, then moves out of it and then returns back there, I need to retrieve the total time he spent in each ICU visit (i.e. two records)


texts: Calculating lifetime customer value per month

texts:
I'm trying to calculate the lifetime customer value (LCV/CLV) for the last 12 month for the date the customer joined. Supposing each customer is buying something more than just in that first month (which they do), the monthly LCV value should continue to grow over time. So the December LCV should be much higher than the January LCV.

This is the query I am using to try to do that:

SELECT
   COUNT(DISTINCT user_id) as customers,
   SUM(amount)*.01 as amount,
   SUM(amount_refunded)*.01 as refunded,
   SUM(amount_refunded)/SUM(amount)*100 as refund_percent,
   SUM(amount)/COUNT(DISTINCT user_id)*.01 as LCV_pre_refunds,
   (SUM(amount)-SUM(amount_refunded))/COUNT(DISTINCT user_id)*.01 as LCV,
   CONCAT_WS(', ', MONTHNAME(MAX(created_at)), YEAR(created_at)) as month
FROM `payments`
WHERE `amount` > 0
AND `payments`.`status` = 'succeeded'
AND `payments`.`created_at` >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
AND `payments`.`user_id` IN (
SELECT `id` FROM `users` WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
)
GROUP BY MONTH(`payments`.`created_at`);

This query would give me the values for this month and the previous month. How would I change it to get the values for the last 12 months grouped by month but still limiting the LCV sum to start growing from the point the customer joined?

For example, suppose I buy a plan in January for $50 and then in 3 months buy another plan for $10 and then in 2 months buy something else for $15. Then my lifetime value is $75 but in Jan it was $50, in April it was $60, in June it was $75.


texts: Query multiple rooms with different conditionals (Adults, Children)

texts:
I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per hotel like this:

1 Room with 2 Adults / 0 Children
1 Room with 4 Adults / 2 Children
1 Room with 2 Adults / 1 Children

Here is my query:

SELECT
COUNT(pl.day) AS Days, p.property_ID AS Hotel_ID,
p.name AS Hotel_Name, r.room_name AS Room_Name, r.room_type_ID AS Room_ID

FROM property p

  INNER JOIN room_type r
    ON p.property_ID=r.property_ID
    AND (r.max_adults >= 3 AND r.max_children >= 0)

  INNER JOIN plan pl
    ON pl.room_type_ID=r.room_type_ID
    AND (pl.day >= "2014-07-07" AND pl.day <= "2014-07-11")

GROUP BY Room_ID, Hotel_ID
HAVING Days = 4

EDIT:
The tables are:

property > property_ID ...
plan > property_ID | room_type_ID | day | rate | alloc | min_stay ...
room_type > property_ID | max_adults | max_children ...
...


texts: DMV Index proposals still reported as missing

texts:
We have recently converted a big client running our patient/journal application from ORACLE to MSSQL, and after the conversion we have had quite a few performance issues.

After running the DMV query:

SET NOCOUNT ON
PRINT '-- MissingIndexes'
DECLARE @ProductVersion NVARCHAR(128)
SET @ProductVersion = CAST(SERVERPROPERTY ('ProductVersion') AS NVARCHAR(128))

IF CAST(LEFT(@ProductVersion, CHARINDEX('.',@ProductVersion)-1) AS INT) > 8
BEGIN
SELECT
  IndexImpact = user_seeks * avg_total_user_cost * (avg_user_impact * 0.5),
  LastUserSeek = groupstats.last_user_seek,
  FullObjectName = details.[statement],
  TableName = REPLACE(REPLACE(REVERSE(LEFT(REVERSE(details.[statement]),
    CHARINDEX('.', REVERSE(details.[statement]))-1)),'[',''), ']',''),
  EqualityColumns = details.equality_columns,
  InequalityColumns = details.inequality_columns,
  IncludedColumns = details.included_columns,
  Compiles = groupstats.unique_compiles,
  Seeks = groupstats.user_seeks,
  UserCost = groupstats.avg_total_user_cost,
  UserImpact = groupstats.avg_user_impact,
  DatabaseName = DB_NAME(details.database_id)
FROM
  sys.dm_db_missing_index_group_stats AS groupstats
  INNER JOIN sys.dm_db_missing_index_groups AS groups
    ON groupstats.group_handle = groups.index_group_handle
  INNER JOIN sys.dm_db_missing_index_details AS details
    ON groups.index_handle = details.index_handle
ORDER BY
  IndexImpact DESC ;
END

We got a massive list of reported "missing indexes", with quite a bit of "IndexImpact".

After we retrieved the list of indexes, we narrowed the list down to a handful which we reckoned would suffice to fix the most critical issues and added those.

One of the indexes we added was the following:

 IndexImpact  TABLE    EQ-Columns  INEQ-COLUMNS  INCLUDED
 69287773     JOURNAL  NULL        EmpId         All columns in the table(*)

After the indexes was added we tried the DMV query again, but this index and a few others like it where still reported as missing. We blamed this on not having reset the execution plans and cleared the cache buffers, so we ran:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

But even after running those queries the indexes where still reported as missing.

We have not identified the code that executes this exact query, but running the following query:

SELECT * FROM JOURNAL WHERE EmpId = 3

Results in the following execution plan:

Are we missing something? Or is it just reported as missing because the server has not been restarted since the index was added?


texts:
I am working with two EC2 instances. On one instance Golang code runs and on another instance Mongodb runs. Now I want to import a json file through command line for which I have written following command:

mongoimport --host MYIP --port 27017 --username USERNAME --password 'PASSWORD' --collection COLLECTION --db MYDB  --drop --type json --file [FILEPATH]/[FILENAME].json --jsonArray

when I run this command on Mongodb server then it runs there properly but when I run it on a different server (from where the connection to mongodb will be made), it gives me following error:

assertion: 18 { ok: 0.0, errmsg: "auth failed", code: 18, codeName: "AuthenticationFailed" }

For username and password I logged into the database and created a user having role "readWrite".

Can anybody tell me how should I connect to it through remote server?

Thanks!


texts: Cassandra: memtable_offheap_space_in_mb and memtable_cleanup_threshold

texts:
I use C* v2.1.2 do write only workload (into one column family) and experiment with offheap-objects memtable space.

I set parameters to:

memtable_offheap_space_in_mb = 51200  # 50Gb
memtable_cleanup_threshold = 0.99

and expect that flush will not be triggered until available memtable offheap space reaches ~50Gb. But flushes are triggered
before that limit. System monitor shows that in used only ~16Gb at that moment (linux+jvm+heap+...).

Why such thing is happened?


texts: What are the valid formats of a PostgreSQL schema name?

texts:
I can't seem to find documentation that describes the valid formats of a PostgreSQL schema name. I know that a schema name cannot:

start with a number
have spaces
start with pg_

What else? Where should I look?


texts: Combine two table with different column but same value


texts: ~15 data sets w/ 25k rows & ~20 columns + quick  one time reports = Access VS SQL?

texts:
I am working with around 10-15 datasets each containing around 25,000 rows and around 20 columns. Data is text in name/adress/desc and the rest numbers.

I need to be able to calculate certain fields, pull out data on demand for certain list of customers (10, 20, 30) and generally extract data based on certain criteria.

Am I better off using MS Access or using SQL?

Please ask if You need any additional info..


texts: Best Possible Solution for Reporting Mirroring or Replication?

texts:
Hi I have SQL SERVER 2008R2 Data Centre Edition and I use SSRS and a Vendor application for reporting.  I have a Reporting Server where we backup and restore everyday from Live Server to have Data available for our reports.

I face many problems because of this any code developed has to wait a day until the restore is done to test. Any code developed on Reporting Server is Over written. Surprise surprise reports show a day old data and I could go on and on.

I know I have two options available Mirroring and Replication:

Mirroring will allow read-only mode and will reports run fine? Does this Read-only mode any other implications that I am missing ?

Replication I do not have Primary Keys on all the tables so I am left only with the option of Merge replication which uses a globally unique identifier (GUID), I cannot let sql server to add this column to all the tables in my databases.

Can anyone please advise me a solution for having a Live Copy of Sql Server Database on another Server for Reporting Purposes?

MY Requirments

I can live with a few minutes old data.
Any changes made on
Production Server needs to be copied over to Reporting Server i.e
Data, Sql Server Objects (Tables, Views, Store Procedures,
Everything)
Any Objects created on Reporting Server does not need
to copied over to production server/databases.

Please advise any solution as it will be a great help Thank you in Advance.


texts:
What is the correct implementation of handling concurrent SQL inserts while ensuring a non-primary key column (i.e. wid) unique and auto-incremental?

Note: There was a design constraint that led this field (i.e. wid) in not becoming the primary key. A separate auto-increment PK is included in the column called id. Changing the wid to PK is not feasible as the database design is now heavily coupled to multiple software projects. So I need to devise a workaround.

Some more information:
The column wid follows a predefined format of concatenation of branch id and sequence number.

Sample rows:
ID - wid
1 - 11000001
2 - 11000002
3 - 10000001
4 - 11000003
5 - 10000002

I'm thinking of 3 possible ways:

1.) Reserving the wid by executing a transaction and proceeding with other SQL statements which should have been included in the same transaction. The reservation is executed in a separate transaction to make the locking as short as possible. However, if the subsequent transaction failed (e.g. due to database connection issues), then the invalidated row containing the reserved wid will remain in the table.
(note: the minimum and maximum threshold used in wid is the concatenation of branch id and sequence number)

START TRANSACTION;
SET @wid_d = 0;
SELECT COALESCE(MAX(`wid`), 0) INTO @wid_d FROM table
WHERE `wid >= 10000000 AND `wid <= 10999999 FOR UPDATE;
INSERT INTO table (`wid`) VALUES (IF (@wid_d = 0, 10000001, @wid_d+1) )
COMMIT;

2.) Including the reservation of wid in the entire transaction to eliminate the problem of insertion of invalidated row, should the transaction fail but this approach prolongs the locking of tables.

3.) Rely on the failure of transaction calls for re-executing the transaction until it performs successfully; should the transaction fail due to the race condition of inserting the same MAX(wid) + 1. This approach will prevent insertion of invalidated rows but the problem is the number of times the transaction should be re-run. There is also no distinction of knowing the problem that is caused by the conflicting wid or other problems, hence, this is a problematic approach.


texts: UTL_TCP how to monitor number of connections?

texts:
I have a leak somewhere in the number of UTL_SMTP connections being made and closed.  There is an underlying limit of 16 from its reliance on UTL_TCP.

I'd like to be able to monitor the connection usage to find the problem.

Where can I get a count of the number of open UTL_TCP connections?


texts: Sql server error message "the database is read only" in a remotely server - availability database

texts:
I have a failover cluster with a high availability sql server service, the problem is that I cannot create a table, update, delete remotely even if I already give specific permissions and i had already made the request "ALTER DATABASE [****] SET READ_WRITE".

I don't understand why I always get the "database is read only" error message every time.

Thank you.


texts:
Below is an example pgpool architecture:

This implies that you only need to have pgpool on a single server; is this true? When I look at the configuration I also see that you configure backends within pgpool.conf; so it further implies this. But, it doesn't explain why I see pgpool on backend servers also.

When looking over the documentation I also see:

  If you are using PostgreSQL 8.0 or later, installing pgpool_regclass function on all PostgreSQL to be accessed by pgpool-II is strongly recommended, as it is used internally by pgpool-II.

So I'm not sure what to think; if it's best practice to have pgpool on all backends or just a dedicated server?


texts: Copying a table while maintaining consistency in Oracle

texts:
We have a PL/SQL stored procedure that is called on user demand which takes a copy of another table (ie. drop index > delete (not truncate) > insert > create index) and we've noticed the odd race condition where an index will not always get recreated.

This leaves us a table with no indexes, so future calls to the procedure fail (yes we could simply ignore the failed drop index). However, I'd like to get to the bottom of why the table is losing its indexes.

I don't think LOCK TABLE is the answer because after every DDL statement (eg. drop index) I'd keep losing the lock due to the implicit commit.


texts: MySQL memory usage keeps growing. 30GB growth in 3 weeks

texts:
We have a server with 64GB memory.

After restarting MySQL, in 3 weeks memory is almost full.

MySQL was installed using Debian Percona packages.

Version: Percona Server Release 17 5.7.19-17-log

In daytime, average selects per second are 2500.
Average writes per second are around 100.

Here is the mysqld config.
I'm not a DBA but I have access to server and MySQL root.

Are there any obvious problems? Thanks.

[mysqld]
skip-slave-start
log_error = /var/log/mysql/error.log
server-id               = 2
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 1
max_binlog_size         = 1G
binlog_do_db            = gsa

user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES
symbolic-links=0

# CACHES AND LIMITS #
tmp-table-size                 = 512M
max-heap-table-size            = 512M
query-cache-type               = 1
query-cache-size               = 200M
query-cache-limit              = 10M
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 102400
table-definition-cache         = 16384
table-open-cache               = 400

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 32M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 20G
innodb-buffer-pool-instances   = 8

innodb_write_io_threads = 8
innodb_read_io_threads = 8


texts: Best way to build a server inventory db and web interface?

texts:
My team is looking to build a rich inventory of servers at our enterprise. We are trying to consolidate several different excel documents into one database.

I found a great utility called AppGini (http://bigprof.com/appgini/) that can generate some pretty sweet material in short order. Our only problem is we might end up having 40 potential fields for each server...

Demo database here: http://bigprof.com/demo/employees_view.php

IE, it's a really wide spreadsheet, lots of columns. A lot of that data is not needed and would be annoying on a webpage (nobody wants to scroll left to right in a browser) The pages that AppGini generates can't give custom views.

For example; I might only want to see "Name", "Description", "Administrative Contact" but another person might need "Name", "Services", "Stack", "Ports" - I hope that makes sense. We all need to see something different.

What is the best way to go about this? Are there any similar programs to AppGini or utilities that I'm not familiar with that might be able to do this for me?


texts:
I am new to RML and am trying to use it to capture a trace, replay it on another server and then compare the two.

I have managed to capture a trace using the server side script that comes with it and then used ReadTrace to convert it to RML.

I have even managed to use OSTRESS to replay it on my new 2012 server named PROD using the following command.  I am only guessing this is OK being a 2012 server?

ostress.exe -i\\Path\*.rml -mreplay -c\\path\samples.ini -SPROD

While at the same time I had another server side trace running.

So how do I now compare the traces?  I have my first trace in a .trc format and .rml format and my second just in .trc.


texts: How to set the default timezone in PostgreSQL on Amazon RDS?

texts: The default timezone is currently set to UTC. How to replace that default value? I am using PostgreSQL running on top of Amazon RDS


texts: When to use role codes and when to use supertype / subtype

texts:
What's the best practice to follow when designing a data model when it comes to choosing between using supertype/subtype and role codes?

To be clear, when i say role codes I mean a table design like this:

Whereas for supertype / subtype I'd do something like this:

The advantages I see for supertypes / subtypes are that the types aren't mutually exclusive (i.e. food could be both a fruit and a vegetable) and that if you have properties that only apply to one of the types you could either put them in the subtype table, or in another table with a foreign key that points to the subtype table. The disadvantages are that you have to create a table for every sub type.

So does it make more sense to have supertypes / subtypes until you have so many types that it's unwieldy to have a seperate sub type table for every type?

Should you use supertypes / subtypes regardless of how many types you have as long as each type has very different properties?

Or does it make sense to make the decision based on weather your types should be mutually exclusive or not?

I've been struggling with this for a while so any advice from some experienced data modelers on how to make these decisions would be greatly appreciated.


texts:
In our asset tracking system, I have the following 2 tables:

item
item_location_date

Where item_location_date has the following fields:

ild_item (FK to Item)
ild_location (FK to location)
ild_pallet (Nullable FK to pallet id)
ild_created_when (creation timestamp)

To get an item's current location, it's something like this:

select *
from item
cross apply (
    select top 1 *
    from item_location_date
    where ild_item = itm_id
    order by ild_date_created desc
) ild

This, however is relatively slow. Looking at the execution plan, the issue is with the sorting of the item_location_date table, it is using the correct clustered index, which I've defined as:

ALTER TABLE [dbo].[item_location_date] ADD  CONSTRAINT [PK_item_location_date] PRIMARY KEY CLUSTERED
(
    [ild_date_created] DESC,
    [ild_item] DESC,
    [ild_location] DESC
)

However, performance is still poor. Changing the query to:

select *
from
item inner join
(select *
 from (
        select *,
                ROW_NUMBER() over (PARTITION by ild_item order by ild_date_created desc) rn
        from item_location_date
    ) ild
    where rn = 1
) itm_location
on itm_location.ild_item = itm_id

Yields better results, changing the sort from a top-N-Sort to a normal sort.

This database is still very small and easilty fits in RAM with plenty to spare, but I'm concerned about the performance as the quantity of data increases.

Is there anything I'm missing? Do I have the index set up to return to return the results as efficiently as possible for this scenario?


texts: MySQL Cluster Data node can't get the node ID?

texts:
I have a MySQL Multi Node ( Cluster ) like this
A is ndb_mgmd and also the client
B and C is ndbd
I have successfully set up everything but the Data Node just cant get the Node Id.
In ndb_mgmd server when I use ndb_mgm and show, I got something like this:
Connected to Management Server at: xxxx:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2    @xxxxxxxx  (mysql-8.0.21 ndb-8.0.21, Nodegroup: 0)
id=3    @xxxxxxxx (mysql-8.0.21 ndb-8.0.21, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @xxxxxxxx  (mysql-8.0.21 ndb-8.0.21)

[mysqld(API)]   1 node(s)
id=4    @xxxxxxxx  (mysql-8.0.21 ndb-8.0.21)

I even show NodeId=2 status and got this :
ndb_mgm> 2 status
Connected to Management Server at: xxxxxxxx:1186
Node 2: started (mysql-8.0.21 ndb-8.0.21)

The problem is in the Ndbd server somehow it cant get the ID:
2020-09-05 22:51:13 [ndbd] INFO     -- Angel connected to 'xxxxxxx:1186'
2020-09-05 22:51:43 [ndbd] ERROR    -- Failed to allocate nodeid, error: 'Error: Could not alloc node id at xxxxxxxxx port 1186: No free node id found for ndbd(NDB).'

Things i have tried :

See if the config.ini got problem but everthing look fine: ndbd nodeid , etc
Check my.cnf in ndbd server : The ndb-connectstring point to ndb_mgm server .

Thanks for reading this question .


texts:
My DB is SQL Server 2008 and I want to scale up the system by 5 fold (maybe more in the future).

The system currently has 95% read & 5% write from front end. And from backend actual data is bulk loaded using ETL.

I looked in to options like

Scaling with shared database (using SAN)
SODA (new caching mechanism)
Clustering in SQL Server 2008 R2

but all either allows only read or duplicate the existing data.

Can someone point me in the right direction (replication of data would be my last but one option).

Thanks
JC

Thanks all for the suggestions.

Looks like i am left with increase the hardware and for better performance at this point of time.

SAN setup would become more administrative work because the updates from data load would happen atleast once a day and we need to bring down the servers on SAN every time. And there is also some updates happening from front end.

Other thing i am planning to do is to divide the data on to two servers based on regions, which will reduce the load on each server to almost half.


texts: Postgresql Streaming Replication on Windows failover

texts:
Hello i have setup streaming replication using PostgreSQL 11 on Windows working OK.
How can i manually use the replica ( it is hot_standby=on ) in case Master fails ?
What are the steps i have to perform to the replica ?


texts: How can I determine what's a better configuration for my app?

texts:
I'm currently using Postgres 12 on AWS RDS and am using their default configuration for the database.
I use a gem called PgHero that advises me to use PGTune to improve the efficiency of the database.  After inserting my inputs on https://pgtune.leopard.in.ua/
# DB Version: 12
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 7 GB
# CPUs num: 2
# Data Storage: ssd

It gives me the advice to update my DB configuration to:
max_connections = 200  # AWS has: LEAST({DBInstanceClassMemory/9531392},5000)
shared_buffers = 1792MB  # AWS has {DBInstanceClassMemory/32768}
effective_cache_size = 5376MB # AWS has {DBInstanceClassMemory/16384}
maintenance_work_mem = 448MB  # AWS has GREATEST({DBInstanceClassMemory*1024/63963136},65536)
checkpoint_completion_target = 0.7  # AWS has 0.9
wal_buffers = 16MB # AWS is not set
default_statistics_target = 100  # AWS is not set
random_page_cost = 1.1  # AWS is not set
effective_io_concurrency = 200 # AWS is not set
work_mem = 9175kB # AWS is not set
min_wal_size = 1GB  # AWS is 192MB
max_wal_size = 4GB  # AWS is 2048MB
max_worker_processes = 2 # AWS is 8
max_parallel_workers_per_gather = 1 # AWS is not set
max_parallel_workers = 2 # AWS is not set
max_parallel_maintenance_workers = 1 # AWS is not set

I'm not sure whether to use PGTune or just leave RDS defaults?  How does one typically determine if there is performance updates? Am I in over my head?


texts: MySQL Community Edition: Can I replicate my entire server with another server in one go?

texts:
I need to migrate all the databases from one server to another. There are about 100 databases, and all databases are used daily and updated/inserted too and read through their respective websites.

If I copy over the databases in one go, by the time the website for that database is migrated to the new server, the data will be out of sync.

Is there a way to setup a temporary replicate of all the databases in the server to replicate to the new server (one way master/slave replication, master being the original server and slave being the source server). I can then setup the website on the new server and run it in parallel to the old site until all sites are migrated then switch off the old server.

I'm running MySQL 5 community edition.

Thanks

Jason


texts:
I have a stored procedure that allows users to pass in parameters.

SELECT *
FROM Table1
WHERE columnA =@paramA, ColumnB=@paramB....

I need to hide the results if there are less than 10 rows, but return them if there are more than 10. Is there a clean way to do this? Any help would be great.


texts: Comparing the location of values in different tables

texts:
New to SQL. Hope to find distinct pizza sold by restaurants that are located in the same area as bob (customer).

Database schema:

Customers(cname, area)

Restaurants(rname, area)

Pizzas(pizza)

Sells(rname,pizza,price)

Likes(cname, pizza)

   SELECT distnct pizza
   FROM Sells
   WHERE rname in (
      SELECT rname
      FROM Restaurants
      WHERE area.....
   -- This is the part where i don't know how to continue.
   -- How do i write it so that i compare the location of the restaurant with bob's location?
   );


texts: Database Design Concept - Are Multiple Relationships A Bad Thing?

texts:
I have a question about Relational Database design. I'm just learning the basics of databases, so bare with me, in that I'm not sure of the technical terms for some of the concepts I'm trying to express here.

In short:

In the database structure in the image below, there is no replicated data in different tables, so far as I can tell, this design is 3NF compliant, however maybe I have misunderstood one or more of those concepts.

I'm unsure as to whether or not having multiple relationships in the design creates possible issues with referential integrity. A picture says a thousand words at this point:
http://img580.imageshack.us/img580/3757/t9ku.png

If you look at the Service table in the middle, it has a relationship directly to Customer. It also has a relationship to Site, which in turn has a relationship to Customer.
Is this okay, or should I remove some foreign keys so that the only 'path' from Service to Customer, is via the Site table?

In long:

I'm working on a practice example revolving around data services. I've created my 'structure', that is to say the tables themselves and the key's in them (both primary and foreign) in order to see the relationships. What has struck me is that it is possible to define a few of my tables to have more than one relationship to other tables, such that there is more than one 'path' between tables.

Does this create possible issues of referential integrity?
(i.e. You can get from one table to another, with or without going 'through' other tables, the extra relationship appears to allow some tables to be 'bypassed')

Is this a bad thing in a relational database?
Should any given table only have a single relationship to another table such that the 'path' through the database is 'linear' rather than having choices in the 'path'?
Or, assuming this is in fact 3NF compliant, is being able to define more relationships a good thing?
(i.e. it might make query design easier in some instances because the direct relationships allow you to avoid having to join lots of intermediate tables...)


texts: Will SQL Server 2014 not cause problems with a SQL Server 2008 DB?

texts:
I will be doing development work on a SQL Server 2008 database.

I am planning on buying a copy of SQL Server for this project. I am wanting to verify that the latest SQL Server version, 2014, won't cause any problems in the following scenario:

Pull,(Backup) copy of database from 2008 (the source)
Restore database to my local 2014
Make changes
Pull,(Backup) copy of database from 2014
Restore over the existing 2008 database (which was the source) with the copy from 2014

I am aware that I won't be able to utilize any of the newer features that 2014 might offer that aren't available in 2008... I just want to know that it won't blow anything up.

NOTE: I don't have access to the SQL Server 2008 DB server right now... additionally I don't yet have the SQL Server 2014 software; I have SQL Server 2012 express.

I have an answer for this, just export as SQL statements (thanks to comments below). The better question is here


texts: Postgresql: A bit confused on listing all roles in my system

texts:
I am just getting into PostgreSQL. I am ultimately interested in using PostGIS but thought it would pay off in the long run to get thoroughly familiar with the client/server authentication systems and also some general database management concepts through the command line instead of pgadmin GUI.

Going through the PostgreSQL documentation, it shows two methods for listing the defomed roles in the system.

\du

and

SELECT rolname FROM pg_roles

I wanted to try these out on my own system, and I did the following in cmd:

psql -U postgres
psql (11.1)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Then I did:

postgres=# SELECT rolname FROM pg_roles;
          rolname
---------------------------
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 postgres
(9 rows)

As you can see the listed roles are different, which is very confusing to me. my pg_hba.conf file is as follows:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

I changed all the authentication methods to trust because I wanted to facilitate access to the superuser role, since I forgot my password and could not find any straightforward method to retrieve it.

Why are the role names different for the two commands? Are

 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend

also roles?


texts: FILEPROPERTY not working over linked server to Azure SQL Database

texts:
FILEPROPERTY (Transact-SQL) is not working with a linked server pointing from a on-Premise SQL 2017 to a Azure SQL Database. I see the following comment in books online:

  Returns NULL for files that are not in the current database.

But my linked server has @catalog pointing to a specific database.

Here is my code and I get NULL for spaceUsedMB , freeSpaceMB, and spaceUsedMB columns.

SELECT
 collectedAt = GetDate(),
 serverName =(SELECT * FROM OPENQUERY (AzureDB_adventureworks, 'SELECT @@SERVERNAME')),
 databaseName=(SELECT * FROM OPENQUERY (AzureDB_adventureworks, 'SELECT DB_NAME()')),
 fileName = LEFT(a.NAME, 64) ,
 a.FILE_ID AS fileId,
 fileSizeMB = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)),
 spaceUsedMB = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,'SpaceUsed')/ 128.000, 2)),
 freeSpaceMB = CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,'SpaceUsed'))/ 128.000, 2)),
 percentFree = CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,'SpaceUsed'))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))),
 a.physical_name
FROM [AzureDB_adventureworks].[adventureworks].sys.database_files a

I was able to get by with following code but still curious to know why above code did not work.

SELECT * FROM OPENQUERY (AzureDB_adventureworks,
    'SELECT
        GETDATE() AS collectedAT,
        @@SERVERNAME AS serverName,
        DB_NAME() AS databaseName,
        LEFT(a.name, 64) AS fileName,
        a.file_id AS fileId,
        a.size AS fileSizeMB,
        CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,''SpaceUsed'')/ 128.000, 2)) AS spaceUsedMB,
        CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,''SpaceUsed''))/ 128.000, 2)) AS freeSpaceMB,
        CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,''SpaceUsed''))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))) as percentFree,
        a.physical_name AS physicalName
from adventureworks.sys.database_files a'
)

If I run the following code against Azure SQL Database I will get correct result for all columns.

SELECT
 collectedAt = GetDate(),
 serverName = @@SERVERNAME,
 databaseName= DB_NAME(),
 fileName = LEFT(a.NAME, 64) ,
 a.FILE_ID AS fileId,
 fileSizeMB = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)),
 spaceUsedMB = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,'SpaceUsed')/ 128.000, 2)),
 freeSpaceMB = CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,'SpaceUsed'))/ 128.000, 2)),
 percentFree = CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,'SpaceUsed'))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))),
 a.physical_name
FROM sys.database_files a


texts: How Can I Read Where TempDB's Files Will Be Created After Restoring Master DB?

texts:
I'm restoring to an alternate server in a DR drill and have restored master but can't restart the instance, even with /f and /m switches. The log is indicating tempdb can't be created, probably looking for the paths from the source instance. How can I determine which path it's trying to create tempdb's DB files in?

I was able to restore the master backup under an alternate name on a different instance so I can browse the system tables. Is there a spot where I can look for them?

I have to go with the assumption that access to the source server is lost so I can only rely on the backups to determine the correct configuration.


texts: Which postgres design for a nested resource that can link to the same type of resource

texts:
I have this kind of data that I need to store in postgres (ruby object).

#<InitRes:0x007f8ec3734c78
 @kind="RestrictionsSchema",
 @restrictions=
  [#<ParsedRes:0x007f8ec60ed218
    @description="Disallow user",
    @key="disallowUserConfig",
    @restriction_type="bool",
    @title="Disallow user">,
   #<ParsedRes:0x007f8ec2c96898
    @description="Restriction message",
    @key="disallowUserConfigMessageKO",
    @restriction_type="string",
    @title="Restriction message">,
   #<ParsedRes:0x007f8ec3551730
    @description="List of configurations",
    @key="configurations",
    @nested_res=
     [#<ParsedRes:0x007f8ec2565678
       @description="configuration",
       @key="configuration",
       @nested_res=
        [#<ParsedRes:0x007f8ec2504328
          @description="configuration name",
          @key="name",
          @restriction_type="string",
          @title="Profile Name">,
         #<ParsedRes:0x007f8ec34b35d0
          @description="server URL",
          @key="server",
          @restriction_type="string",
          @title="Server">,
         #<ParsedRes:0x007f8ec2a64228
          @default_value=
           #<ParsedResRestrictionValue:0x007f8ec3437de0
            @type="bool",
            @value_bool=false>,
          @description="Use Web logon",
          @key="weblogonMode",
          @restriction_type="bool",
          @title="Use Web logon for authentication">],
       @restriction_type="bundle",
       @title="Configuration">],
    @restriction_type="Array",
    @title="Configurations">]>

The ParsedRes can have multiple nested_res that contains and a list of ParsedRes. It can be nested up to two levels deep.

I'm wondering what will be the best design for this kind of data. I would to need to be able to fetch the same tree to display it in the future.

I thought about having a table that can link to a parent and daughter

CREATE TABLE parsed_res (
  id           SERIAL PRIMARY KEY,
  -- other columns
  parent_id    INT,
  daughter_id  INT
);

Is it a good idea?
What is the best way to fetch this tree (subselect?)
ltree ?


texts: Failed to run query: error: 1023 on Redshift

texts:
I have a lambda :

'use strict';
const config = require('./redshift_config');
const pg = require('pg');
const redshift_conn = `pg://${config.user}:${config.password}@${config.host}/${config.database}`;

function move_to_disable_tables(users_ids, context) {
  const client = new pg.Client(redshift_conn);
  const query = `
  INSERT INTO disable_connections
  SELECT *
  FROM active_connections
  WHERE user_id
     IN (${users_ids});
  DELETE FROM active_connections
  WHERE user_id
     IN (${users_ids});
  INSERT INTO disable_downloads
  SELECT *
  FROM active_downloads
  WHERE user_id
     IN (${users_ids});
  DELETE FROM active_downloads
  WHERE user_id
     IN (${users_ids});
  `;

  client.connect(function(err) {
    if (err) {
      return context.fail(`Failed to connect: ${err}`);
    }
    client.query(query, function (err) {
      if (err) {
        return context.fail(`Failed to run query: ${err}`);
      }
      context.succeed(`Successfully transferred.`);
      client.end(function (err) {
        if (err) return context.fail(`Error in ending query: ${err}`);
      });
    });
  });
}

exports.handler = function(event, context) {
  move_to_disable_tables(event.users_ids, context);
};

But I get sometimes :
"Failed to run query: error: 1023"

  The error you are getting occurs whenever you run concurrent transactions is such a way that Redshift's serializable isolation is violated. See http://docs.aws.amazon.com/redshift/latest/dg/c_serial_isolation.html for a description of what this means.

From : https://forums.aws.amazon.com/message.jspa?messageID=443644#443644

Before the sql was warped into a block
BEGIN; END;
but I removed it to fix the issue but it still occurs. Any ideas?

Does this 4 queries can fail in concurrency?

The lambda doesn't run very often :


texts: Generate sample data for an Oracle database

texts:
Good morning,

What free [or trial] software can I use to generate sample data for an Oracle database?

I've been trying with various tools, but none work.

Thanks for all suggestions,

Alec Taylor


texts: Postgres - indexing multi-tenant database

texts:
Our multi-tenant application uses Postgres database. The application was created many years ago, and now we are seeing some performance issues when load increases.
Every database table we have has at least two indexes - Id (PK) and TenantId (FK). On every select we added TenantId filter, and when we fetch an item by Id there also TenantId is added in the where clause.
Now, my question is that did we do it wrong. Instead of adding two indexes we should have added index on (TenantId, Id) and that would have improved our query performance? Would this have improved both the scenarios - when selecting using tenantId and tenantId + Id?


texts: three tables relation order is correct but values are false

texts:
i have those tables

     table1
|  id  |  name  |
|  1   |  axe   |
|  2   |  bow   |
|  3   |  car   |
|  4   |  dart  |

        table2                                        table3
|  t1_id  |  number  |                        |  t1_id  |  letter  |
|  1      |  5       |                        |  1      |  a       |
|  1      |  6       |                        |  1      |  b       |
|  1      |  2       |                        |  1      |  c       |
|  2      |  2       |                        |  2      |  a       |
|  2      |  2       |                        |  2      |  c       |
|  2      |  3       |                        |  2      |  r       |
|  3      |  8       |                        |  3      |  y       |
|  3      |  3       |                        |  3      |  i       |
|  3      |  1       |                        |  3      |  a       |
|  4      |  8       |                        |  4      |  a       |
|  4      |  9       |                        |  4      |  b       |
|  4      |  10      |                        |  4      |  c       |

and table1(id) is linked with table2(t1_id), table3(t1_id)

i run it to get them order by the highest letter_count match then by the highest average_number match to get this proper result http://www.sqlfiddle.com/#!9/69086b/8/0

SELECT
  t1.id,
  t1.name

FROM
  table1 t1

INNER JOIN
  table2 t2
    ON t2.t1_id = t1.id

LEFT JOIN
  table3 t3
    ON t3.t1_id = t1.id
      AND t3.letter IN ('a', 'b', 'c')

GROUP BY
  t1.id

ORDER BY
  COUNT(t3.letter) DESC,
  AVG(t2.number) DESC

|  id  |  name  |
|  4   |  dart  |
|  1   |  axe   |
|  2   |  bow   |
|  3   |  car   |

and everything is working ok

but when i wanted to check if there is any problems with the query i decided to check the letter_count and avg_number so i used this query

SELECT
  t1.id,
  t1.name,
  COUNT(t3.letter) AS letter_count,
  AVG(t2.number) AS avg_number

FROM
  table1 t1

INNER JOIN
  table2 t2
    ON t2.t1_id = t1.id

LEFT JOIN
  table3 t3
    ON t3.t1_id = t1.id
      AND t3.letter IN ('a', 'b', 'c')

GROUP BY
  t1.id

ORDER BY
  letter_count DESC,
  avg_number DESC

what i expected the result to be was

|  id  |  name  |  letter_count  |  avg_number   |
|  4   |  dart  |  3             |  9            |
|  1   |  axe   |  3             |  4.3333333333 |
|  2   |  bow   |  2             |  2.3333333333 |
|  3   |  car   |  1             |  4            |

but the result i got was http://www.sqlfiddle.com/#!9/69086b/3/0

|  id  |  name  |  letter_count  |  avg_number   |
|  4   |  dart  |  9             |  9            |
|  1   |  axe   |  9             |  4.3333333333 |
|  2   |  bow   |  6             |  2.3333333333 |
|  3   |  car   |  3             |  4            |

am i supposed to be worried on how the letter_count is multiplied three times on the future or does this just happens when i want to select the values and nothing bad is going to happen if i just kept it in ORDER BY?

i just want them to be ordered correctly which is what i got but checking the values showed me that letter_count is multiplied many times so i am confused about its performance or the letter_count can be ignored and the performance wont be affected?


texts: Using fulltext search to find a sequence of digits

texts:
We have a column of ten digit numbers (with leading zeros). We are trying to find rows that have a specific sequence of digits.

For example, all rows containing "5088" in any place in the column.

Can full text search (FTS) help us in this case?

We tried other methods such as LIKE, but the performance was very poor.


texts: In Job Step > Properties, only see two tabs: Package and Configuration — EncryptSensitiveWithPassword

texts:
I am going to echo another question almost exactly, only my question has a different objective:

In Job Step > Properties, only see two tabs: Package and Configuration

I am trying to set a job step that executes a package requiring a package password, since the package protection level is set to EncryptSensitiveWithPassword. The job is running on SQL Server 2014. I see all kinds of examples of how to do this, where the job step properties page has 9 tabs (Set Values, Verification, Command Line, General, Configurations, Command Files, Data Sources, Execution Options and Logging). Command Line allows me to enter the password (with the /DECRYPT command line option).

When I look at my job step, logged in to the server as an Admin, running SSMS as administrator, all I see are two tabs: Package and Configuration. I deployed the packages using the Project Deployment method. How do I get those other tabs to show up?

(My ultimate goal is to give the SQL Agent who runs the job access to a MySQL DB password, and I understand setting the package protection level to EncryptSensistiveWithPassword is the correct way to do that, but I cannot find another way to provide the package password.)


texts: What could cause problematic queries to disappear from QueryStore?

texts:
I have a log table of "Execution Timeout Expired" error messages. The timeouts occurred at a certain time (say 11/17, 1:23 pm). A couple weeks ago, I was able to go into Query Store, "Top Resource Consuming Queries", click on "Configure", go to the Custom time setting, and pick that exact minute where the timeouts occurred. I found 1 procedure and 1 function that were consuming lots of resources - a very clear spike.  I can't remember exactly what it was (CPU, Logical reads) but I think it may have been all of them.
And there seemed to be an obvious problem that I could fix (basically the function was in the join clause, and the SP could be re-written to avoid the function).
But now I'm trying to validate this finding, that the specific sp and function were the problem. And when I do, in Query Store, what I believe is the exact same thing I did a few weeks ago, the sp and function doesn't show up at all.
Is there something obvious I'm missing? Could something have been dropped from Query Store? Other stored procedures and functions are still showing up for that time period, just not the ones that showed as spiked when I checked last time.


texts:
I have considered TDE and cell level encryption mechanism for securing my database, however these two cannot fully satisfy my requirements.

I've found that PGP may help me, but it is mainly used for mail services.

What is the technical feasibility for implementing PGP for my SQL server database, is there any possibilities for that?

TDE does not satisfy my requirements because it only protects data-at-rest.  I need to protect the data from an attacker who has access to the database while it is running on the server containing the encryption certificate, as well as the data contained within backups.

I'd also like to have some users able to see the decrypted values, while some other users cannot ever see the decrypted values.

I have implemented master-slave Replication, and need to enable security without affecting the replication.
Approximately 80% of coding has been completed, so its really hard to change the queries and stored procedures.
I need to enable security for selected tables only (payments, customer details, password, etc.)
I need to secure data from injection.


texts: Deploying dacpac through sqlpackage.exe with NETWORK SERVICE user

texts: I have a database release pipeline in Azure DevOps Server which deploys our dacpac files through Powershell commands that run the sqlpackage.exe commands to publish the database. I am using the NETWORK SERVICE user to publish the databases to our DEV and QA environment as I wanted to avoid putting the username and password on the release pipeline. We will be deploying these dacpac files to Production soon and the production server is outside our network. Are there security concerns with using the network service user to deploy the dacpac files on a production environment? What alternatives are there besides putting the username and password in secret variables?


texts:
Is there any command line tool that I can use on a DB2 database to dump a specific set of tables?

The problem is that I work in an application that its database tables was created in an existing schema alongside another application tables what would cause a lot of concurrency with these applications.

So after long discussion with our client he finally decide to attend our request to separate our database server since our application grows very large (tables with 14kk rows) and have lots of simultaneous request.

I've searched through DB2 documentation without any success. All the methods I've found out doesn't seems to solve this specific problem.

I read about the Export command. And couldn't find anything the do what we need.

Thanks in advance.

Edit

As asked in comments dump a specific set of tables means that we need to dump some tables from the database not all of then. Say that we have on this database about 5k tables and about 200 of then belongs to our system, so how would we dump only those 200 tables?

On the Export command documentation (mentioned above) there is no mention about exporting a group of tables only a data set (through a SQL command) of tables which does not meet the requirements.


texts: Performance issue in 12c - Additional Full table scan after index range scan

texts:
I am trying to improve the performance of a procedure which we recently moved from 11g to 12c. I am simply using a refcursor which gets the result set from an underlying query. In 12c I am having a significant performance hit. The query running time in 11g was less than 3 seconds while in 12c it is taking more than 2 minutes.

In 12c, the stats are not gathered for the last 1 month(testing environment).

When I compared the execution plan, the only difference I could see was that there is a full table scan following an index range scan happening for a table.
This full table scan is not present in 11g.

Following is the plan snippet where this is happening. Thanks in advance for any help.

18 STATISTICS COLLECTOR
  17 VIEW       VIEW SYS.VW_1 Cost: 137  Bytes: 81  Cardinality: 9
    16 HASH UNIQUE  Cost: 137  Bytes: 432  Cardinality: 9
     15 FILTER
      14 CONNECT BY WITH FILTERING (UNIQUE)
       6 TABLE ACCESS BY INDEX ROWID TABLE TAB1 Cost: 2  Bytes: 27  Cardinality: 1
        5 INDEX UNIQUE SCAN INDEX (UNIQUE) TAB1_PK Cost: 1  Cardinality: 1
       13 HASH JOIN  Cost: 5  Bytes: 312  Cardinality: 8
        11 NESTED LOOPS  Cost: 5  Bytes: 312  Cardinality: 8
      8 STATISTICS COLLECTOR
     7 CONNECT BY PUMP
       10 TABLE ACCESS BY INDEX ROWID BATCHED TABLE TAB1 Cost: 3  Bytes: 216  Cardinality: 8
     9 INDEX RANGE SCAN INDEX TAB1_IE2 Cost: 1  Cardinality: 8
      12 TABLE ACCESS FULL TABLE TAB1 Cost: 3  Bytes: 216  Cardinality: 8


texts: How can I dump the schema of a SQL server database on Linux?

texts:
UPDATE:
My goal is to get a set of SQL commands that can b executed and would recreate the database that I made including all the resources such as tables, indexes, trigger functions, privilege grants etc - same as what the MySQL and POstgres commands below do.  I'm working on an open source project and I want to be able to give a sql file to the end user so all they ned to do is run it to get their database correctly set up.
On MySQL:
mysqldump --no-data somedatabasename dump_schema_mysql.out

On Postgres:
pg_dump -s somedatabasename > dump_schema_postgres.out

I can't seem to find an equivalent for SQL server on Linux, though surely such a common task must be easily done?
I have the sqlcmd utility running ok:
ubuntu@ubuntu:~$ sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 17.6.0001.1 Linux
Copyright (C) 2017 Microsoft Corporation. All rights reserved.

usage: sqlcmd            [-U login id]          [-P password]
  [-S server or Dsn if -D is provided]
  [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-D Dsn flag, indicate -S is Dsn]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-g enable column encryption]
  [-G use Azure Active Directory for authentication]
  [-? show syntax summary]
ubuntu@ubuntu:~$ sqlcmd -?


texts: How can I prove that the following T-SQL is bad for performance?

texts:
I have recently inherited a codebase with a large amount of Stored Procedures.  The system they are supporting is encountering numerous performance problems which I am looking in to.

A number of the Stored Procedures have a pattern like this:

Create Temp table
Build up dynamic SQL query to insert a bunch of records, e.g.

DECLARE @sql VARCHAR(MAX)
SET @sql = 'INSERT INTO @tempTable
SELECT SomeColumn, SomeColumn2, SomeColumn3, etc FROM MyTable'

IF @someParam = [SomeValue]
    SET @sql = @sql + 'WHERE SomeColumn = [SomeValue]';

IF @someOtherParam = [SomeOtherValue]
    SET @sql = @sql + 'WHERE SomeOtherColum = [SomeOtherValue]';

Execute this dynamic sql

EXEC(@sql);

Select from the temp table and bring in a bunch of additional information to return to the client.

SELECT

   ...

FROM

   @tempTable
   INNER JOIN ...

My immediate thoughts are:

There is dynamic SQL, so no cache plans, meaning plans generated every time.
There is an INSERT SELECT pattern, so table locking is more likely to be an issue.

I have re-written some of the Stored Procedures in this way instead:

    SELECT

       ...

    FROM

        MyTable
        INNER JOIN ...

    WHERE

    (
        @someParam != SomeValue
        OR
        SomeColumn = SomeValue
    )
    AND
    (
        @someOtherParam != SomeOtherValue
        OR
        SomeOtherColumn = SomeOtherValue
    )

From comparing execution plans and client statistics in SQL Management Studio, I have not sped the Stored Procedures up so I am apprehensive about suggesting wholesale re-writes of all Stored Procedures.

I am trying to set up some profiling of a live customer scenario, but as yet have been unable to prove my thoughts.

Can anyone offer any confirmation of the theory behind my thoughts, or any better ways of proving my suspicions?

The problem is I have read that dynamic SQL is not always a closed case - i.e. it depends on how it is used.  My understanding of locking also falls down at the fact that nowhere can I get 100% confirmation of how this type of INSERT SELECT will lock tables.


texts: What is the best data type to store prices upto 8 decimals + indicators in SQLite?

texts: I am developing a trading system which stores data in SQLite, consisting of OHLC and volume. I would also like to store some indicators like RSI etc I read the other answer which indicated integers as the data type. In my case I am looking at 8 digit precision which would slow down the calculations if I were to multiply every value by 1e8 Any recommendations are appreciated!


texts: After server move a query doesn't work anymore

texts:
I've moved an ASP+SQLServer application from an old server to a new one.
The old one was a Windows 2000 server with MSDE, and the new one runs Windows 2008 with SQL 2008 Express.

Everything is ok, even a little faster, except just one function whose ASP page gives a time out.
I've tryed the query within that page in a management query windows and it never ends. In the old server it took about 1 minute to be completed.

Here is the query:

SELECT DISTINCT TBL1.TBL1_ID, REPLACE(TBL1_TITOLO, CHAR(13)+CHAR(10), ’ ’), COALESCE(TBL1_DURATA, 0), TBL1_NUMERO, FLAG_AUDIO
FROM SPOT AS TBL1
INNER JOIN CROSS_SPOT AS CRS ON CRS.TBL1_ID=TBL1.TBL1_ID
INNER JOIN DESTINATARI_SPOT AS DSP ON DSP.TBL1_ID = TBL1.TBL1_ID
WHERE DSP.PTD_ID_PUNTO = 1044
AND DSP.DSP_FLAG_OK = 1 AND TBL1.FLAG_AUDIO_TESTO=1 AND TBL1.FLAG_AUDIO_GRAFICO=’A’ AND CRS.CRS_STATO>2
OR TBL1.TBL1_ID IN (SELECT ID FROM V_VIEW1 WHERE ID IS NOT NULL AND V_VIEW1.ID_MODULO = 403721)
OR TBL1.TBL1_ID IN (SELECT TBL1_ID FROM V_VIEW2 WHERE V_VIEW2.ID_PUNTO = 1044)
ORDER BY TBL1_NUMERO

I've tried to transform the two views in the last lines into tables and the query works, even if it's a little slower than before. I've migrated the database with its backup/restore function. Could it be an index problem? Any other suggestions?


texts: How to make middle and suffix matching using full text search?

texts:
I'm trying to find a string on a database using FTS on Postgres:

select name
from users
where to_tsvector('simple', name) @@ to_tsquery('simple', 'ad:*')

But the :* search only the words start with ad.

I found this question Get partial match from Get partial match from GIN indexed TSVECTOR column column.

But it doesn't mention how to solve the problem of middle and suffix matching using FTS?


texts: MySQL - INSERT INTO ... SELECT ... UNION

texts:
I'm having an issue with a fairly complex query where I need to store the ids from one table (based on heavy criteria) into a narrow, alternate table for future reference, but I keep running into a bad sql construct error.

If I run the following query by itself it works fine:

(SELECT DISTINCT a.id FROM accounts AS a
WHERE a.createdate >= date_sub(now(), INTERVAL 1 YEAR)
AND a.email NOT LIKE '%gmail.com')
UNION DISTINCT
(SELECT DISTINCT a.id FROM accounts AS a
WHERE a.createdate < date_sub(now(), INTERVAL 1 YEAR)
AND a.email NOT LIKE '%yahoo.com') AND a.email NOT IN
(SELECT email FROM inactive_clients));

However, if I try to run same query with like this:

INSERT INTO my_backup_table (account_id) VALUES ($aboveQuery);

I get the following error:

MySQL server version for the right syntax to use near 'UNION DISTINCT (SELECT DISTINCT a.id FROM accounts at line 1

I tried any number of parenthetical permutations and still received the same error.


texts:
I want to insert a whole table's contents to another database which has the same table name.

How can I achieve this?

I am using SQL Server Express 2008 R2.


texts: More Databases Per Sql Server 2005 Instance

texts:
Our company currently hosts about 400 databases per SQL Server 2005 instance.  500-600 databases and we see performance start to deteriorate.
Database size ranges from 50mb to 40-60gb in the extreme, with probably an average of maybe a few GB.

What are some good metrics to collect and how would we get these to determine our bottleneck?

Is it a performance limitation of SQL Server to have too many databases?  Is it all of the cached procedure plans for hundreds of stored procedures for hundreds of databases?  Is it inefficient queries? Or a combination?

What is likely to help us increase the number of databases per server the most?  All of the schemas are identical and the databases separate each customer's data.  Maybe refactoring everything so we can combine multiple customers into one db and filter by customer, or optimizing our queries to the extreme? Or maybe its just a SQL Server limitation?


texts: How to turn JSON array into Postgres array?

texts:
I have a column data of type json that holds JSON documents like this:

{
    "name": "foo",
    "tags": ["foo", "bar"]
}

I would like to turn the nested tags array into a concatenated string ('foo, bar'). That would be easily possible with the array_to_string() function in theory. However, this function does not accept json input. So I wonder how to turn this JSON array into a Postgres array (type text[])?


texts: Am I doing a security breach if I am connecting a MS-Access front end (multiple computers) to a SQL Server back-end?

texts:
I have a MS Access frontend that I would like to install on 5-7 computers, so they can access a SQL Server stored on a shared network drive:

If possible, is it safe and can my data be corrupted? Otherwise, how I have 5-7 users access the SQL Server simultaneously by using an interface?

Thank you very much for your time!


texts: Calling Sproc From Another Server - runs as SA

texts:
I have a sproc on ServerA that calls another sproc on ServerB

The servers are connected through linked server.

When the sproc gets called on ServerA it is called by the user login. However, then on ServerB, the 2nd sproc is called by SA.

I would have thought that the user credential would have passed through to ServerB rather than SA?


texts: Galera cluster without having to specify all hosts on wsrep_cluster_address

texts:
While evaluating the galera cluster, on the documentation it mentions that all cluster members should be listed on the "wsrep_cluster_address" setting...

This doesn't seem very scalable to me as adding a new node would mean having to reconfigure all existing nodes.

Is it so? or is there any way of not having to specify all nodes on that variable?


texts: mysql set auto increment to 1 with foreign key

texts:
I want to set auto increment to 1 by below command, but it doesn't works:

ALTER TABLE tablename AUTO_INCREMENT = 1

I searched and found out that I should use TRUNCATE TABLE. But this command fails due to foreign key constraint. Any idea?


texts: PostgreSQL: How to backup only One Schema from a database and restore it on another server

texts:
I have a database named "A" which has two schemas "B" and "C".
I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres.
Do I have to create a new DB on new sever as of name "A" and then restore the Schema "B" on it.
pls help with commands.

--- information from comment on Peter's answer:

I want to backup and restore Schema "B " + data. Secondly I forgot to mention that Postgresql 9.1 running on Ubuntu 12.04