texts: Custom ID Numbering - fast way on insert?

texts:
As start: this is for an ETL stored procedure that is serialized, so paralellism is not of concern.

I need to assign custom ID numbers for markers during loads. I can not use an identity field, because the ID numbers are to be unique by a "bucket number" - essentially another numbered field.

I currently use the following code:

DECLARE @idRunner smallint
SELECT @idRunner = ISNULL(MAX([Id]),0)
    FROM sim.[Variable]
    WHERE [BucketRef] = @simBucketNo

DECLARE variable_cursor CURSOR FOR
    SELECT DISTINCT p.[Variable]
        FROM simstg.[Parameter] p
        LEFT OUTER JOIN sim.[Variable] v ON (p.[Variable] = v.[Code])
        WHERE p.[BucketRef] = @stgBucketNo
            AND v.BucketRef = @simBucketNo
            AND v.Code IS NULL
OPEN variable_cursor
DECLARE @variable VARCHAR(64)

FETCH NEXT FROM variable_cursor INTO @variable
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @idRunner = @idRunner + 1
    INSERT INTO sim.[Variable] ([BucketRef], [VariableNo], [Code])
        VALUES (@simBucketNo, @idRunner, @variable)

    FETCH NEXT FROM variable_cursor INTO @variable
END

CLOSE variable_cursor
DEALLOCATE variable_cursor

and I Do not like it. It uses cursors which I prefer to avoid.
(Sidenote: this code is untested)

Is there a way to do this more efficient, without a cursor? IR numbers must increase from the highest used when new elements are added. Multiple buckets exist (by bucket no) and have their own numbering.

I am always only going to process data for one bucket at a time (one bucket in sim, one in simstg).


texts: MS-SQL index not using all the available columns as seek predicates

texts:
I have a SQL Server query on a large table (>17 million rows) that sometimes does not populate all the predicates before performing an index seek, which results in not filtering out enough rows and slowing the query down significantly (hours).

Microsoft SQL Server 2005 database is used and this SQL query is generated by Hibernate.

Here is how the simplified query looks like:

SELECT MAIN_TABLE.FIELD_1,
       MAIN_TABLE.FIELD_2,
       MAIN_TABLE.FIELD_3,
       MAIN_TABLE.FIELD_4,
       TABLE_1.FIELD_1,
       TABLE_1.FIELD_2,
       TABLE_2.FIELD_1,
       TABLE_2.FIELD_2,
       TABLE_3.FIELD_1
FROM MAIN_TABLE
     INNER JOIN TABLE_1 ON MAIN_TABLE.TABLE_1_ID = TABLE_1.ID
     INNER JOIN TABLE_2 ON MAIN_TABLE.TABLE_2_ID = TABLE_2.ID
     LEFT OUTER JOIN TABLE_3 ON MAIN_TABLE.TABLE_3_ID = TABLE_3.ID
WHERE MAIN_TABLE.SOME_ID IN (SELECT SOME_ID FROM PARTITION_VIEW
                             WHERE PARTITION_VIEW.SOME_FIELD_1 = [VALUE_1]
                             AND PARTITION_VIEW.SOME_FIELD_2 = [VALUE_2]
                             AND PARTITION_VIEW.SOME_FIELD_3 = [VALUE_3])
AND MAIN_TABLE.DATE BETWEEN [DATE_RANGE_1] AND [DATE_RANGE_2]
AND TABLE_1.STATE = [STATE]

The PARTITION_VIEW is a UNION of materialized views that all have index on columns  SOME_FIELD_1, SOME_FIELD_2, and SOME_FIELD_3.

In the MAIN_TABLE, we have an index on columns DATE and SOME_ID and this is the index that gets utilized which is correct.

When I checked the query plan from the SQL Server Management Studio, I found that in some cases such as when the date range is large (one year), the predicate used is the DATE column only. Then only after all the rows are within the given date range are found, the result set is filtered out by the given SOME_IDs.

It seems like the query optimizer did not pick the best query plan available.

When the exact query is directly executed from SQL Server Management Studio, everything performs correctly. This suboptimal query plan is used only when the query is executed from the application through Hibernate.

One thing I tried was to create actual table PARTITION_TABLE which copied over all the data from the PARTITION_VIEW and retrieved SOME_IDs from the PARTITION_TABLE, then the index on MAIN_TABLE was using all the predicates (SOME_ID, DATE). I want to keep using the view if possible since it automatically reflects the changes.

Is it possible to keep using the union of materialized views and ensure that the index on the MAIN_TABLE will always retrieve the SOME_ID on the where clause first so that it can be used as part of the predicate in the index?


texts: Stored procedure to check if username exists

texts:
I'm trying to write a stored procedure

So far I have this, but it always returns 1

CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS`(IN `USERNAME` VARCHAR(64) CHARSET utf8mb4)
SELECT CASE WHEN MAX(user_id) IS NULL THEN '0' ELSE '1' END User_exists
  FROM `dbname`.`tablename`
 WHERE `username` = USERNAME

The goal is to perform this via AJAX so the user can see whether registration is likely to succeed before submitting their registration form


texts: json functions: double quotes added when going through a variable

texts:
Im stuck with what it seems simple question, when I create a JSON using the MariaDB/MySQL json functions going through variables, it adds double quotes which is annoying. However, if I don't go through variables, it works fine. Unfortunately I have to go through variables as I'm building my JSON using a recursive function:

without variables:

declare temp_json JSON;
set temp_json = json_object('data',json_object("jsonapi",json_object("version", "1.0")));
select temp_json

OUTPUT

{"data": {"jsonapi": {"version": "1.0"}}}

with variables

declare temp_json JSON;
declare jsonapiheader JSON;
set jsonapiheader = json_object("jsonapi",json_object("version", "1.0"));
set temp_json = json_object('data',jsonapiheader);
select temp_json

OUTPUT

{"data": "{\"jsonapi\": {\"version\": \"1.0\"}}"}`

So how do I do like 2) but with the output of 1) ?

my system:
MariaDB 10.2.9


texts: Azure SQL database query performance degradation

texts:
I have a Azure SQL database (compatibility level = 150). I'm running into a performance problem executing a database query.
So after I've rebuilt all my indexes, the query runs fast. When testing, with different parameters, all of a sudden it slows down. The same query with the same parameters is fast in the beginning, and slow after I do around 50 tests.
Now when I purge the Query store data via the UI or via SQL:
ALTER DATABASE [mydb] SET QUERY_STORE CLEAR;

the performance is back.
I've tried all settings on the Query Store, the problem remains, only deleting the data collected gives me back the initial performance.
Ok, so executing DBCC FREEPROCCACHE WITH NO_INFOMSGS; had the same performance impact as clearing the query store.
The query is built form my data model in Power BI (direct query). It can be fast for all possible parameter combinations. I tested all of these when the cache was recently cleared.
Power BI sends the query to Azure SQL db with a syntax for parameterized queries.
I just becomes slower and slower.
So is it the right solution, optimize for unknown, knowing that it can be fast for that same parameters?
Underneath the that is called from the app, I have 3 table-valued functions. I changed the 3 off them with OPTION (OPTIMIZE FOR UNKNOWN), update the statistics and rebuild all indexes. I must say the performance stays, which is good, although it's still a bit black magic for me.


texts: What's the data flow between Pgpool, Postgresql and client

texts:
I'm new in Pgpool.

I want to ask the question, what is the dataflow between Pgpool, Postgresql when client querying data

1/

Client query data to Pgpool,
PgPool ask Postgresql
Postgresql calculate result, return data to PgPool
PgPool transfer data to Client

2/

Client query data to PgPool
PgPool ask Postgresql
Client and Postgresql make a connection
Postgresql calculate result,
Postgresql directly transfer data to Client

3/ Other schema ?

Could you please show me the right answer

Many thanks


texts:
I have a login issue here. I created a new user login in SQL Server 2014. I tried to connect to my database using this new login and the below error pops up

  Message
  Error: 18456, Severity: 14, State: 58.

I searched for the error and found out that I have to be in mixed authentication mode. I went to properties of the server and changed it to mixed mode from security tab, restarted SQL Server and still the same issue. I tried to investigate more and when I tried the below query

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode]

It shows that I'm still in Windows Authentication mode, although the fact is that I changed it to mixed mode.

I am still stuck with this issue and am not able to identify the problem. Can anyone help me out?

I am connecting from SSMS, not code.

Right clicking the server and going to properties -> security, shows SQL Server and Windows.


texts: Please explain what does "for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)')" do in this code

texts:
In this code I am converting the subjects(columns) English , Hindi , Sanskrith , into rows

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

 select @colsUnpivot
               =stuff(
         (select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)'),
        1, 8, ''
    );

print @colsunpivot

select  @query
              = 'select Name, Subject,Marks
                 from result2
                 unpivot
                        (
                          marks for subject in (' + @colsunpivot + ')) as tab'

exec sp_executesql @query;

Please explain what does for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)') do here .


texts: Is there a postgres function documentation pattern that is similar in concept to javadoc?

texts: I am mostly a Java developer. Today I am writing some postgres functions. Out of habit I keep trying to document these functions using a Javadoc-like comment style describing the function parameters and return type. Is there an official or widely supported pattern for documenting postgres functions?


texts: Modelling a database involving Customers, Projects, Employees, Drawings and Revisions

texts:
I am working on a database based application and am also looking for some feedback on how to organize the relevant tables. The application handles customer project info and the projects technical drawings.

The scenario

Each Project is made up of n quantity of Drawing Sets, which have n quantity of Drawings with n quantity of Revisions each  made by Employees "a", "b", and "c".
Each Project needs to be associated with the Customer it belongs to  and the Employees who worked on the project, both of which have arbitrary ID Numbers.

My considerations

Having "Index Tables" to link a customers and employees table with a projects table seems like an obvious choice but since the projects essential have a nested structure I have become unsure about how to continue.

Information structure

If working up-to-bottom, a Revision would have a unique ID structured as

(Project No.)-(Set No.)-(Drawing No.)-(Revision No.)

and a Drawing ID would be

(Project No.)-(Set No.)-(Drawing No.) and so on.

The Numbers are made up as follows:

Project: Sales Order Number, Line Item, Release
Drawing Set: Type, Set Sub Type
Drawing: Page Number, Section Number
Revision: Rev Number, Minor Rev, Build Rev

Further considerations

All the Projects would go into a table just fine with a compound primary key being made up of the Sales Order Number, Line Item, and Release, the Project ID. But If a Drawing Set's primary key was a compound key made up in part by a compound key from another table the number of columns start to get out of hand.

This is where I get torn with using "Index Tables" to link Drawings with Drawing Sets and Revisions, or make a new drawings table and a revisions table for each Drawing Set or just for each Project. I have read that surrogate primary keys would be better than natural keys but for some reason I still feel like having project-specific tables is better than grouping all Drawings from all Projects into a single table. I'm fairly new to this whole database thing so I'm not even sure if another solution even exists but some feedback on how you would approach this would be helpful.


texts: Mysql separate username from client table for performance and use myisam for it

texts:
I'm creating a database for a very heavy messenger like Telegram ...
I decided to use a different table for client-username and separate it from the clients table.

This is My 'client' table:

InnoDB (because we are going to have too many updates over select !)
ascii_general_ci

     ID       -> int (unsigned parimary auto-index)
     password -> char (64) (not-null ('char' get us better performance over 'varchar' since password-length is fixed-length (64) (because of hash))
     ...

This is my 'client_username' table:

MyISAM (because we are going to have too many SELECT over update !)
ascii_general_ci

     client_ID       -> int (unsigned unique)
     client_username -> char (16) (using 'char' for better performance duo to it's fixed-length type. also it's better to use 'char' type in MyISAM type)

I separated username because the client table is InnoDB and it's not good as MyISAM in SELECT.  So I separated username into a table with MyISAM type.  Here we have the best performance in searching for username, and also I did it because I want to choose 'char' type for my username and I heard 'char' type is faster than varchar nly in 'MyISAM' type.  Am I right about all of these points?


texts: Why are there differences in data when comparing table to itself? TSQL

texts:
I'm comparing two tables that should have the same data.  I've got a couple of different queries that are supposed to return rows that don't match.  However, the queries return rows that DO match.  I'm not sure why.  Here is an example:

select
which = 'TableA',
*
from (
select * from compareTempA
except
select * from compareTempB
) x
union all
select
'TableB',
*
from (
select * from compareTempB
except
select * from compareTempA

) x
order by dateindex, empid, serviceSales, productSales

For some reason I get results like the following:

As you can see these rows appear to match.  I've also tried:

select sum(case when A.productSales = B.productSales then 0 else 1 end) from
compareTempA A
join compareTempB B
on A.dateindex = B.dateindex
and A.empid = B.empid
and A.storeid = B.storeid

To which I get 3011.  I'm baffled because the data is supposed to be the same in both tables.  The only thing I can think of is that I formed the two tables by inserting into each of them in the following way:

insert into compareTempA
select * from someView

And I did the same for compareTempB.  I tried truncating the tables first and reinserting.  Please help!


texts:
I am wondering how to store trees on a physical disk, for perhaps a tree-oriented database like an XML database.

Wikipedia states about XML databases:

  ...custom optimized data structures are used for storage and querying. This usually increases performance both in terms of read-only queries and updates. XML nodes and documents are the fundamental unit of (logical) storage, just as a relational database has fields and rows.

But doesn't explain what the actual data structure is for storing the tree on disk. Wondering if one could explain how that works. It seems you could store each "document" as its own file such as this as a file:

<a>
  <b>
    <c>
      <d>Hello</d>
    </c>
    <c>
      <x>World</x>
    </c>
    <e>
      <f ref="b:0/c:0/d"></f>
    </e>
    <g>
      <f ref="b:0/c:0/x"></f>
    </g>
  </b>
  <b>
    <c>
      <d>Hello2</d>
    </c>
    <c>
      <x>World2</x>
    </c>
    <e>
      <f ref="b:1/c:0/d"></f>
    </e>
    <g>
      <f ref="b:1/c:0/x"></f>
    </g>
  </b>
</a>

And then you would have a query "give me all b/c/x where b/c/d matches Hello", or just "give me all nodes below c". The ref="b:0/c:0/d" is a pointer to a specific node, something like that.

Basically I'm wondering, in order to accomplish these sorts of things, the data structure(s) to use to store XML or any tree structure on disk.

Maybe instead of storing the whole XML document in a file, you end up storing something like this:

a/b/c/d Hello
a/b/c/d World
a/b/e/f ref=b:0/c:0/d
a/b/g/f ref=b:0/c:1/d
...

That's pretty much what I'm wondering, how the data would look in a file on disk at a physical level. Not really sure what to search for. XML database data structures and such doesn't return much.


texts: SQL Server 2008: What are the English Word Breakers?

texts:
I'm trying to replicate what SQL Server 2008 is doing when generating full-text indexes, but I'm having a hard time finding a list of which characters SQL Server 2008 considers to be word breakers.

Is there a list out there specifying which characters SQL Server 2008 will break words on for full-text indexes for English?


texts: InnoDB Cluster Read-Only Lag Behind Writer

texts:
For software testing purposes, I'm using MySQL v5.7 and MySQL Router with a sandbox deployment of an InnoDB cluster with one read-write instance and two read-only instances.

I'd like to be able to determine the amount of time between when data is written to the read-write instance and when that data is available in a read-only instance.  From what I read, it sounds like SHOW SLAVE STATUS it what I'm looking for; it returns Seconds_Behind_Master.  Unfortunately that command returns zero rows.

The cluster configuration is whatever the MySQL wizard created for me which appears below for one of the instances.  The other two instances have different port numbers.

[client]
protocol = TCP
port = 3310
user = root

[mysqld]
transaction_write_set_extraction = XXHASH64
disabled_storage_engines = BLACKHOLE,FEDERATED,ARCHIVE
binlog_checksum = NONE
gtid_mode = ON
server_id = 1746145354
auto_increment_offset = 3
basedir = C:/Program Files/MySQL/MySQL Server 5.7
port = 3310
datadir = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/sandboxdata
log_error = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/sandboxdata/error.log
pid_file = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/3310.pid
log_bin
loose_mysqlx_socket = mysqlx.sock
log_slave_updates = ON
relay_log_info_repository = TABLE
secure_file_priv = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/mysql-files
master_info_repository = TABLE
binlog_format = ROW
plugin_load = mysqlx.dll
enforce_gtid_consistency = ON
loose_mysqlx_port = 33100
socket = mysqld.sock
report_port = 3310
log_syslog = OFF

Is there a setting here that's my problem or does SHOW SLAVE STATUS just not work with this setup?


texts: Database mirroring with fixed set of tables without sql server replication

texts:
We have requirement of mirroring the database with some fixed set of tables. We do not want to use sql server replication for that purpose.

Do we have any solution for that or any software for this purpose which we can use for the replication of the set of tables to remote location?

We have some set of tables around 6 to 7 that needs to be replicated in remote location so that we can have offline website setup when our live system went down. Which only shows important bit of information online for temporary purpose.


texts: How can I import my server list from pgAdmin 3 to pgAdmin 4?

texts: I wonder whether there is any way to import my server list from pgAdmin 3 to pgAdmin 4 (so that I don't have to create servers one by one again).


texts: How to shrink over sized logs in Database Mirroring?

texts:
I've searched the forums here at dba.se and other places on the Net and haven't found a specific answer.  I know this question looks similar to many, but the ones that I've found only address how to shrink a log file, which I already know how to perform.

I happen to be in one of those unique positions that a log shrink is necessary.

My specific question is:

When I perform a log shrink on the primary in a mirrored set, do I then failover and perform a log shrink on the "new" primary?

Or does this somehow replicate the changes to the secondary, making the manually failover unnecessary?


texts: I'm trying to run an oracle database in virtual box under oracle linux

texts:
I'm trying to run a oracle DB in VirtualBox and access it from my host mac OSX , i am using port forwarding to access my guest, I can telnet my guest on the port, however when i try to connect from SQL developer i get IO error got minus one from a read call.

have googled around, one potential gotcha was specific ips allowed in the sqlnet.ora config - not the problem,  any idea this is really annoying me now


texts: How can SQL server AlwaysOn AG can be used as a DR feature?

texts: I'm using AlwaysOn ag feature as a HA feature where automatic failover is possible.. I was thinking to log shipping as DR but there is potential data loss in that.. thinking of using ag as a dr(async).. is it possible? how will it work?


texts: MYSQL backup options using mysqldump and mysqlbackup

texts:
I'm currently using mysqldump to backup mysql database.

I heard that mysqlbackup offers a much much better performance.  Unfortunately it requires MySQL enterprise version which is pricy.

A few questions:

Does a database backed up by mysqlbackup require the user to have MySQL Enterprise Subscription to restore it?
Are there any free or low cost alternatives that offer better performance than mysqldump?


texts:
I have a table of Courses and it has a foreign key to Class_ID.

A class teaches several courses dividing into several terms. So, Terminal is another table, it has a foreign key Class_ID to class table.

Each terminal (or term) has courses that need to be completed in that term. So, there is another join table TermCourse which has foreign_key to both Course table and Terminal table.

The problem is, I can input into TermCourse table a course, which is not listed for this class, hence shouldn't also be included for any of the terminal of this class.

How do I enforce that only a subset of the courses are eligible to be included into TermCourse, namely those ones who are already included for this class to which this terminal belongs to?

The tables are

Class
-------
Class_ID (Primary key)
Description

Course
-------
Course_ID (Primary Key)
Course_Name
Class_ID (Foreign Key to Class)

Terminal
--------
Terminal_ID (Primary key)
Terminal_No
Class_ID(Foreign key to Class)

TermCourse (Join Table)
--------
Terminal_ID (Foreign key to Terminal)
Course_ID (Foreign key to Course)

Here is a diagram of this ER design


texts: Mysql : Maximum number of connections on a per user basis - Possible?

texts:
Is it possible to set a maximum number of open connections that a user (NOT all users combined, just one specific user) may have at a given time in mysql?

Eg:

If I have a user "user1234", and want to only allow him to have a max 10 connections open at any given time.

Thanks.


texts: Slow query - big difference between estimated and actual rows

texts:
At my work, we have a problem with a search query.

If I don't include the lookup into Table_39 (see below query), it takes 0 seconds to execute the query.
If I include the lookup (..AND (Not Exists(Select 1 From Table_39 ll ..), it takes 4 minutes and 57 seconds.

From the execution plan I can see that there is a huge discrepancy between number of expected rows and actual rows, especially in the index for Table_39 (62.6 estimated vs. 10,956,165), so I tried running UPDATE STATISTICS WITH FULLSCAN, but it didn't make any difference.

I would appreciate any input I can get on how to improve the performance of the query that includes the lookup!

Query:

select [Table_2].Gid
  into #Table_1
  from [Table_2]
where [Table_2].Gid_Line in ('{31445a8f-900b-4d64-b72f-4e2ac10fd6a7}')
union
select '{31445a8f-900b-4d64-b72f-4e2ac10fd6a7}' as Gid

create unique nonclustered index temp_Table1 on #Table_1 (Gid)
go

select top 500 Table_9.Id
 , Table_9.Gid
 , Table_9.name
 , Table_9.TimeEnter
 , Table_9.Prio
 , Table_9.Direction
 , Table_9.status
 , Table_9.CreaterId
 , Table_9.Subject
 , Table_9.BodyType
 , Table_9.Attach
 , Table_9.FromAddr
 , Table_9.ToAddr
 , Table_9.Gid_LineSet
 , Table_9.xml
from Table_9
where Table_9.Gid_LineSet in (
    select Gid
      from #Table_1
      )
  and not exists (
   select 1
   from Table_39 ll
   where ll.Id_Obj_To      = Table_9.Id
    and ll.Id_ObjType_To   = 5
    and ll.Id_ObjType_From = 3
    and ll.Id_Obj_From in (
        9602, 10661, 10857, 10858, 10859, 68823
      , 68824, 68825, 68826, 68827, 68828, 68829
      , 68830, 68831, 68832, 68833, 68834, 372513
      )
    )
order by Table_9.Id desc

drop table #Table_1

Execution plan for slow query (with lookup): https://www.dropbox.com/s/rc1i6vvdwwc5hzh/long%20query_Cleaned.sqlplan?dl=0

Execution plan for fast query (no lookup):
https://www.dropbox.com/s/bjafn5tiy3uepul/short%20query_Cleaned.sqlplan?dl=0


texts: How to select id of father from the child id recursively

texts:
I have in the field cod_categorizacao_pai the ID of the father, and I need to mount a select that just enter an ID and it will list the paternal IDs of this ID until it reaches the parent of all. How do I set a select to do this? Will I need a procedure? I am using PL / SQL.

The table:

CREATE TABLE [dbo].[menu]
(
  id INT PRIMARY KEY
, name NVARCHAR(20)
, parent_id INT REFERENCES [dbo].[menu] ([id])
);

Suppose I am using the records below:

INSERT INTO menu VALUES (1,'Main Product',NULL);
INSERT INTO menu VALUES (2,'Next Main Product',NULL);
INSERT INTO menu VALUES (3,'Addon Product',1);
INSERT INTO menu VALUES (4,'Addon Product',1);
INSERT INTO menu VALUES (5,'Sub-Addon Product',3);
INSERT INTO menu VALUES (6,'Sub-sub-Addon Product',5);
INSERT INTO menu VALUES (7,'Addon Next Main Product',2);

I would like to have the following listing if I passed ID '6' as a parameter:
1,3,5,6


texts: MySQL upgrade on Ubuntu - any heads ups?

texts:
I am needing to upgrade MySQL on Ubuntu, it is a production server and naturally cautious.

My many googles look to be essentially saying that I need to :

Backup my current mysql database and tables/data
Uninstall current mysql
Install new MYSQL 5+
Restore Databases/ tables and data
Hope and Pray I got it right ??

Something doesn't seem right, sounds like a lot of down time and risk

Am I missing something, or any simple solutions?

Upgrading from MySQL 4 to 5 on Ubuntu 10

Many Thanks,

Rob


texts: PostgreSQL efficient lookup for 2 column combinations

texts:
I have a PostgreSQL (v. 12) database storing match results. I have integer columns team_home and team_away which have foreign key constraints for team_id in table teams . My question has a few scenarios:

If I want to find all games involving a single team with ID 1 I could do:

SELECT * FROM matches WHERE team_home = 1 OR team_away = 1;

If I wanted to find all games between 2 teams, IDs 1 and 2 I could do:

SELECT * FROM matches WHERE (team_home = 1 AND team_away = 2) OR (team_home = 2 AND team_away = 1);

I want to join my matches table with a teams table:

.
SELECT m.match_id, t1.name, t2.name
FROM matches m
JOIN teams t1 ON t1.team_id = m.team_home
JOIN teams t2 ON t2.team_id = m.team_away
WHERE m.team_home = 1 OR m.team_away = 1;

The question I have is - are these the optimal queries and can I improve efficiency?
I have read a bit about indexes and this page seems to suggest I could improve my queries across two columns like this by using a GIN index, however that is for text columns and not Foreign Key integer columns.
Alternatively should I create an ARRAY column of team ids and then use an ANY query? (The obvious downside to that is you lose the abilit


texts: MySQL dump error (#1005 - Can't create table 'novephp_cz_bv.jos_jquarks_customsets' (errno: 150))

texts:
I´m trying dump mysql database from MySQL 5.1. to MySQL 5.5. But I´m getting an error "#1005 - Can't create table 'novephp_cz_bv.jos_jquarks_customsets' (errno: 150)".
Problematic part of script is here:

CREATE TABLE `jos_jquarks_customsets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `set_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `set_id` (`set_id`),
  CONSTRAINT `jos_jquarks_customsets_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `jos_jquarks_setsofquestions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Do you have some ideas?

Thanks, Adam


texts: What is the impact of large varchar data types on indexing?

texts:
We have some tables in our database that have a data type of varchar(8000) where the longest actual value in that column is 75.  When we place an index on the column we get the expected warning that the maximum permissible index length is 900 bytes.

Does the actual data type impact the effectiveness of this index?  That is, since the actual data is far below 900 bytes, will the index still work?

For what it's worth, I am trying to get the data type modified as this is a ridiculous limit for the column's purpose, but I'm not sure how vehemently to argue.


texts: MySQL table filed with text datatype inserting some script value automatically and ignored user input

texts:
In my MY-SQL database table having with text datatype.  In that field having some unwanted script tag added automatically.  If I given that any value also ignored.

and also I tried to edit that value  with update command but I couldn't.

and tried to re-create that table, but getting same value. Here I verified and there no any triggers.

Herewith attached the screen shot for reference:


texts: Oracle Client sessions keep disconnecting

texts:
We recently moved our Oracle server from a remote location to a local server and now the Oracle clients keep disconnecting.

It does not seem to be a timeout issue because it happens while we are actively using it. We are using Oracle SQL Developer, PL/SQL Developer, and Toad.  We are running Oracle 12 Enterprise on a VM.

We do not see any errors - our sessions just drop. Our server is running on a VM on Redhat 7. We are running the clients on Windows.  Often when we run a query we have to wait for it to re-initialize before the query is executed. Hence we are assuming that since it has to re-initialize that our session dropped.

When we write a query the client (Toad, PL/SQL Developer, etc) has to reconnect in order to execute the query.  I would understand if we stepped away but we are actively writing queries and executing them and then the client disconnects and has to reconnect in order to execute.

How can we solve or debug this issue?


texts: Group by minute and two minute intervals

texts:
I have a datetime column and I can easily run queries with a group by on my datetime column. However, I would like to run group queries for

1-minute intervals
2-minute intervals
5-minute intervals
1 hour intervals
etc.

How do I do this?


texts:
I am trying to understand a portion of a query i am looking at. it was written elsewhere.

between now() - interval 30 day and now();

Does this mean between now and 30 days above (returns data between now and one month in advance or does it mean between now and one month prior?

The problems i am having to understand are:

Why is there a minus sign instead of an AND after between? Unless the and condition is (between now-interval 30 day AND now).
If so, does the minus sign incinuate back 30 days?


texts: Postgres fetch exact ROW using JSON column

texts:
I have records in table like below format.

Table struture

CREATE TABLE test.orders (
id serial NOT NULL,
info json NOT NULL,
value varchar NULL,
CONSTRAINT orders_pkey PRIMARY KEY (id)

);

If I pass value for info column {"101":1} then only value s1 should be fetched not the others.

So is there any feasible way in postges to fetch exact this type of result?

Thanks


texts:
I have a following given scenario.
A user can have zero or more item.
A user can comment on the item. (Just simple comment listing with name of people who comment, no need hierarchical structure for replying comment)

And i come out database structure like this. Not sure if this is a good database design


texts: Is there a way to prevent a database from being deleted from the SSMS GUI?

texts:
Using SQL Server Management Studio, it is possible to right-click a database in the Object Explorer and delete it. (You can also delete a database using T-SQL, not demonstrated here.) I accidentally deleted the wrong database (thankfully, I was able to restore from a recent backup).

Still, it got me thinking. Is there a way to configure a database against accidental deletion -- by GUI or by T-SQL Script? If I actually need to remove a specific database, then I should have to take a few deliberate steps to enable database deletion. Otherwise the database cannot be deleted.

I searched for "prevent database deletion" and all I found were how-to articles about how to prevent table row data from being deleted -- not accidental deletion of the entire database.


texts: Slower queries after upgrade from mysql-5.5 to mysql-5.6

texts:
There is lots written about how MySql 5.6 is faster than MySql 5.5 (e.g. http://dev.mysql.com/tech-resources/articles/mysql-5.6.html) but we upgraded our Amazon RDS instance (using the new upgrade procedure) and found that the average time spent in the database per page view got worse, from 30ms to 50ms - a significant slow down.

This is a chart (from newrelic) of the Time spent in the database per page view (approx 1000 queries per second):

The slow down seems to be on simple UPDATE statements. I wouldn't expect them to be a problem because the PK is used. This example shows a statement that took 6 seconds to execute but the explain plan is optimal (rows in table: 700k):

mysql> EXPLAIN UPDATE users SET type='dual', updated_at=NOW() where user_id=1234;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | users   | range | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+

updated_at=NOW() seems to be a common theme in the newly slow statements.

Is there any reasons UPDATEs are slower with MySQL 5.6? Does anyone have any clues about what I could check to debug this slow-down?

Other info: instance size: db.m2.4xlarge , query_cache is off. performance_schema is off. exact versions: was: 5.5.27, now: 5.6.16.


texts:
I am playing around with some stuff in SSMS to learn a little more as I study for my 70-461 exam and I came across a little hangup. I am trying to create a table to play around with so I don't have to alter/delete any of the already created tables in the AdventureWorks or TSQL2012 databases. I've created a temp table to test my code before I actually create a table to play with and this is the code I am using to insert values into my table:

DECLARE @i INT = 1
 WHILE @i < 10
    BEGIN
    INSERT INTO #TestEmployeeCountry
    VALUES ( SUBSTRING('ABCDEFGHIJKLMNOP', @i, 1),
        CASE (SELECT ABS(CHECKSUM(NEWID()))%10 +1)
            WHEN 1 THEN 'USA'
            WHEN 2 THEN 'CANADA'
            WHEN 3 THEN 'MEXICO'
            WHEN 4 THEN 'UK'
            WHEN 5 THEN 'FRANCE'
            WHEN 6 THEN 'SPAIN'
            WHEN 7 THEN 'RUSSIA'
            WHEN 8 THEN 'CHINA'
            WHEN 9 THEN 'JAPAN'
            WHEN 10 THEN 'INDIA'
        END)
    SET @i = @i + 1
    END;

The problem I am having is I keep getting an error saying "Cannot insert the value NULL into column 'Country', table 'tempdb.dbo.#TestEmployeeCountry" The reason this is is because I have the Country column set to NOT NULL, and my code does work for some of the inserts, the problem is I randomly get NULL values out of my case statement.

I know that to fix this I can easily add another line that says "DEFAULT xxxxxx" however I want to understand what is going on because based on what I see I shouldn't have to do that, should I? I thought I wrote my case statement correctly, giving me a number between 1-10 only and upon testing just that specific select statement over 1000 tries, I always get a random number between 1-10, nothing larger or smaller. Can anyone help me to understand why this code tries to enter a NULL value into that column?


texts: MySQL: SELECT with SUM() and COUNT() as new columns

texts:
What is the correct SELECT statement to produce this desired output?

+------------+----------+-------------+-----------------+
| QtyOfParts | FamilyID | NumInFamily | TotalQtyOfParts |
+------------+----------+-------------+-----------------+
  28           1          3               79
  32           1          3               79
  19           1          3               79
  28           2          2               66
  38           2          2               66
  46           3          4              151
  30           3          4              151
  51           3          4              151
  24           3          4              151
...more rows...

'QtyOfParts' and 'FamilyID' are existing columns in the table.

'NumInFamily' is a calculated column for display/report; its
value is the COUNT() of each unique FamilyID. E.g., FamilyID
= 1 has 3 in its family; FamilyID = 2 has 2 in its family;
FamilyID = 3 has 4 in its family.

'TotalQtyOfParts' is a calculated column for display/report;
its values is the SUM() of the QtyOfParts for each FamilyID.
E.g., FamilyID = 1 has 28 + 32 + 19 = 79 parts; FamilyID =
2 has 19 + 28 = 66 parts.

Using 'SELECT ... COUNT(FamilyID) ... GROUP BY FamilyID' fails
to show each and every row -- instead it shows only one row
for each FamilyID. So for example instead of seeing three rows
for FamilyID = 1, only one row is shown. (Note: ROLLUP made no
difference.)

Using SUM() in the select simply tallied the sum of every single
value under QtyOfParts.

CREATE TABLE IF NOT EXISTS `Test`.`NoOps` (
  `ID_NoOps` INT NOT NULL,
  `QtyOfParts` INT(3) NOT NULL,
  `FamilyID` INT(3) NOT NULL,
  PRIMARY KEY (`ID_NoOps`))
ENGINE = InnoDB


texts: resuming server replication on PostgreSQL servers

texts:
I got one main server and other two servers are replicating from the main.

I got full disk space on the main server (I have an archive_command that put wal archives into the /wal_archive/ directory).

It got full, and the PostgreSQL log shows the error:

archive command failed:

    2019-06-04 09:52:49.079 EEST [3365] LOG:  archive command failed with exit code 1
    2019-06-04 09:52:49.079 EEST [3365] DETAIL:  The failed archive command was: test ! -f /wal_archive/000000010000028C0000003B && cp pg_xlog/000000010000028C0000003B /wal_archive/000000010000028C0000003B

I cleaned up the /wal_archive/ directory with pg_archivecleanup, but PostgreSQL still throws the same error.

I have tried to reload PostgreSQL config without making changes to the config itself and without restart using:

/etc/init.d/postgresql reload

but still the same, in log, PostgreSQL throws an error.
How should I resume wal_archive copying to the wal_archive directory?

Should I change archive_command to true, reload, and change archive back to original again?

I'm trying to avoid restarting the server itself.


texts: Whats the best and fast way to import a huge DBF file to a Postgres in Windows?

texts:
I have some huge DBF files (about 35 GB) to import to Postgres.
Whats the best and fast way to do it?

OBS: I have 300 GB of free disk space and 8 GB RAM.


texts: Is this the correct way of moving MySQL data directory, (on Centos)?

texts:
I need to move my data directory to a bigger drive on the same server.

# stop mysql
/etc/init.d/mysql stop

# copy the data over
cp -R -p /var/lib/mysql /home/mysql

#edit my.cnf
# and add a 'datadir' section with the new path.

#restart mysql
/etc/init.d/mysql restart

The copying of the data will take ages, (I suspect), but at least it will be safe.

I might also want to add a temp directory somewhere.

Do I need to do anything else to move the data.

Will I need to rebuild indexes or something else?


texts: sp_OACreate vbscript.regexp Class not registered

texts:
Imagine a Windows Server 2003 Enterprise x64 Edition.

I can create a 'vbscript.regexp' object using a vbscript (confirming that the DLL is registered).

I cannot create a 'vbscript.regexp' object using sp_OACreate in a SQL Server 2005 SP3 (.4053)(cluster) environment.

OLE Automation is checked ON in surface area configuration.

The sp_OACreate was working until very recently.

Can you help me?

*CODE*

            declare
                @source varchar(5000),
                @regexp varchar(1000),
                @replace varchar(1000),
                @globalReplace bit,
                @ignoreCase bit

            set @source = 'NBG76TF43'
            set @regexp = '[^0-9]'
            set @replace = ''
            set @globalReplace = 1
            set @ignoreCase = 1

                DECLARE @hr integer
                DECLARE @objRegExp integer
                DECLARE @result varchar(5000)

                EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
                IF @hr <> 0
                    BEGIN
                        EXEC @hr = sp_OADestroy @objRegExp
                        PRINT  'A'
                        PRINT @hr
                    END
                EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
                IF @hr <> 0
                    BEGIN
                        EXEC @hr = sp_OADestroy @objRegExp
                        PRINT  'B'
                        PRINT @hr
                    END
                EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
                IF @hr <> 0
                    BEGIN
                        EXEC @hr = sp_OADestroy @objRegExp
                        PRINT  'C'
                        PRINT @hr
                    END
                EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
                IF @hr <> 0
                    BEGIN
                        EXEC @hr = sp_OADestroy @objRegExp
                        PRINT  'D'
                        PRINT @hr
                    END

                EXECUTE @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
                IF @hr <> 0
                    BEGIN
                        EXEC @hr = sp_OADestroy @objRegExp
                        PRINT  'E'
                        PRINT @hr
                    END

                EXECUTE @hr = sp_OADestroy @objRegExp
                    IF @hr <> 0
                        BEGIN
                            PRINT  'F'
                        PRINT @hr
                        END

                PRINT  @result

            /*
            A
            -2147211480
            B
            -2147211480
            C
            -2147211480
            D
            -2147211480
            E
            -2147211480
            F
            -2147211480
            */


texts:
I have a mongodb cluster where shards exist across multi geographically locations and each of those locations have replica members for each shard. I have setup our DR datacenter. Which also has a mongos for creating backups. I want to use the mongodump command to dump the database onto the file system of the mongos instance before sending it off to long term archive. The issue is that mongodump command on sharded clusters sets read pref to secondary so it may dump data from an active production secondary and not the one I have setup for this processes.

  When running mongodump against a mongos instance where the sharded
  cluster consists of replica sets, the read preference of the operation
  will prefer reads from secondary members of the set.
  http://docs.mongodb.org/manual/reference/program/mongodump/

I am looking for a way to change the readpref to nearest when running the mongodump command. Or if there is another command that will allow for the same sort of backup as mongodb. (http://docs.mongodb.org/manual/core/read-preference/)


texts: INSERT and DELETE in single operation (moving from one table to other)

texts:
Stemming from this question, I am going to use that complex sort's results to decide which rows' data to move from one table to another. Though I have not yet tried it, I think there should be no problem to do the "copy" part via a:

INSERT * FROM (...)

Using my results from that SELECT inside the parentheses.

However, if I then user the same query to try to SELECT the same results in order to do a DELETE FROM, the results will no longer include those same rows. So, I need some way to store at least the primary keys (and origin table, since we are talking about 2 origin tables and one destination table) in order to DELETE the correct row from the correct table.

From what I have read and understood, it's not possible to do in a single query, and in any case would require a transaction (which I need to lookup how to correctly do).

So my first approach would be to do the INSERT copy first, and then maybe delete form the two tables any rows containing primary keys appearing in my destination table. It seems a little inefficient since that table would be much larger than the two origin tables, and I figure that from the derived table I got from my subquery, it would be much much more efficient to use the info located there. So, any clues as to how I could best go about this?


texts: How to use JSON path wildcards in JSON_QUERY?

texts:
For a minimal working example say I have a column storing JSON with a structure similar to the following.

DECLARE @json NVARCHAR(4000) =
    '{"Dims":
     [
      {"Name":"Apple", "Baking": ["Pie","Tart"], "Plant":"Tree"},
      {"Name":"Tomato", "Cooking":["Stew","Sauce"], "Plant":"Vine"},
      {"Name":"Banana","Baking":["Bread"], "Cooking":["Fried"], "Plant":"Arborescent"}
     ]}
'

This is valid JSON and scalars can be returned.

SELECT ISJSON(@json);

1

SELECT JSON_VALUE(@json,'$.Dims[0].Name');

Apple

However, when I use JSON_QUERY to return the names of all of the "Dims" SQL Server 2017 errors.

SELECT JSON_QUERY(@json,'$.Dims[*].Name');

Msg 13607, Level 16, State 4, Line 16
JSON path is not properly formatted. Unexpected character '*' is found at position 7.

This should return

["Apple", "Tomato", "Banana"]

This is a valid standard JSON path. Any ideas why this is not working and how to get this to work on SQL Server 2017.


texts: Azure SQL - Modifying Indexed Views without downtime

texts:
We have a SQL Azure database with a large indexed view that supports a website with high traffic during business hours.  One of our goals from a DevOps perspective to be able to ship software (including database changes) during business hours without customer impact (i.e., zero-down deployments).  We've been able to do this successfully in nearly all areas of our database using compatibility techniques however we run into issues when it comes to including new columns into our Indexed (Materialized) Views in SQL Azure.

I'm looking for general guidance, experience, or unique techniques that would allow for the inclusion of new columns into a fairly large Indexed View without customer impact.

If needed I can provide more specifics but given that we may deal with more indexed views in the future, hoping someone has a couple patterns in mind to consider.

TL;DR; - What is the best way to deal with indexed view changes while maintaining high service availability?


texts:
I can't believe they make it this hard.  I am at a loss about how to view the data in my database.

Is there an easy way to see what data is in my tables with pgAdmin III?  Alternatively, is there a program that I could use that does not suck?


texts: Replication monitoring refresher for distribution job affecting performance

texts:
I have a performance problem due to a sql agent job executing dbo.sp_replmonitorrefreshjob.

  The environment is a 2 node Cluster with SQL Server 2005 SP2 and with
  transaction replication. The OS is  Windows Server 2003 R2.

This job runs a stored procedure on distribution database and Application Team experienced total Integration Inbound & Outbound transaction failure due to multiple queries being blocked on the Database. Upon looking deeper into the queries running on the database the following query was identified to have been running for over 9 Hours and when this job(exec dbo.sp_replmonitorrefreshjob) is killed, the issue goes away.
This job was setup to set with 2147 retry attempts and   retry interval 1 so it is pretty much running 24/7.

Some of the code for this Stored Procedure is

ALTER procedure [sys].[sp_replmonitorrefreshjob]
    (
    @iterations tinyint = 0 -- 0 - run continuously, non 0 - run for specified iterations
    ,@profile bit = 0 -- for internal use - DO NOT DOCUMENT (remove this before release)
     )

Is there a way to improve this job and how often does it need to run. If we can get away with this job and if there is another option let me know.
Upgrade is not an option to different version or even service pack is not a question.


texts: PostgreSQL 9.3 running on Linux - need to authenticate vs. Windows Active Directory

texts:
Database server is Ubuntu Linux 3.13.0-68-generic
Database is PostgreSQL 9.3.10
LDAP set up according to https://cdc.iseage.org/tutorial-pam-ldap-authentication-active-directory-debianubuntu/
pg_hba.conf line:
host all all 127.0.0.1/32 ldap ldapserver="my.ldap.net" ldapprefix="uid=" ldapsuffix=",ou=People,o=my.net,dc=my,dc=ldap,dc=net"
There is a PostgreSQL account set up for searched.user@my.net

I'm able to get successful LDAP searches:

~>$ ldapsearch -h my.ldap.net -x -b "ou=People,o=my.net" "uid=searched.user@my.net" cn

That returns as expected, the CN of the user looked for. I'm also able to bind lookups as in

~>$ ldapsearch -h my.ldap.net -W -D "uid=bind.lookup@my.net" -b "ou=People,o=my.net" "uid=searched.user@my.net" cn

and have the correct information returned after the bind.lookup user enters their AD password.

When I try:

psql -U "searched.user@my.net"

I get prompted for the password. Upon entering the password, I get:

psql: FATAL: password authentication failed for user "searched.user@my.net"

That's what I've tried. Any ideas on how I can get this working? I should mention that it is NOT desired to join the database server to the Windows domain - too many other linkage issues would arise.


texts:
I am trying to enable file stream on Microsoft SQL Server 2017, on a Windows Core 2019 Server.

I have changed the registry settings at the following location to enable it as there is no GUI. I set the value to 3

ServerName\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.InstanceName\MSSQLServer\Filestream.EnabledLevel

After This I restarted

I have also installed the latest Cumulative update (20) as I know there where issues with Driver signing.

After This I restarted

I am seeting the following Errors in the log file at C:\Program Files\Microsoft SQL Server\MSSQL14.InstanceName\MSSQL\Log

<{781FAE78-09AB-4EE5-B051-67747BDB19E3}>RsFxMgmtInitialize failed (the RsFx device is not found. Check if RsFx driver is started.): Error 0x80070002 (-2147024894)
2020-04-21 08:56:03.94 spid5s      FILESTREAM: failed to connect to kernel driver RsFx0503.
<{1038F43D-3391-45F7-B1B3-BADF26459429}>Failed to initialize CFsaShareFilter: Error 0x80070002 (-2147024894)

<{1038F43D-3391-45F7-B1B3-BADF26459429}>Failed to initialize CFsaShareFilter: Error 0x80070002 (-2147024894)

2020-04-21 08:56:03.94 spid5s      FILESTREAM: effective level = 0, configured level = 2.
2020-04-21 08:56:03.94 spid5s      FILESTREAM feature could not be initialized. The operating system Administrator must enable FILESTREAM on the instance using Configuration Manager.

SELECT @@VERSION

Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64)   Mar 13 2020 14:53:45   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

I got this working on one server and cannot get it working on the second one.
There is also a version of sql 2019 on this box

Any help? Also any advice on getting this working on a cluster


texts:
I'm working on joining multiple tables together to extract some data related to user "runs", this includes basics like runID, username etc but also joins to the run times and favourites.

I have the query below which uses a LEFT JOIN for the user_run_times and run_favourites table.

The query should show all runs for the user, the latest date ran, if it's a favourite, and the quickest run time - All of which is displayed, but if more than one time is logged the favourites count goes above one (should only ever be one or zero).

SELECT
    user_runs.userID,
    user_runs.runID,
    `createdBy`,
    `username`,
    DATE_FORMAT(`dateCreated`,'%d-%m-%Y') `dateCreated`,
    COUNT(runFavouriteID) AS favourite,
    DATE_FORMAT(MAX(`runDate`),'%d-%m-%Y') AS `lastRan`,
    MIN(`runTime`) AS `fastestTime`
FROM
    `user_runs`
    INNER JOIN `runs` ON user_runs.runID = runs.runID
    INNER JOIN `users` ON users.userID = runs.createdBy
    LEFT OUTER JOIN `user_run_times` ON user_run_times.runID = user_runs.runID
    LEFT OUTER JOIN `run_favourites` ON run_favourites.runID = user_runs.runID
WHERE
    user_runs.userID = 4
GROUP BY
    runID, user_run_times.runID;

I'm looking for some suggestions on how to improve this query? The current output is:

UserID   runID    createdBy   username   dateCreated   favourite   lastRan       fastestTime
4        3        3           bob        11-12-2011    3           27-01-2012    36920030

The column favourite should only display one (as there is one row in run_favourites) but it shows three (two for user_run_times and one for run_favourites)

Hopefully I haven't made this too confusing, if somebody could suggest how I can output the table structure in a "text" format like most on here then I will also post those.

Any advice appreciated, thanks! :)


texts: Sql Rank for records contain multiple combinations

texts:
I am trying to filter the records which contains multiple combinations and removing the records which contains a single combination. Database DB2

Query Used:

select *
from
    (select b.Store,b.order,
            rownumber() over (partition by b.store order by b.order) AS RNK
     from Library.Table1 b
    ) tmp
where tmp.RNK > 1
order by tmp.Store

Current output:

Store Order   RNK

Store1  Order1  1
Store2  Order1  1
Store2  order2  2
Store2  order3  3
Store3  Order1  1
Store3  order2  2
Store3  order3  3
Store4  Order1  1

Expected output:

Store Order   RNK

Store2  Order1  1
Store2  order2  2
Store2  order3  3
Store3  Order1  1
Store3  order2  2
Store3  order3  3


texts:
I'm trying to create a DDL Trigger dynamically so I can create a job that checks each database and creates the trigger if it's not already in place. To do this, I'm using sp_foreachdb. The code works on it's on but I can't get it to work with sp_foreachdb so I'm guessing I've missed a single quote. However, I'm struggling to find the error. Any help is greatly appreciated.

EXEC master.dbo.sp_foreachdb
'
IF NOT EXISTS (SELECT * FROM sys.triggers T WHERE T.name =
''Database_DDL_Audit'') AND ''?'' <> ''TempDB''
BEGIN
EXEC
(
''
CREATE TRIGGER Database_DDL_Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
DECLARE @EventDataXml   XML
DECLARE @SchemaName     SYSNAME
DECLARE @ObjectName     SYSNAME
DECLARE @EventType      SYSNAME

SET @EventDataXml = EVENTDATA()

SELECT
@EventType  = @EventDataXml.value(''''(/EVENT_INSTANCE/EventType)[1]'''', ''''SYSNAME'''')
, @SchemaName = @EventDataXml.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''', ''''SYSNAME'''')
, @ObjectName = @EventDataXml.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''', ''''SYSNAME'''')

INSERT [ServerAdmin].[dbo].[ServerChangeLog]
(
[CreateDate]
, [LoginName]
, [ComputerName]
, [ProgramName]
, [DBName]
, [SQLEvent]
, [SchemaName]
, [ObjectName]
, [SQLCmd]
, [XmlEvent]
)
SELECT  GETDATE()
        , SUSER_NAME()
        , HOST_NAME()
        , PROGRAM_NAME()
        , @EventDataXml.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''SYSNAME'''')
        , @EventType
        , @SchemaName
        , @ObjectName
        , @EventDataXml.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', ''''NVARCHAR(MAX)'''')
        , @EventDataXml
''
)
END
'

The error I'm getting is

  Msg 102, Level 15, State 1, Procedure Database_DDL_Audit, Line 40 [Batch Start Line 0]
  Incorrect syntax near '@EventDataXml'.


texts: Designing a table with multiple column types and different foreign keys

texts:
I have a table item in which different columns are filled depending on the template type of item that it is e.g.

|item_id|template_id|brand|model|heading|usp_1|usp_2|icon_1_asset_id|icon_2_asset_id|
|-------|-----------|-----|-----|-------|-----|-----|---------------|---------------|
| 1     | 1         | ABC | xyz |       | ddd |     | 1             | 2             |
| 2     | 1         | ABC | xyy |       | eee |     | 3             | 4             |
| 3     | 2         | BCD | ghj | opqrs |     |     | 2             |               |
| 4     | 3         | FGH | stu |       | jjj | kkk |               |               |

In reality, it has about 40 columns.  template_id has a foreign key to the template table, icon_1_asset_id and icon_2_asset_id both have foreign keys to the asset table.  The columns filled depend on the template_id, in this example:

All items have a template_id, brand, model.
template_id = 1 items have usp_1, icon_1_asset_id, icon_2_asset_id
template_id = 2 items have heading, icon_1_asset_id
template_id = 1 items have usp_1, usp_2

As you can see, there is a large amount of wasted space of cells that will never hold data.

I have considered altering the structure to:

Table item

|item_id|template_id|
|-------|-----------|
| 1     | 1         |
| 2     | 1         |
| 3     | 2         |
| 4     | 3         |

Table property_type

|property_type_id| description     |
|----------------|-----------------|
| 1              | brand           |
| 2              | model           |
| 3              | heading         |
| 4              | usp_1           |
| 5              | usp_2           |
| 6              | icon_1_asset_id |
| 7              | icon_2_asset_id |

Table template_property_type

|template_id|property_type_id|
|-----------|----------------|
| 1         | 1              |
| 1         | 2              |
| 1         | 4              |
| 1         | 6              |
| 1         | 7              |
| 2         | 1              |
| 2         | 2              |
| 2         | 3              |
| 2         | 6              |
| 3         | 1              |
| 3         | 2              |
| 3         | 4              |
| 3         | 5              |

Table item_property

|item_property_id|item_id|property_type_id| value |
|----------------|-------|----------------|-------|
| 1              | 1     | 1              | ABC   |
| 2              | 1     | 2              | xyz   |
| 3              | 1     | 4              | ddd   |
| 4              | 1     | 6              | 1     |
| 5              | 1     | 7              | 2     |
| 6              | 2     | 1              | ABC   |
| 7              | 2     | 2              | xyy   |
| 8              | 2     | 4              | eee   |
| 9              | 2     | 6              | 3     |
| 10             | 2     | 7              | 4     |
| 11             | 3     | 1              | BCD   |
| 12             | 3     | 2              | ghj   |
| 13             | 3     | 3              | opqrs |
| 14             | 3     | 6              | 2     |
| 15             | 4     | 1              | FGH   |
| 16             | 4     | 2              | stu   |
| 17             | 4     | 4              | jjj   |
| 18             | 4     | 5              | kkk   |

Although this gives more efficient storage and an amount of database-level control over which properties can be filled, you loose the foreign key constraint on the property value due to the fact that some properties are linking to another table and some are plain text.

Is there a way to put some database-level constraint on the value column - possibly by breaking it out onto another table, or re-working the structure?

Or is there a completely different way to approach this type of data?

I'm using SQL Server, although the fundamental problem is not tied to any specific relational database management system.


texts: Could you please explain clustering in SQL server?

texts:
I'm new to clustering concepts. I would like to know more parallel processing with multiple DB nodes to have load balancing for the incoming requests. Our main goal is to achieve performance and lot of queries held in queue that come from for BI applications.

Is there any built in mechanism in SQL server to redirect requests to secondary node? We have huge volume of queries that runs on specific day and we need to distribute to secondary node.

We have SQL server 2012 in place and one database server up and running.


texts: When to choose an attribute vs. a new dimension?

texts:
I am fully aware of what is a fact, attribute and dimension. However, I'm quite confused to which traits I should choose for dimensions vs attributes of that dimension. For instance, I'm building a hospital data warehouse and gender could be a dimension. However, it can also be an attribute of the "patient" dimension.

I am aware that ONE advantage of choosing gender as a dimension is that I can save on space (i.e. there could be millions of patients, and thus millions of attributes). But, what if gender changes? How can I track that? Same with other attributes: diagnoses can be a dimension AND it could be an attrbiute. If I choose it as a dimension, how can I track a patient's flow/change of diagnoses??

Thank you so much


texts: I need to choose a data type for SQL Server( BigInt vs Decimal) on the basis of performance and querying (10 digit number)


texts: Found important issue with SQL Server 2008 R2. Need Advice

texts:
Recently while installing my copy of SQL Server 2008 R2; the power went OFF. So now it does say the some features are installed and some are not but when I run SQL server detection manager it says that there is nothing. I inquired further and there is probably some problem in the registry counter. I also tried uninstalling SQL server's installed components from Control Panel and I was successful in uninstalling SOME components but some others are now saying that some totally other components are now missing and HENCE it cannot be uninstalled

The log error is below:

  Warning 26003. Microsoft SQL Server 2008 R2 Setup Support Files cannot
  be uninstalled because the following products are installed:
  * SQL Server 2008 R2 Common Files
  * SQL Server 2008 R2 Database Engine Shared.

Please can I have some expert or novice (practically anyone) advice and solution for this issue.


texts:
I am looking to replace some hard-coded values in CLR code (SQL 2012), I came across the use of config files here - https://stackoverflow.com/questions/16177769/assembly-configuration-clr-stored-procedure.  A couple responses said it is better to save key/value pairs in tables as opposed to config files, one reason being that it wasn't supported in SQL 2014+.

Our developer says it works fine in SQL 2014, so I'm not concerned with that, but I do think storing the values in a table on the local database would be a better idea.

Can anyone else confirm that config files are or are not supported in SQL 2014+?
Is there any documentation on best practices for storing key/value pairs in a table, or any reason not to?


texts: Postgres partition tables 1 million rows

texts:
I have a 1 million rows in one of my table and expect it grow to 3-4M in next 1 or 2 years.

There is lot of averages and sum are run on the date column for this row.

I'm planning to partition the tables to make the query run faster..

Questions:

Is it ok to partition table that just has 1 million rows.
I have 10 columns in the table but there are 2 columns that are text which stores around 2000 bytes of data.
How many rows Postgres can handle without partitions.


texts:
I am using SQL Server Management Studio 2008. I am connect to localhost and I want to create a database. When I click on New Database, type the name and click ok, I get the message:

  Index was outside the bounds of the array (Microsoft.SQLServer.Smo).


texts:
When I righ click the job + view history, I can only see 2 days of logs. I'm sure this is a really noob question but I can't find the answer.

I've been changing values but with no success. The same config I have in this server, I have in other servers.


texts: case statement sub query need to return multiple values

texts:
Below query need to return multiple values

select

CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
THEN (SELECT StationeryClientProject.ImpFile from StationeryClientProject
      WHERE StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id)
ELSE (SELECT Stationery.ImpFile from Stationery
      WHERE Stationery.Stationery_Id = [Order].Stationery_Id)
END AS ImpFile

FROM [Order]
LEFT OUTER JOIN ClientProject ON ClientProject.ClientProject_Id = [Order].ClientProject_Id
LEFT OUTER JOIN StationeryClientProject ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id
LEFT OUTER JOIN Stationery ON Stationery.Stationery_Id = [Order].Stationery_Id

but it was showing up an error

  Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1
  value. This is not permitted when the subquery follows =, !=, <, <= ,
  >, >= or when the subquery is used as an expression.

Not sure how to handle it. any help would be highly appreciated


texts: Can we commit the transaction when the sql server trigger fails?

texts:
I am trying to avoid data loss when the insert trigger on a table fails. I am trying this with the following scenario and my code is failing.

When an insert happens on the Customers table, I want to insert the same data into the Archive table. When the trigger fails, I don't want to roll back the entire transaction, which would lead to data loss on the Customers table.

Even if the trigger fails, I want the data to be inserted in the Customers table, and the stored procedure should return the Customer_ID as usual.

ALTER TRIGGER [dbo].[Customer_Insert_Trigger_Test]
   ON  [dbo].[Customers]
   AFTER INSERT
AS
BEGIN

BEGIN TRY
    begin transaction;
    set nocount  on;
    SAVE TRANSACTION InsertSaveHere;
    --Simulating error situation
    RAISERROR (N'This is message %s %d.', -- Message text.
       11, -- Severity,
       1, -- State,
       N'number', -- First argument.
       5); -- Second argument.

   Insert into Archive select * from Inserted;
    commit transaction;

  END TRY

  BEGIN CATCH
    ROLLBACK TRANSACTION InsertSaveHere;
  END CATCH
END

My question is mostly around how to avoid the actual insert on customers table to roll back if the trigger fails. How to change my code for that?


texts:
I'm trying to run a recursive alter table reading a couple of parameters from my sys table.

DECLARE @AlterCmd NVARCHAR(MAX)

SELECT top 1
        @AlterCmd = 'ALTER TABLE [dbo].[table] ALTER COLUMN ' + COLUMN_NAME + ' NVARCHAR(' + CHARACTER_MAXIMUM_LENGTH + ')'
    FROM
        information_schema.columns
    WHERE
        table_name = 'table_name' and DATA_TYPE like '%char%'

print @AlterCmd

In this sample code I just want to validate if the command is coming right but it's not possible to run it due an error in the concatenate. According to the error message I get:

Msg 245, Level 16, State 1, Line 63
Conversion failed when converting the varchar value ')' to data type int.


texts: Concatenating only desirable values with LISTAGG

texts:
In the Oracle DB there is a table with data "YEARS", see a table/image below
+-----+-----------+------------------+
| FID |    I0     |       BJA        |
+-----+-----------+------------------+
|   1 |     0     |       1949       |
|   2 |     0     |       1996       |
|   3 |     0     |       1970       |
|   4 |     1     |       1871       |
|   4 |     0     |       1975       |
|   5 |     0     |       1967       |
|   6 |     0     |       1968       |
|   7 |     0     |       1926       |
|   7 |     1     |       2009       |
|   7 |     2     |       2012       |
|   7 |     3     |       2018       |
|   8 |     0     |       1956       |
|   9 |     0     |       1990       |
|  10 |     0     |       1953       |
|  10 |     1     |       1904       |
| ... |    ...    |       ...        |
+-----+-----------+------------------+

I tried to create a fiddle (here), but it did not work ...
CREATE TABLE YEARS (
    "FID" NUMBER(10,0) NOT NULL,
    "I0" NUMBER(10,0) NOT NULL,
    "BJA" NUMBER(10,0)
)
INSERT INTO YEARS (
    "FID","I0","BJA"
)
VALUES (
    (1,0,1949),
    (2,0,1996),
    (3,0,1970),
    (4,1,1871),
    (4,0,1975),
    (5,0,1967),
    (6,0,1968),
    (7,0,1926),
    (7,1,2009),
    (7,2,2012),
    (7,3,2018),
    (8,0,1956),
    (9,0,1990),
    (10,0,1953),
    (10,1,1904)
);

I am trying to execute the following query:
SELECT YEARS.FID,
       MIN(YEARS.BJA) AS "CONSTRYEAR",
       LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC) AS "RECONSTRYEAR"
FROM DB.YEARS YEARS
GROUP BY YEARS.FID;

And the output of the above query:

However, it is not the result that I want ... Yes, I am reading documentation source 1, source 2, and source 3; and also I have seen related threads How to use LISTAGG and WHERE together and How to use Oracle's LISTAGG function with a unique filter?.
How can I get the result looking like this:
+-----+-----------+------------------+
| FID | CONSTRUCT |   RECONSTRYEAR   |
+-----+-----------+------------------+
|   1 |      1949 |                  |
|   2 |      1996 |                  |
|   3 |      1970 |                  |
|   4 |      1871 |             1975 |
|   5 |      1967 |                  |
|   6 |      1968 |                  |
|   7 |      1926 | 2009, 2012, 2018 |
|   8 |      1956 |                  |
|   9 |      1990 |                  |
|  10 |      1904 |             1953 |
+-----+-----------+------------------+

Where you see that values from "CONSTRUCT" column were excluded from "RECONSTRYEAR". I do not understand where do I need to put a WHERE YEARS.IO != 0 clause in LISTAGG, so the lowest year won't be included.


texts: How do I navigate PostgreSQL documentation to find a list of all functions that work on a specific type?

texts:
Let's say I want to find a list of functions that can work on jsonb, what is the most effective way to do this from the terminal without using google? Assuming only access to

man pages
psql
postgresql tables


texts: Should I rebuild index after truncate/large insert?

texts:
I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.)

Basic outline is very simple:

Truncate Tables
Insert 1.75M rows in 'batches' of around 75,000 per time.

I am wondering if I should explicitly re-build the indexes at any time in this process? e.g.

Truncate Tables
ALTER INDEX ALL ON xxx REBUILD WITH (FILLFACTOR=90) [or something similar]
Insert 1.75M rows

or perhaps

ALTER INDEX ALL ON xxx DISABLE
Truncate Tables
Insert 1.75M rows
ALTER INDEX ALL ON xxx REBUILD WITH (FILLFACTOR=90) [or something similar]

Any assistance appreciated...not a DBA - a Dev who knows DB's pretty well is more accurate!


texts: How to improve poor performance with two joined CTE expressions on hierarchical data?

texts:
I have a table of hierarchical data where I want to select entries that, 1) only include matches that are descendants of a given parent, and 2) returns the full hierarchical path of the matched entries.
I am doing this with two CTEs: one that gets all recursive children from the specified parent, and another that selects matches and gets all the recursive parents of those matches. The main query then joins and groups these two CTEs.
However, the performance is not good. Running either CTE on its own (i.e., only getting enties that are descendants, or only getting path hierarchies) returns the results immediately, but both CTEs together (running on a table with millions of entries) can take minutes to hours, depending on the number of string matches, even a single match. It also won't return the first found entry until all are found.
Is it possible to improve the performance of the below query, or at the very least, make it so it starts returning found results immediately?
The table has this structure:
CREATE TABLE [Files]
(  [fileId]       INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,  [name]         TEXT    NOT NULL
,  [parentFileId] INTEGER NULL
,  UNIQUE
   (  [name]
   ,  [parentFileId]
   )
);
CREATE INDEX [Files_parentFileId] ON [Files]([parentFileId]);
CREATE INDEX [Files_name]         ON [Files]([name]);

And the fastest query I've been able to produce is:
-- The [@Context] CTE lists all the fileIds that are descendants
--  of the input @rootFileId. The query should only return
--  matches that are in this list of fileIds
WITH           [@Context]
AS             (
   -- Starting with the ID of the root entry..
   SELECT      @rootFileId           [fileId]
   UNION
   -- ..build up all the IDs of the children
   SELECT      [Files].[fileId]
   FROM        [Files]
   JOIN        [@Context]
      ON       [Files].[parentFileId] = [@Context].[fileId]
               )
-- The [@FilePath] CTE selects each entry that matches the given
--  input @name and all entries that are its parents
,              [@FilePath]
AS             (
   -- Starting with the entries that match the given @name..
   SELECT      -1                    [depth]
   ,           [Files].[fileId]
   ,           [Files].[parentFileId]
   ,           [Files].[name]
   FROM        [Files]
   WHERE       [name]                LIKE @name
   UNION
   -- ..build up all the IDs of the parents
   SELECT      [@FilePath].[depth] - 1
   ,           [@FilePath].[fileId]  -- for grouping entries by the matched entry's id
   ,           [Files].[parentFileId]
   ,           [Files].[name]
   FROM        [Files]
   JOIN        [@FilePath]
      ON       [Files].[fileId]      = [@FilePath].[parentFileId]
               )
-- Group all the parent entries into one column with each found entry
--  and exclude any entry that wasn't on the [@Context] list
SELECT         [@FilePath].[fileId]
,              [@FilePath].[name]
, GROUP_CONCAT([@FilePath].[parentFileId]) [pathFileId]
, GROUP_CONCAT([@FilePath].[name], '\')    [pathname]
FROM           [@FilePath]
JOIN           [@Context]
   ON          [@Context].[fileId]   = [@FilePath].[fileId]
GROUP BY       [@FilePath].[fileId]
ORDER BY       [@FilePath].[depth];


texts: Can PostgreSQL results include the query?

texts:
I run the following command to run 2 queries in a sql file. Could be 1, could be 50 queries. Depends on the deployment.

psql -U <username> -h <servername> -d <databasename> -f
20140701_Queries.sql > 20140701_Results.txt

Here are the queries:

select max(day) from tbl1;
select max(day) from tbl2;

Here are the results I get:

    max
------------
 2014-06-02
(1 row)

    max
------------
 2014-06-06
(1 row)

I would rather it come out something like this:

select max(day) from tbl1;

        max
    ------------
     2014-06-02
    (1 row)

select max(day) from tbl2;
        max
    ------------
     2014-06-06
    (1 row)

But the exact formatting isn't important right now. Just that someone can look for their query and see their results.

Thanks in advance :)

-- Edit

If I just paste the two queries into psql, I get this mix of results and my input which would be fine, if I could just output it to a file:

db=# select max(day) from tbl1;
    max
------------
 2014-06-02
(1 row)

db=# select max(day) from tbl2;
    max
------------
 2014-06-06
(1 row)


texts: Postgresql Function Trying to Insert into 1 row instead of 2

texts:
Postgresql 9.3, Mac OS X 10.6.7

I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows.

First, here is the Table T1:

 test_app_development=# select * from T1;
   name  | age | id
  -------+-----+----
   jim   |  50 |  1
  bill   |  55 |  2
   Ed    |  35 |  3
   joe   |  30 |  4
  dave   |  45 |  5
  steve  |  38 |  6
   (6 rows)

Now the Function:

  create or replace function stats_test() returns numeric as $$
    begin
     insert into stats(stdv) select stddev(age) from T1;
     insert into stats(ave_age) select avg(age) from T1;
     return 1;
    end;
 $$ language plpgsql;

And here are the results to the function:

 select stats_test();

 test_app_development=# select * from stats;
    ave_age | id | stdv
   ---------+----+-------
            |  1 | 9.496
    42.167  |  2 |
   (2 rows)

I have tried variations but with no luck:

  insert into stats(ave_age,stdv) select avg(age), stddev(age) from T1;


texts: How to conditionally create JSON array in postgres with group by clause?

texts:
I am using Postgresql 13.0 and have a table category as follows:
 cat_id | cat_name | piece | weight | is_tag_stock
--------------------------------------------
  4     |   cat1   | 20    | 10     | true
  5     |   cat2   | 30    | 15     | false
  4     |   cat1   | 40    | 5      | false
  4     |   cat1   | 50    | 30     | false

How do I group by in such a way that for one category, the query yields one record, and based on is_tag_stock, it should form JSON?  Also, the JSON should contain the summation of the piece and weight field grouped by cat_id and cat_name. If is_tag_stock is true, then the piece and weight should be added to the tagged field else in the untagged field.
NOTE: For one cat_id, there would be only one cat_name. cat_name is included in the table just to avoid join.
Expected Output
 cat_id | cat_name | tagged                      | untagged
------------------------------------------------------------------------------
  4     |   cat1   | {"piece":20,"weight":10}    | {"piece":90,"weight":35}
  5     |   cat2   | {"piece":0,"weight":0}      | {"piece":30,"weight":15}


texts: Permission denied when trying to install sp_blitz on in the master database in Amazon RDS

texts:
I am trying to install sp_blitz on AWS RDS SQL Server (14.0.3223.3) standard edition and getting following error:

Msg 262, Level 14, State 18, Procedure sp_Blitz, Line 1 [Batch Start Line 0]
CREATE PROCEDURE permission denied in database 'master'.
Msg 208, Level 16, State 6, Procedure sp_Blitz, Line 2 [Batch Start Line 3]
Invalid object name 'dbo.sp_Blitz'.

Please advise, thanks.


texts:
I request your kind support with the following case.
I have this score table as follows:

create table score (idscore int primary key auto_increment,
                    ndeporte varchar(45),
                    category varchar(45),
                    gender varchar(45),
                    team1 varchar(45),
                    score1 int,
                    team2 varchar(45),
                    score2 int,
                    team3 varchar(45),
                    score3 varchar(45)

)

MY SCORE TABLE

NOW I WANT TO GET THIS RESULT.

I have doubts on how to order my data from my table so that it shows as well as the image.
I request your kind support

I am working on MYSQL 8


texts:
server OS: Ubuntu 14.04
MYSQL Server version: 5.5.43-0ubuntu

The output of:

select User, host from user

is:

db    %
db    localhost

In my /etc/mysql/my.cnf, I made sure that bind-address is set to the real IP address of the server. I can connect to the server from my laptop using sftp and ssh

Yet, it refuses to connect me when I try to connect using mysqlbench error: can't connect to MySQL server on 'xxxxxxx' 10061

I can also access the web server fine from a browser, it displays the default ubuntu page.

What am I missing?

Update:

I was able to connect when I did: bind-addess=0.0.0.0

This obviously creates a security risk, as you can connect from any IP address, but there was an issue with the way Amazon EC2 assigns internal and external IP addresses, so I have to figure out the correct IP address to bind to.


texts: How to rewrite subquery to return only 1 row

texts:
I need to somehow check if query result is NULL, MySQL returns error Subquery returns more than 1 row. How should I rewrite it in order to work correctly please?

SELECT DISTINCT user.id, user.username, user.name, user.surname, user.avatar,
(SELECT IF(ISNULL(
  (SELECT attendant.fk_user AS u
  FROM attendant
  WHERE fk_event = 1

  UNION

  SELECT organizer.fk_user AS u
  FROM organizer
  WHERE fk_event = 1

  UNION

  SELECT invitation.fk_user_to AS u
  FROM invitation
  WHERE fk_event = 1)), NULL, 1)) AS related
  ...


texts:
I need to create a system where the user creates dynamic filters based on our customer's attributes. There are more or less 30 possible filters and 30 millions of customers, but the number of customer increase every day and the attribute value can change every day too, so we have insert and updates in these set of data every day. Another thing is that I can create a new filter or remove.
In this case we can use a relational database like Oracle and create a index for every colunm, but with inserts and updates every day, can I have a problem with performance? Should I use a search engine for this case like Elasticsearch? Or there is a recommended database or architecture for this use case?
I need to return a count of customers that match these filters at most in 5 seconds.
EDIT
Some attributes:

Downloaded the app (boolean)
Credit card limit (number)
Last transaction (date)
Status (text)
Last access (date)
How many times used the credit card (number)
City (text)
Average transaction value (number)

The user can use >, <, =, >=, <= to filter or use IN, like city IN ('New York', 'Seattle')


texts: PowerShell script to send an email with SQL results in HTML format

texts: Does anyone have a PowerShell script to query results from a SQL Server database and send it out as an email in HTML format?


texts: How to setup Mysql master-slave replication with the slave meant for testing where divergence is OK

texts:
Problem:
I have a staging DB server on which functionality is tested before pushing it to the production environment. Now, we want to start doing VA/PT (Vulnerability Analysis and Penetration Testing) on our application, but since that can be destructive on the staging DB server, we want to make a separate Testing environment with a VAPT web+DB server.
Requirements:

The data from the staging DB server must be replicated onto the VAPT DB server automatically so that specific new use cases, features, etc can be tested for vulnerabilities.

Due to VAPT activities (testing data, SQL Injection, possibly DROP TABLE exploits, etc) the VAPT DB server itself will also have its own data changes, i.e. divergence from Staging DB (Master)

So, if I use simple Master-Slave replication as below I am assured of #1:
Staging DB (Master) -> VAPT DB (Slave)
But if I do #2, the slave will eventually diverge, which is fine for the testing environment, but, will it interrupt or mess with the Master-Slave replication as per #1?
An obvious example where divergence will cause errors is a VA/PT activity that causes DROP TABLE users so that the Staging DB (Master) users table trying to INSERT/UPDATE data will cause replication errors. Some UPDATEs/DELETEs might cause errors too.
In particular,
If I use ROW-based replication divergence will happen quickly causing frequent errors.
If I use STATEMENT-based replication, since ids will not match, it is possible that some data will break because ids are essential to link data in related tables even though we do not use foreign keys.
Alternatively, instead of replication, I could manually dump the Staging DB into the VAPT DB daily, which would be cumbersome to automate.
OR,
I could make copy DBs and setup various partial copy operations, but that would complicate matters too much, given that I am not a developer and that my developers often make and revert changes of various sizes randomly.
EDIT: The data directory varies between 20-25 GB on Staging
Surely someone has come across this problem in their work so far and there might be a set of best practices for this situation i.e. maintaining a match between staging and testing environments in real-time while allowing testing freedom to play with the data.
I tried googling for a while but the right phrasing for google escapes me. All I get is howtos for master slave replication, handling unwanted drift/divergence and so on. Nothing much about desired/accepted drift and divergence or partial replication.
Thanks in advance.


texts:
Please read the following code
SET IMPLICIT_TRANSACTIONS ON

BEGIN TRANSACTION
DELETE FROM xxxx
SELECT * FROM xxxxxxxx
WHERE EXISTS (  SELECT * FROM xxxxx
                WHERE CONCAT(xxxxxxx) =  CONCAT(xxxxxxx)
            )

COMMIT

After this I run the following SP
CREATE PROCEDURE [dbo].[xxxxxx]
AS
BEGIN
---To remove any duplicates
WITH CTE AS(
   SELECT xxxxxx, xxxxxx, xxxx,
       RN = ROW_NUMBER()OVER(PARTITION BY xxxxx, xxxx ORDER BY xxxx)
   FROM xxxxxxx
)

delete FROM CTE WHERE RN > 1

---Insert and load
insert into xxxxxxxx
select xxxxxxx.*
from xxxxxxx
left join xxxxx
on
    xxxxxxxxxxx=xxxxxxxx COLLATE SQL_Latin1_General_CP1_CI_AI

where xxxxx is null

truncate table xxxxx_Staging

END

The above SP stuck and continue to run due to lock. Why my SP is stuck even I am using Being Transaction before Delete in the first staement. I dont want to use with(NOlock) option in the SP. Anyway to write SP in a better way.


texts: pg_restore fails on foreign key contraints

texts:
I need to restore a PostgreSQL DB from a backup that was created using "custom format" so that a single table could be extracted from it later. (Per the documentation individual tables cannot be automatically extracted from a plain text backup.)

Because the backup was created as a binary file I do not have the option of editing it, and can only restore it with pg_restore.  Because it is not loaded in the DB, I don't have the option of setting the constrains disabled. (catch-22)

When I try to use pg_restore to restore the custom dump, it fails because of foreign key constraints.

I have looked at perhaps a dozen or more posts on pg_restore and foreign key constraints, but they all require that the DB be already loaded in PostgreSQL so that I can disable the constraints.

I need to restore the entire DB from scratch, both the structure and the data. The backup already exists. I am not looking for a different way to dump it in the first place (although I'd like to learn about that if that is the only option). I have not found anything in the documentation for either pg_dump or pg_restore that addresses this.

I'm finding it difficult to think that PostgreSQL would allow a non-restorable dump to be created. Surely there is a simple answer to this?

The Question: Is there a way to restore a custom format dumped PostgreSQL DB, both schema and data, that contains numerous foreign key constraints?

---- EDIT:

I am more than slightly surprised, but as zsheep suggested, it appears the backup was damaged.

Starting from scratch, I can now go through the entire process of backup, remove the existing DB, and restore from backup (confirming at each step) with no failures or errors:

pg_dump -Fc -f DBnnn-BLANK my_db

psql -U my_user -d postgres

drop database my_db;\q

createdb -T template1  my_db

pg_restore -d my_db DBnnn-BLANK


texts: SQL Server Standard Edition CPU + Tempdb Configuration

texts:
I have SQL 2014 sp3 (Standard) With below CPU Details according to 8 CPU we have configured 8 Tempdb datafiles.

Based on my configuration could you please suggest what should be the CPU & Tempdb configuration for the same.

I am facing the Top 2 Wait type to the server.

ASYNC_NETWORK_IO
SOS_SCHEDULER_YIELD


texts: Query that always worked now hangs indefinitely

texts:
many years ago I coded a stored procedure containing the following SQL (for a client's backend website) and it has always worked fine for them right up until a few days ago when it started to fail with a timeout message. When I run the SP directly in SSMS it just doesn't terminate, I left it running for 90 minutes and it neither timed out nor completed. In the past it would have run fine in just a few seconds.

During the query the CPU maxes out, but RAM usage stays reasonable.

We have moved to a faster server with more RAM and the latest version of SQPEXPRESS (10.50.4000.0) but this has not fixed the problem. (BTW i'm aware of the single-core limitation of SQLEXPRESS, but that's what we are having to use)

The database tables involved are pretty large, but have only been very slowly increasing in size over the years and the code has always worked pretty quickly right up until recently.

Unfortunately the project was so long ago that I have very little idea about what is going on in the code even though I created it myself!

My hunch is that the query involves an exponential increase in CPU cycles as the table sizes increase, and that we've now hit a critical point in size.

My knowledge of SQL Server is patchy, especially after several years of non-involvement. Would I be right in thinking that this can be tackled by adding indexes? If so how should I go about it?

Or perhaps the script needs to be recoded using a different approach?

Any help greatly appreciated!

select DISTINCT
Pprin.product_id as printerID,
dbo.deduceMFRfromSubCatName(subcat.category) as MFRdeduced,
RTRIM(ISNULL(ECAT.category,'NOCAT')) + '///' + RTRIM(ISNULL(SubCat.category, 'NOSUBCAT')) + '///' AS catPath,
ISNULL(PO.forceName,Pprin.model_name) as approvedPrinterName,
max(M.dateCreated) as newestProductCreated,
max(M.lastUpdated) as mostRecentProductEdit,
max(M.lastIceCatFetch) as latestProductSheetFetch,
isnull(PO.dateForced, '1-Dec-2012') as nameApprovalDate

from xtra_icecat_printers1_rel_carts2 R
inner join xtra_icecat_allproducts Pcart on R.prodID2 = Pcart.product_id
inner join xtra_itemmaster I on dbo.dataSKU(I.SKUlite) = Pcart.SKUlite
inner join xtra_SKUmaster M on dbo.dataSKU(M.SKUlite) = Pcart.SKUlite
inner join xtra_icecat_allproducts Pprin on R.prodid1 = Pprin.product_id
inner join xtra_icecat_productdata Dprin on R.prodid1 = Dprin.product_id
left join Xtra_IceCat_Cats CA on CA.catID=Pprin.category_id
left join Xtra_IceCat_PrinterNameOverride PO on R.prodid1 = PO.printerID
left join category ECAT on ECAT.CatID=I.CatID
left join subcategory Subcat on Subcat.SubcatID=I.subCatID
where copyOfPrinterID=Dprin.product_id and isnull(PO.forceName,'OFF')<>'OFF'
group by Pprin.product_id, forceName, ECAT.category, subcat.category, Pprin.model_name, PO.dateForced
order by Pprin.product_id

dbo.dataSKU function (from comments):

ALTER FUNCTION [dbo].[dataSKU] (@SKUlite as varchar(25))
RETURNS varchar(25)
AS
BEGIN
  IF @SKUlite LIKE '%COMP'
    SET @SKUlite = LEFT(@SKUlite, LEN(@SKUlite)-4)
  RETURN @SKUlite
END;


texts: Is there a way to see if database has been accessed through SQL Injection attack?

texts: I recently discovered that my website had a page with a SQL Injection vulnerability. Upon testing, it was easily exploitable with a pentesting tool like sqlmap. How do I figure out if the site database has been accessed with a SQL Injection attack, possibly exploiting this loophole? The server is running Apache/2.4.18 and MySQL Ver 14.14 Distrib 5.7.16.


texts:
Could someone give me a brief explication about these two mechanisms in oracle : stored and external procedures.

Are they distinct ? Are they differ when we integrate in some language (Java, C++ or C ...) ?


texts: getting records for yesterday day starting till ending PostgreSQL

texts:
Currently my application is running over 2 dbs one is snapshotDB one is evercamDB. In snapshotDB we are saving records now it want to retrieve them , currently am using this query

select camera_id,count(*) as snapshot_count
from snapshots
where snapshot_id in (select snapshot_id
                      from snapshots
                      where created_at = 'now'::date - 1)
group by camera_id

what i want is to get records from snapshots such as am considering today is 2016-01-21 and i want to get all records from yesterday. '2016-01-20 00:00:00' to '2016-01-20 23:59:59'. i really dont know how to bind time with 'now'::date - 1 in such format as i have written.

And i want to place this date and time in (select snapshot_id from snapshots where created_at = 'now'::date - 1) so that i can get records as i want.

Any suggestion or help will be appreciated


texts: How to interpret undo statistics in Oracle AWR report?

texts: How can I use undo statistics in oracle AWR report for tuning Oracle? which indicators are important to identify that size of undo segments needs to be increased?


texts: Official PostgreSQL Capitalization conventions

texts:
Is there an official PostreSQL convention regarding capitalization in DB, Table and field names?

The examples on the official site suggest a lowercase and _ word separation, and I wonder whether this policy is official.

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);


texts: Get the most present elements from many fields

texts:
Here is my table:

id|item0|item1|item2
--+-----+-----+-----
1 | 10  | 12  | 12
2 | 12  | 68  | 13
3 | 15  | 13  | 10

I just want to get the most present number of all itemX columns. The query result should be: 12, 13, 10, 15, 68.

I know how to do this for one field:

SELECT item0 from finalBuild GROUP BY item0 ORDER BY COUNT(item0) DESC

But no for several at once.


texts: Auto increment char varying column prefixed with some characters

texts:
I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well.

Is there a way to achieve this?


texts: SELECT , GROUP AND DISPLAY IN TABULAR FORM AND COUNTING INSTANCES

texts:
Answer to this query was received as -

SELECT location,
   AVG(CASE WHEN qtr = 'Q1' THEN achievement END) q1-average,
   AVG(CASE WHEN qtr = 'Q2' THEN achievement END) q2-average
FROM sourcetable
GROUP BY location

Now, the problem is while counting the instances generated with the above query. The final output I require as shown below -

Can such output be generated using a single query?


texts:
In this normalization question

When we convert it into 1NF according to the given site the answer is

Is there a need to take out movie category?
Won't it cause information lose?

Also when we convert it in 2NF the table of movies doesn't contain category column.

Isn't there transitive dependency between movie name and its category?
What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples


texts: MySql: does max_user_connection concern SUPER user?

texts:
On MySql 5.7 I've setup variable max_user_connection = 35.
I have several users, their number of simultaneous connections is effectively limited.
Now I have created a brand new user with SUPER privilege. Is this user's connection is concerned by the max_user_connection variable too ?
Thanks
Edit: I know the max_user_connection documentation, but this specific point is not covered.


texts: Size of Partitions in Partitioned Tables

texts: According to the following link, it is advisable to use hash partitioning when, "The sizes of range partitions would differ quite substantially or would be difficult to balance manually." This implies that when creating partitioned tables, the partitions should be relatively similar in size. Is this true?