texts: How can I flatten value fields from a mapping table into a single column on an output report?

texts:
I have a mapping table structured similarly to the following:

ID       EntityID   EntityName  EntityCategory   ValueID
-----------------------------------------------------
 1          1       System      Category1        50
 2          1       System      Category1        51
 3          1       System      Category1        52
 4          1       System      Category1        53

where ValueID is a FK to another table:

ID     Value
----------------
50     Red
51     Blue
52     Green
53     Yellow

I need to output a report for business users where each row in the report represents one primary-keyed row in this table, but the "Value" column is a flattened concatenation of the values related to ValueID, like this:

Name     Category    Possible Values
------------------------------------------------
System   Category1   Red | Blue | Green | Yellow

How can I write a query to produce this "flattened" output?


texts: Postgres not able to recover in replication mode

texts:
Because of manual cleanup of WAL files, my DB is corrupted. PostGres is throwing this error :

2017-08-10 20:44:41.887 UTC 2661    LOG:  database system was interrupted;
last known up at 2017-08-03 04:07:25 UTC

2017-08-10 20:44:42 INFO:   preparing to get xlog FROM: /ghostcache6/postgresql9.1/drupal_db_1/wal_incoming/192.168.44.167/00000021.history.??????????.gz  TO: pg_xlog/RECOVERYHISTORY

2017-08-10 20:44:42 INFO:   Skip processing '.history' file.

2017-08-10 20:44:42.040 UTC 2661    LOG:  starting archive recovery

2017-08-10 20:44:42 INFO:   preparing to get xlog FROM: /ghostcache6/postgresql9.1/drupal_db_1/wal_incoming/192.168.44.167/0000002100000FD2000000A1.??????????.gz  TO: pg_xlog/RECOVERYXLOG
2017-08-10 20:44:42 INFO:   SWITCH TO LIVE MASTER DB TRIGGERED
2017-08-10 20:44:42.048 UTC 2661    LOG:  could not open file "pg_xlog/0000002100000FD2000000A1" (log file 4050, segment 161): No such file or directory
2017-08-10 20:44:42.048 UTC 2661    LOG:  invalid primary checkpoint record
2017-08-10 20:44:42 INFO:   preparing to get xlog FROM: /ghostcache6/postgresql9.1/drupal_db_1/wal_incoming/192.168.44.167/0000002100000FD20000009F.??????????.gz  TO: pg_xlog/RECOVERYXLOG
2017-08-10 20:44:42 INFO:   SWITCH TO LIVE MASTER DB TRIGGERED
2017-08-10 20:44:42.055 UTC 2661    LOG:  could not open file "pg_xlog/0000002100000FD20000009F" (log file 4050, segment 159): No such file or directory
2017-08-10 20:44:42.055 UTC 2661    LOG:  invalid secondary checkpoint record
2017-08-10 20:44:42.055 UTC 2661    PANIC:  could not locate a valid checkpoint record
2017-08-10 20:44:42.055 UTC 2660    LOG:  startup process (PID 2661) was terminated by signal 6: Aborted
2017-08-10 20:44:42.055 UTC 2660    LOG:  aborting startup due to startup process failure

Thankfully i have backup of data folder, which i extracted in data folder back. But Postgres is still down with the same error. Can i run pg_resetxlog in this case? Please suggest.


texts: Restore one table from SQL Server data file (.mdf)

texts:
I stupidly deleted most of the contents of a table in my database. I have a backup of the server which has the .mdf on it.

I assume I can make a new db and attach the backup .mdf. Then I can select the records from the backup database and insert them into my live database.

Is there an easier way to do this? Or is that the only way?

Thanks


texts:
Is there any tsql-query/DMV through which I can tell my workload type on database, whether mostly Reads or mostly writes happening?

Thanks in Advance.


texts:
I have a bson file dumped by mongodump command.

How can I read the content of this file to memory with C#??

I don't want to use the mongorestore command.


texts: SQL Server query scanning more partitions than expected

texts:
So my team has the following select statement in one of our stored procedures

SELECT
    ai.Name
    ,dc.Component
    ,SUM(dc.Value) Value
FROM
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601 + ai.DayOfMonth - 1)
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270601 + ai.DayOfMonth - 1)))
GROUP BY
    Name
    ,Component

And we've partitioned the DailyCosts table on the months of each year as follows:

CREATE PARTITION FUNCTION [PF_CostDateByYearMonth](int) AS RANGE RIGHT FOR VALUES (
    20180101,
    20180201,
    20180301,
    20180401,
    20180501,
    20180601,
    20180701,
    20180801,
    20180901,
    20181001,
    20181101,
    20181201,

    ...

    20300101,
    20300201,
    20300301,
    20300401,
    20300501,
    20300601,
    20300701,
    20300801,
    20300901,
    20301001,
    20301101,
    20301201)

We noticed that when we ran the procedure, rather than finding the appropriate partitions right away it looked like it was scanning partitions from either end. For the above example, it viewed partitions 20180101 up to 20190701 and 20301201 down to 20270601 totaling 62 partitions.

When we removed the math from the where statement (+ ai.DayOfMonth - 1), the partitions read dropped down to 2, 20190601 and 20270601, as expected. Note that we use seed data and DayOfMonth is set to 15 on every account.

What causes the server to scan the partitions when this math is included, and is it actually looking through all of the indexes in these partitions or is it just checking their ranges and moving on?

Any and all sources you can provide would go a long way in helping us understand!


texts: Add database to existing MySQL master-master replication which uses binlog_do_db?

texts:
We have 2 Mysql 5.7 servers running in master-master replication mode.

And there is a database replicating between these 2 servers:

binlog_do_db = database1

Now we need to add a new database into replication, I have read Add database to existing replication which uses binlog_do_db?, but its for master-slave. Is there any other things should be considered for master-master situation?


texts: Accepting only SSL connections to MS SQL server

texts:
I have followed http://support.microsoft.com/kb/316898 to force encryption on my MS SQL server. My intention is to reject any client connection that does not use SSL to connect. Am I on the right track?

Here is my detailed steps :

makecert -r -pe -n "CN=slc02xla.company.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 c:\my.cer
I imported the same certificate into the trusted Root Certification Authorities Store
In SQL Server Configuration Manager, expandrf SQL Server Network Configuration, right-clicked Protocols for , and then selected Properties.
On the Certificate tab, selected the desired certificate from the Certificate drop-down menu, and then clicked OK.
On the Flags tab, selected Yes in the ForceEncryption box, and then clicked OK to close the dialog box.
Restarted the SQL Server service.

Anything missing?


texts: How can I setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group?

texts:
I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees".

The goal: To setup things in a way that allows all of the users to work with all of the objects in the database.

The problem: I can't expect the users to set the owner to "employees" when they create a new object, because they use various limited interfaces to work with the database. When they create a schema or a table, it gets created with the user as its owner, which means that the other users don't have rights on that schema/table.

I'm using PostgreSQL 11.2.


texts: SQL Server Replace not returning expected result

texts:
Shouldn't the below code return 0?

SELECT REPLACE(ISNULL('',0),'',0)

This doesn't return 0 either....

SELECT REPLACE('','',0)

I have an nvarchar field in a table that contains numerical values but sometimes is blank (not null).

I have a query that checks if that field is < a number in another field but I get

  "Error converting data type nvarchar to numeric."

errors if the field is blank. I am trying to convert the blank values ('') to zeroes during query execution so that I don't get the conversion error but I just realized that

SELECT REPLACE('','',0)

will not convert the blank field to a 0.

I tried using the below code to try and avoid the conversion error but it is not evaluating to 0 when the threshold field is blank as I expected:

SELECT CAST(REPLACE(ISNULL(Threshold,0),'',0) as decimal(4,2))

How can I get the code to evaluate to 0 when the threshold field is blank.

I'm guessing I could write an if or case statement that skips the blank ('') value threshold but I am thinking there has to be a way to do it all inline, something like what I am trying to do with the above code.


texts: can we call CRUD operations as service methods?

texts: I was asked this question by one of my superiors. The usual operations we do on database table from Service layer in java is Create, Update, Retrieve, Delete. These operations are basically services, so is that is the reason why they call it as Service layer also can we call all the CRUD operations as service methods?


texts: How to turn off all RAISE NOTICE on production server?

texts: I put a lot of RAISE NOTICE in functions for debugging purpose. I think this will affect performance, so they should be removed on production environment. Is there any way to opt-out those logging/assertion functions only under specific conditions?


texts:
I have been trying to get an order by to work correctly with an aggregate function.

i.e. order by (bp + points)

I also try adding these as a total amount from the following query however. If I try to add the two columns together I get an error that the field bp can not be found.

If I try to sort by the addition of these fields it doesn't sort correctly and just orders by points

query

SELECT COUNT(game.game_id) AS correct_picks,
       sum(game.points) points,
       pick.user_id,

       (select sum(game.points)
        FROM xf_nflj_pickem_pick pick
        LEFT JOIN xf_nflj_pickem_game game
        ON game.game_id = pick.game_id
        WHERE game.bonus=1 AND pick.user_id = user.user_id
            and game.week_id <= 8 AND game.winner = pick.team_id
        Group by user.user_id) as bp,

       (bp + points) as total,

       user.user_id,
       user.username,
       user.avatar_date,
       user.avatar_width,
       user.avatar_height,
       user.gravatar,
       user.gender
FROM xf_nflj_pickem_game game
    LEFT JOIN xf_nflj_pickem_pick as pick
        ON (pick.game_id = game.game_id)
    LEFT JOIN xf_user as user
        ON (user.user_id = pick.user_id)
WHERE (game.winner = pick.team_id)
    AND game.week_id <= 8
    AND game.pool_id = 1
    AND pick.team_id <> 0
GROUP BY pick.user_id
ORDER BY (points + bp) DESC, user.username, pick.user_id ASC

Results if I just use the order by

correct_picks | points| user_id| bp|
16            | 16    | 845    | 3 |
14            | 14    | 1698   | 3 |
16            | 16    | 109    | 3 |
19            | 19    | 26787  | 3 |

so there are lots of random results here rather than being ordered by by the following

User_id, 26787, 845, 109, 1698


texts:
i need to collect some values from multiple tables and sum this values as a column to the result table.
my query looks like this:
SELECT
u.ID as id,
(
    SELECT `meta_value` as user_name FROM `wxe4_usermeta`
    WHERE `umeta_id` = u.ID AND `meta_key` = 'nickname'
) as user_name,
(
    SELECT SUM(rounds) FROM wxe4_170 WHERE user = u.ID
) as a170_score,
(
    select IF (count(*) > 0, count(*)*66, 0) FROM wxe4_aroundtheworld WHERE user = u.ID
) as atw_score,
(
    select IF (count(*) > 0, count(*)*100, 0) FROM wxe4_X100 WHERE user = u.ID
) as x100_score,
SUM(a170_score + atw_score + x100_score) as darts_total_thrown
FROM darts.wxe4_users as u

the sum throws a "Error Code: 1054. Unknown column 'a170_score' in 'field list'" Error.
what i get without sum:
| id | user_name | a170_score | atw_score | x100_score |
--------------------------------------------------------
| 1  | someUser  | 449        | 3102      | 200        |

what i expect with sum:
| id | user_name | a170_score | atw_score | x100_score | darts_total_thrown |
----------------------------------------------------------------------------
| 1  | someUser  | 449        | 3102      | 200        | 3751              |

Why cant i access these values and how to resolve this?


texts:
I have a very common query where i need to find the list of Users that have same or less than the brokers the user currently have

I want to find the colleague users which have same set of BROKERS or a subset of the user and not any Extra Broker

ID  SiteUserID  BrokerID
5009    19437   765
5008    19437   891
5419    26846   891
5421    26846   765
5117    40348   765
5914    40389   765
5915    40574   891

So for UserID 40574 - it return same 40574

For    UserID 40389 - it return 40389 and 40348 as both have same broker

BUT for UserID 19437 - it will return 19437, 26846, 40348, 40349 and 40574 as the brokers in all the users are same set or subset

I am not able to achive this with simple In operator

Select SiteUserID from SiteUserBroker (nolock) where BrokerID in (
 Select BrokerID from SiteUserBroker (nolock) where SiteUserID = @SiteUserID)

which obviously didn't worked as expected with any simple tweak.

I came up with

Select SUB.SiteUserID from SiteUserBroker SUB (nolock)
inner join (Select Count(*) as TotalBrokers , SiteUserID from SiteUserBroker group by
SiteUserID)Nested on Sub.SiteUserID = Nested.SiteUserID
where BrokerID in (
Select BrokerID from SiteUserBroker (nolock) where SiteUserID = @SiteUserID)
group by SUB.SiteUserID
Having Count(*) - MIN(TotalBrokers) >= 0

Is there a more simpler way!

any ideas are appreciated

Table create - to validate your results

    create table #SiteInfo (ID int, SiteUserID INT, BrokerID int )
      insert into #SiteInfo values (
      5009,    19437,   765),
      (5008,    19437,   891),
      (5419,    26846,   891),
      (5421,    26846,   765),
      (5117,    40348,   765),
       (5914,    40389,   765),
       (5915,    40574,   891)

   DECLARE @SiteUserId INT
         SET @SiteUserId = '19437'


texts: SELECT + INSERT + Query Cache = MySQL lock up

texts:
MySQL server seems to constantly lock up and stop responding on certain types of queries and eventually (after couple of minutes of not responding) give up with an error "MySQL server has gone away", then hang again on the next set of queries, again and again. The server is set up as a slave to replicate from a master to dbA, mostly INSERT statements, around 5-10 rows per second. A PHP based application is running on the server that reads the freshly replicated data every 5-10 seconds, processes it and stores (INSERT ON DUPLICATE KEY UPDATE) results in a separate database dbB. All tables use MyISAM engine. A web application displays the post-processed data for the user. In basic terms the processing steps involved are compression of time series data in per second resolution into per minute, hour and day resolutions.

When MySQL locks up, I execute SHOW PROCESSLIST command and I see the following queries:

N  User          Time   Status                         SQL query
1  system user   XX     update                         INSERT INTO `dbA`.`tableA` (...) VALUES (...)
2  ????          XX     Waiting for query cache lock   INSERT INTO `dbB`.`tableB` (...) VALUES (...) ON DUPLICATE KEY UPDATE ...
3  ????          XX     Writing to net                 SELECT ... FROM `dbA`.`tableA` WHERE ... ORDER BY ...

The "Time" column will keep ticking away synchronously until some sort of query wait timeout has been reached and then we get error "MySQL server has gone away". In 5-10 seconds when it will be time to process new data again the same lock up will happen. Query #1 is the replication process. Query #2 is the updating of the post-processed data. Query #3 is streaming (unbuffered) the newly replicated data for processing. It is the Query #3 that eventually produces the error "MySQL server has gone away", presumably because it is the first one to timeout.

It looks like some sort of dead lock, but I cannot understand why. Simultaneous SELECT and INSERT in one database seems to cause a dead lock with query cache update by INSERT ON DUPLICATE KEY UPDATE in a different database. If I turn off either the Replication or the Query Cache then the lock up does not happen. Platform: Debian 7, MySQL 5.5.31, PHP 5.4.4 - all standard packages. It may be worth noting that almost the same application is currently working fine on Debian 6, MySQL 5.1.66, PHP 5.3.3, with only difference in that the post-processed data is stored using separate INSERT and UPDATE queries rather than INSERT ON DUPLICATE KEY UPDATE.

MySQL configuration (on both the Debian 6 and 7 machines):

key_buffer_size         = 2G
max_allowed_packet      = 16M
thread_cache_size       = 64
max_connections         = 200
query_cache_limit       = 2M
query_cache_size        = 1G

Any hints to why this lock up occurs will be much appreciated!


texts: How to assign MongoDB the complete RAM of system?

texts:
I am trying to run the MongoDB with the full capacity of my system RAM. But I am not getting a way to assign complete RAM of my system. PLease advise me what I can do in the cnfiguration, so that the RAM will take the complete RAM of my system.

The RAM capacity is 32 GB.
After system reserve 28 GB.
I want to assign the MongoDB with atleast 25 GB of RAM from my system.
Kindly, advise me.


texts:
I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this:

WITH PIVOT_DATA AS (
     SELECT *
     FROM
     (
         SELECT USERNAME, GRANTED_ROLE
         FROM DBA_USERS@DB_LINK U LEFT OUTER JOIN DBA_ROLE_PRIVS@DB_LINK R
         ON U.USERNAME = R.GRANTEE
      )
)
SELECT *
FROM PIVOT_DATA
PIVOT
(
    COUNT(GRANTED_ROLE)
    FOR GRANTED_ROLE
    IN('CONNECT') -- Just an example
)
ORDER BY USERNAME ASC;

It works really fine and does the job, but I don't want to write to write any role I want to search for in the pivot_in_clause, because we got like tons of them and I don't want to check every time if there are any changes.

So is there a way to write a SELECT in the pivot_in_clause? I tried it myself:

[...]
PIVOT
(
    COUNT(GRANTED_ROLE)
    FOR GRANTED_ROLE
    IN( SELECT ROLE FROM DBA_ROLES@DB_LINK )
)
[...]

But it always gives me an ORA-00936: "missing expression" in line 1 of the whole query and I don't know why. Can't there be a SELECT in the pivot_in_clause or am I doing it wrong?


texts: Best practice for sharing data over differenet mysql/mariadb databases

texts:
I have app that uses DB with 50+ tables. Now i'm in situation that i need to install another instance of app. Problem is that i would like to use some tables as "common" data, e.g. "brands" or "cities" or "countries" in both (for now there are only 2, but it might soon be more) apps.

I searched and find that i can make "common" DB with such tables, and have views in each DB instance that points to corresponding table.

Main app queries are heavily relying on that common tables, so i'm concerned if that will slow down my queries since views don't have indexes?

Are there some better practices? I'm looking now for replication in mysql manual. Is that way to go? Replicate tables from common DB to app instance DB's? Can this be one-direction replication? (only tables in "common" DB can be changed and then replicated to other DB's)?

thanx for advice Y


texts: Saving images as base64 encoded strings, why is it bad?

texts:
I've seen this on one of the production databases I've come across and these images apparently cover a large portion of their DB. After researching a lot I couldn't really find a lot of good answers pertaining as to why it is actually bad in terms of performance.

Yes, it is not stream friendly and takes up ~33% more space: https://stackoverflow.com/questions/9722603/storing-image-in-database-directly-or-as-base64-data

Sure, it gives wrong hints to DB: https://stackoverflow.com/questions/9722603/storing-image-in-database-directly-or-as-base64-data

And other answers like: storing files on mongodb don't really explain why this is problem

But, what is actually going on that might cause databases that store images as base64 encoded string to lose performance? Why would a DB lose performance on selects(or any other aspect) due to such practices?

I would also like an answer that specifies a NoSQL(MongoDB) vs RDBMS(PostgreSQL) performance in this regard. Does the nature of having things in memory like MongoDB make the performance hit even worse? And if so, why? How is this image saved as a base64 string behaving on both systems?


texts: MySQL - SQL statement to test minimum length of the word 'ft_min_word_len'

texts:
I'm trying to understand FULLTEXT indexes.

Is there some SQL query to test 'ft_min_word_len' variable?

I've tried to match four characters only in the example below but it doesn't work. Why the third row is returned if there is no match for '(database|data)'

mysql> SHOW VARIABLES LIKE 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 4     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE articles\G
*************************** 1. row ***************************
       Table: articles
Create Table: CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' WITH QUERY EXPANSION);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
+----+-------------------+------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('data' WITH QUERY EXPANSION);
Empty set (0.00 sec)


texts: Error when using WITH (DROP_EXISTING = ON)

texts:
Okay so I've been condensing files off of some tiny files and moving them onto an FG that I created for these miscellaneous tables. I've been moving these tables by recreating the indexes with the WITH (DROP_EXISTING = ON) option and it's been working great. However this last time it gave me an error. Here's the script I'm running
CREATE Unique CLUSTERED INDEX PK_MyTable
    ON dbo.MyTable (MyTableID, RowStatus)
    WITH (DROP_EXISTING = ON)
    ON FG_Misc;

When I run this I get the following error message.

Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'dbo.MyTable'. Drop the existing clustered index PK_MyTable' before creating another.

I don't see any reason why this is throwing this error on this particular table but not any others I've been working on.


texts: How to secure SQL Agent Job that INSERTS to a linked server

texts:
What is the proper way secure a T-SQL agent job that moves data between linked servers?

Setup:

Microsoft SQL Server 2012 (SP3) Enterprise Edition
SQL Server Agent running as NT Service\SQLSERVERAGENT
Microsoft SQL Server 2008 R2 (SP3-GDR) Express Edition
Servers are linked to each other
Link security context set to "Be made using the login's current security context."

My goal is to select data from the Enterprise server and insert it into a table on the Express server. The Express server is running vendor software that needs integration with our main database.

I'm trying to use a dedicated account with minimal permissions for this job because the Enterprise server is our core server with a variety of workloads, and I don't really trust the Express server hosting the vendor software will always be secure.

I tried creating an Active Directory user with the necessary database and table permissions on each server, but then I found that Agent Job steps of type T-SQL cannot use proxies to run as another user.

What's the correct way to do this? The solutions I'm finding look silly or risky, like lowing the security context of the server link, making a PowerShell wrapper, or putting credentials in the T-SQL itself. Surely there's an elegant, secure way to move data between linked servers on a schedule? Or is SQL Server lacking in this area?


texts: Implications of changing compatibility mode in SQL Server from 100 to 110

texts:
I have a database Mydatabase created in SQL Server 2008 R2. I have upgraded to SQL Server 2012.

I was trying to execute below query to calculate percentile

select Distinct [KEY],PERCENTILE_CONT(0.25)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q1,PERCENTILE_CONT(0.50)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q2,
PERCENTILE_CONT(0.75)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q3,
PERCENTILE_CONT(1)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q4
from Mydatabase

but I am getting an error stating that

  Msg 10762, Level 15, State 1, Line 1
  The PERCENTILE_CONT function is not allowed in the current compatibility mode. It is only allowed in 110 mode or higher.

Can I change compatibility mode to 110?
What are the implications of changing compatibility mode from 100 to 110?

Please advice


texts: Find closed sub directory with full text search

texts:
I have a simple table with registered directory names inside a full text indexed varchar column.

For example:

'c:\Dir1\'
'c:\Dir1\Sub1\'
'c:\Dir1\Sub2\'
'c:\Dir2\'
'c:\Dir3\'

I now need to find the closest subdirectory to file paths like:

'c:\Dir1\File.txt' -> 'c:\Dir1\'

'c:\Dir1\Sub2\File.txt' -> 'c:\Dir1\Sub2\'

'c:\Dir2\Sub1\SubSub1\File.txt' -> 'c:\Dir2\'

All my efforts with CONTAINS and NEAR failed.
Can somebody help me how I can solve this with text search?


texts: Query takes too much time (2 stages. each stage fast, together - nightmare)

texts:
MS-SQL 2008 R2 (Dell, 2 CPU)

This query takes two minutes to run, which is too slow.

SELECT *
FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190
and event_no <= ( SELECT MIN(event_no)
                  FROM t_events WITH (NOLOCK)
                  WHERE order_no = 2783190 and complaint_id = 80898
                )

However, If I run:

SELECT MIN(event_no) FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190 and complaint_id = 80898

it returns the result, 18880040, in less than a second.

If I run:

SELECT * FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190
and event_no <= 18880040

it returns about 17 lines in 1 sec.

However, together the query takes 2 minutes to return the same 17 lines.

Why is this happening?


texts: The simplest way to copy sql tables from one database to another?

texts:
So I have been logged into two different database. I want to copy one table from the first database to the other one with its content. I want to overwrite the content of the destination one (Because I have the same table name but it has just few entries) So what is the simplest way to do it?

PS: I am using Microsoft SQL Server Management Studio 2019 and each database located on different server.


texts: How do I convert a BIGINT to a DATETIME in Informix 12.1

texts:
I'm new to Informix, and I'm trying to convert a bigint to a datetime.

SELECT sdatetime
FROM CallDetail;

Results:
sdatetime
----------
1572509662678
1572518550704
1572519033540

This seems to be the number of ms from 1-1-1970 but I can't get the syntax right to convert this.  The closest I got was getting a date with no time.  I need both.

How can I do this?


texts: Duplicate result when I select with distinct values from foreign key constarint

texts:
I want to select values from two different tables which have one to many relationship between them.
During select it's retrieving duplicate values even when I use distinct and group by. Above table is person table and the beneath one is address.

I am using this query to retrieve matched records.

I want to group address into single record but its working am I missing something?
I want my result to look like this


texts:
I have the following table in redshift:

 Column | Type
 id       integer
 value    varchar(255)

I'm trying to copy in (using the datapipeline's RedshiftCopyActivity), and the data has the line 1,maybe as the entry trying to be added, but I get back the error 1214:Delimiter not found, and the raw_field_data value is maybe.  Is there something I'm missing in the copy parameters?

The entire csv is three lines that goes:

1,maybe
2,no
3,yes


texts:
I'm importing a largish DB into MySQL (20tables of ~25mill records).

As expected this is taking a long time, but I'm curious as to why the process is running using only 0.000 to 0.700 %CPU and 0.87 %MEM ?

Is there anyway to force the process to use more resources? I've already tried messing around with its nice value.

Seems odd as Googling people importing large databses say they see MySQL eat up near to %100 of their CPU.

edit:

Some more info -
Import has been running for 25 hours now,
top shows CPU TIME as only 6:19.12
VIRT - 106832
RES - 3432
SHR - 484


texts: Informix onunload/onload between different arch

texts:
I have some necro-project using Informix Dynamic Server Version 7.31.UD1.
I need to backup-restore database between servers. The thing is, the onload just silently exits and doesn't create a database, giving no errors. I've successfully used this method on the same server to perform a backup/restore routine, so I'm almost sure I'm doing it right:
<console A>
[root@s10-zone informix]# rm -rf /tmp/.dbld
[root@s10-zone informix]# mkfifo /tmp/.dbld
[root@s10-zone informix]# cat /opt/oracle/exchange/perms32_unl > /tmp/.dbld

<console B>
[informix@s10-zone ~]$ onload -t /tmp/.dbld -b 100 -d default rist
Please mount tape and press Return to continue ...
<I press Enter, 2-5 seconds pass, nothing happens>

The only explanation so far is that the onunload backup was don on a i386 arch, and I'm doing onload import on an amd64 server, - am I right assuming it fails because of this ? The official documentation says "If the page sizes are different, onload fails. If the alignment or numeric data types on the two computers are different (for example, with the most significant byte as last instead of first, or different float-type representations), the contents of the data page could be misinterpreted.", but the page size of identical (4K), not so sure about float representation though.


texts: What is going wrong with postgresql initdb? Why is the `UTF-8` encoding not getting enforced?

texts:
I am using PostgreSQL 9.1. Trying to enforce UTF8 encoding as default.

This is what I am doing.

service postgresql initdb -E 'UTF-8' \
               --lc-collate='en_US.UTF-8' \
               --lc-ctype=locale='en_US.UTF-8';

Although the initilization process goes on without any problem,

a \l at the psql prompt gives there details.

                             List of databases
   Name    | Owner    |Encoding  | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | LATIN1   | en_US   | en_US |

Why is the UTF-8 encoding not getting enforced?


texts: What data structure is used for MongoDB 3.x geospatial indexes?

texts: For the MongoDB 3.x geospatial features what structure is used for the indexes? B-tree? R-tree? X-tree? I can't find any information about the structures in their documentation


texts: Query job history for TSX servers from MSX server using TSQL

texts:
I am attempting to construct TSQL queries to substitute various GUI tools provided by SQL Server Management Studio. One of these tools is the Job Activity Monitor, accessible through the Object Explorer window.

Our current database environment includes a cluster with 3 nodes(one primary and two read-only replicas), as well as a single, independent server standing next to the cluster.

For brevity, I'll call the nodes N1, N2 and N3, and the independent server S1.

Recently, we configured Multi-Server Job Administration for our servers, using S1 as the MSX (master) server, and N1, N2 and N3 as TSX (target) servers.

This means that SQL Agent Jobs that operate on the cluster-nodes are created and managed from S1.

TSX servers report their state, outcome etc to the MSX server, which can be
accessed using said Job Activity Monitor. From the Job Activity Monitor, the job-history of each enlisted server (TSX) can be accessed by selecting View history:

Which takes us to this window:

This is the information I am trying to recreate, using a TSQL query on the MSX server. I want to see the outcome and history of the jobs by all enlisted servers, similar to what is displayed by the GUI window.

I've tried digging through the job-related tables and views of the msdb database, but with no luck. The msdb.dbo.sysjobhistory table on the MSX server contains no history from enlisted servers, and I can't seem to find any good documentation on how else I would go about gathering it. Is this data even accessible through TSQL? Any relevant resource is greatly appreciated.


texts: Howto disable Postgres listening on TCP?

texts:
Simply to avoid many problems in the first place I do not want my postgres server program to accept/listen to anything from any network (i.e. TCP/IP 4/6) connections.
My setup is a Postgres 9.1 on an Ubuntu 12.04 box and I thought tweeking /etc/postgresql/9.1/main/pg_hba.conf to not include those lines which commented out (see below) would cause postgres to "please not listen on network TCP/IP devices"

local   all             postgres        trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
#local   all             all                                     md5
# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
#host    all             all             ::1/128                 md5

Also I know that there is the -i command line to start the postgres server with if we YES want to listen on TCP/IP . I actually seek for the opposite thing a -??? meaning NO please do NOT listen on TCP/IP.
I used a netstat -utap | grep post  and it shows that postgres besides my settings in /etc/postgresql/9.1/main/pg_hba.conf is still listening on  TCP/IP.
QUESTION
What did I do wrong here? How can I shut off this TCP/IP listening attitude of my Postgres server?
Having only unix socket listening I am happy to the max ;)
Thank you
Addition: I also perceive that postgres establishes a UDP connection to this 127.0.0.1:38860, what would this be about?


texts: About Apex SQL Refactor and SSMS keyboard shortcuts being broken

texts:
Hi DBA Stack Exchange!

Today I tried Apex SQL Refactor and right from the get go, once installed and integrated into my T-SQL dev tool of Choice (SSMS 18.3.1) I noticed that some Keyboard shortcuts stopped working.

One example (maybe there are more, but this one I’m pretty sure): After installing and using SQL Refactor for first time (Refactor needs to be used at least once for this to happen) the “Available Databases” functionality in SSMS (which can be invoked by CTRL+U) no longer works. What is worse, this is not a configurable shortcut (like most of the Keyboard shortcuts that can be configured on Tools -> Options -> Keyboard) so once it is broken, the only solution so far (to make it work again) is to uninstall SQL Refactor.

This query shortcut (“Available Databases”) is an extremely useful one that people use it over and over again on SQL Sessions, so breaking the shortcut is a major thing for me.

I’m running the latest available builds of both SSMS (18.3.1) and Apex SQL Refactor (2018.08.0364)

My question is: have you also experienced this? If so, how did you solve the issue of keyboard shortcuts being irreparably lost after Apex SQL Refactor?

If you need me to provide any additional info about my system or logs that could help you reproduce this issue, just let me know.

Thanks to all of you!


texts: How to pass an array of arguments to User Defined Aggregate in SQL Server

texts:
I have a custom aggregation (UDA) and I want to pass an array of arguments like so:

select dbo.MyAggregation(
    t.type,
    N'My aggregation property',
    t.param1, t.param2, t.param3
)
from someTable t
group by t.type

The above is just an example, and I want to pass arbitrary number of params (i.e 4-...).

I've tried to use SqlString as an "array" with some delimiter, and then split the string by this delimiter, but this doesn't seem like a good solution.


texts: Would it be correct to assume that inner query ordering will follow into my outer query?

texts:
Is it spelt out someplace in the documentation that if I do this:
SELECT *
FROM (
  SELECT *
  FROM foo
  ORDER BY bar ASC
  LIMIT 1000
)
LIMIT 10
OFFSET 10

then the inner query ordering will carry on into the outer query?
Or do I need to repeat the inner query ORDER BY in my outer query? This is assuming that we don't have any joins.


texts:
I am using oracle database 11g. Some day's back we have deleted our Archivelog Manually, and now it's getting full and due to that we are not able to connect to our database.

So how should i delete archivelog files :

Is there any way by which we can delete archivelog through rman without connecting to the target database.

Or can we increase the archivelog disk space.

Thanks for the reply.


texts: Execute SQL at different server (with dynamic server name)

texts:
I'm using a settings table to configure on which servers a specific kind of data is stored, and I would like to do this as dynamically as possible without having to add the server as a linked server because these settings can be changed by superusers in an application.

create procedure dbo.getData
begin
    declare @server nvarchar(50) = dbo.getSetting('DATA_SERVER')
         , @user nvarchar(50) = dbo.getSetting('DATA_USER')
         , @password nvarchar(50) = dbo.getSetting('DATA_PASSWORD')
         , @database nvarchar(50) = dbo.getSetting('DATA_DATABASE')

    -- some how connect to the server and make a select on the datatable
end

Is this possible, or what are my alternatives in doing so?


texts:
I have a script that creates triggers,
I would like to run it multiple times and if a trigger already exist it needs to skip the creation part.

Is there a "create if not exist" in mysql triggers?

I tried :

    IF NOT EXISTS ((SELECT TRIGGER_NAME
        FROM information_schema.triggers
        WHERE TRIGGER_SCHEMA = 'xxx_admin' AND TRIGGER_NAME = 'test_AFTER_UPDATE'))
        THEN
    CREATE DEFINER=`root`@`localhost` TRIGGER `xxx_admin`.`test_AFTER_UPDATE` AFTER UPDATE ON `test` FOR EACH ROW
        BEGIN
        INSERT INTO auditTest
        select *, now() from test where id = NEW.id;
        END;
  END IF;

I get : syntex error " 'IF' is not valid input at this location


texts: How to identify when to move all or part of the data from Sybase ASE to Sybase IQ?

texts:
Is there an easy way that any of you guys use to determine this?

Ok, we have more than x gb of data, we should go for IQ!
Ok, we have more than x reports going, we should go for IQ!
Ok, we have a lot of data archived and need better timings retrieving it, we should go to IQ!

What are the factors that you use to make this decision?


texts:
We have a 12 node SQL Server FCI (6 nodes in our primary datacenter and the other 6 nodes at a DR site) which has been working well until an access control list (ACL) was applied to a network switch.

When the ACL was applied, the primary cluster was not able to access/communicate with the 6 DR nodes. As a result, disks on the primary DB nodes started to dismount and the cluster stopped functioning.

Any ideas as to why this is happening?


texts: Only show results greater than a certain number when summing two values together

texts:
I have a query that sums up two values in the same table and then calculates one as a percentage of the other

select account_no,
  100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100)))
from accounts etc

What I want to do is only return results where that value is greater than a certain amount (say 4 in this case). If I add something in the where clause such as:

where 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) > 4

I get the error:

  An aggregate may not appear in the WHERE clause...

I expect there's an easy way round this that I haven't learned yet.  Any ideas?


texts: Is an in-memory table faster to read from than a table cached in memory?

texts:
Is an in-memory (aka Hekaton) table faster to read from than a non-in-memory table that's already cached in the memory of a SQL server?

I ask this because I have a case where I'm considering converting one of my tables to an in-memory table, but right now it's pretty fast to load into the memory cache, but the performance to read / operate against it after it's loaded into the cache is where it's slow.

Can I still see potential performance improvements by converting this table to an in-memory table?


texts:
I am trying to add a new foreign key to an existing table where there is data in the column I am wanting to make a change to.

In dev, I have tried this where data does and does not exist. Where there is no data this works fine.

ALTER TABLE [rpt].ReportLessonCompetency WITH CHECK
ADD CONSTRAINT [FK_Grade_TraineeGrade_Id]
FOREIGN KEY (Grade) REFERENCES [rpt].TraineeGrade(Id)

Where there is data I get the following error

  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Grade_TraineeGrade_Id". The conflict occurred in database "T_test", table "Core.Report.TraineeGrade", column 'Id'.

I would be grareful if someone could let me know what I need to do in order to ensure that this works where data does and does not exist as I cannot control if the live database will or will not have any existing data.

Thanks

Simon


texts: How to allow a user to create databases only with a certain prefix?

texts: We're having a multi-user Linux server. I'd like to give each user the ability to create and manage databases at will, but to keep things sane, force them that each database name they create must start with their user name as prefix. For example, user joe would be only allowed to create databases like joe_app1 and joe_app2 but not app1 or jack_app1. Is this possible in PostgreSQL? Or is there another way how to separate name-spaces for user-created databases?


texts: How to configure SSIS in SQL Server AlwaysOn

texts:
I have AlwaysOn Availability Group setup for my SQL Server. Now I would like to configure SSIS. How do I do it? Below link provides some information but since I am not DBA it's difficult to understand this article:

AlwaysOn Availability Groups and SQL Server Jobs, Part 23: Maintenance Plan Backups and SSIS Packages

Can someone guide me on basic setup of SSIS in an Availability Group and explain how synch up happens between nodes?

Update

I am referring to the SSIS Catalog database (SSISDB)

Can I use listener in my connection managers?


texts:
Can someone elaborate on how the following two queries would provide different results?

That is, how the query is processed differently using WHERE or AND.

select
    orders.*,
    accounts.*
from
    orders
left join
    accounts
    on orders.account_id = accounts.account_id
where
    accounts.sales_rep_id = 321500;

select
    orders.*,
    accounts.*
from
    orders
left join
    accounts
    on orders.account_id = accounts.account_id
    and accounts.sales_rep_id = 321500;


texts: Sql server full text search performance with additional conditions

texts:
We have a performance problem with SQL Server (2008 R2) Full text search. When we have additional where conditions to full-text search condition, it gets too slow.

Here is my simplified query:

SELECT * FROM Calls C
WHERE (C.CallTime BETWEEN '2013-08-01 00:00:00' AND '2013-08-07 00:00:00')
AND CONTAINS(CustomerText, '("efendim")')

Calls table's primary key is CallId (int, clustered index) and also Calls table indexed by CallTime. We have 16.000.000 rows and CustomerText is about 10KB for each row.

When I see execution plan, first it finds full-text search resultset and then joins with Calls table by CallId. Because of that, if first resultset has more rows, query gets slower (over a minute).

This is the execution plan:

When I run where conditions seperately, it returns 360.000 rows for CallTime condition:

SELECT COUNT(*) FROM Calls C
WHERE (C.CallTime BETWEEN '2013-08-01 00:00:00' AND '2013-08-07 00:00:00')

and 1.200.000 rows for Contains condition:

SELECT COUNT(*) FROM Calls C
WHERE CONTAINS(AgentText, '("efendim")')

What can I do to increase performance of my query?


texts: MySQL cluster stuck in start phase 4, how to proceed?

texts:
Over the weekend our cluster we set up for evaluation had one of its data nodes lose network connectivity briefly. This was while another data node was in the middle of a restart (which had been going for 22 hours in phase 5, but that's a separate issue.) I'm seriously glad this happened before going production.

This led to all of the data nodes going offline:

2012-11-23 19:24:19 [ndbd] ALERT    -- Node 1: Forced node shutdown completed. Caused by error 2305: 'Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s)(Arbitration error). Temporary error, restart node'.

The question is: What can I do to get the cluster going again? Alternatively, what can I look at to see what is going on? The logs are essentially empty (as you'll see below). What is safe to do at this point?

Details:

I am trying to get the cluster back online but am having little luck. I started 3 of the data nodes the normal way (ran "ndbmtd") and the data node that went down mid-restart with "ndbmtd --initial". All three "ndbmtd" nodes logged that they completed phase 3, meaning they're in phase 4, and the one "ndbmtd --initial" node completed phase 1 at the same time.

That was all approximately 50 hours ago.

Here's what I have in the --initial node's log:

2012-11-24 07:49:54 [ndbd] INFO     -- Start phase 1 completed
jbalock thr: 0 waiting for lock, contentions: 1 spins: 1
jbalock thr: 0 waiting for lock, contentions: 2 spins: 2
jbalock thr: 0 waiting for lock, contentions: 3 spins: 3
jbalock thr: 0 waiting for lock, contentions: 4 spins: 4
jbalock thr: 0 waiting for lock, contentions: 5 spins: 5
jbalock thr: 0 waiting for lock, contentions: 6 spins: 6
jbalock thr: 0 waiting for lock, contentions: 7 spins: 7
jbalock thr: 0 waiting for lock, contentions: 8 spins: 9
jbalock thr: 0 waiting for lock, contentions: 9 spins: 36
jbalock thr: 0 waiting for lock, contentions: 10 spins: 37
jbalock thr: 0 waiting for lock, contentions: 11 spins: 38

and here's what's at the end of all of the other data nodes logs:

RESTORE table: 655 32 rows applied
RESTORE table: 655 38 rows applied
Applying undo to LCP: 61183
2012-11-24 07:55:06 [ndbd] INFO     -- Undo head - undo_1.log page: 321844 lsn: 245995614
send lock node 49 waiting for lock, contentions: 200 spins: 363193
send lock node 49 waiting for lock, contentions: 400 spins: 695011
send lock node 49 waiting for lock, contentions: 600 spins: 1194842

Node 49 in the management node.

Finally, here's the pertinent entries in the config.ini (only thing excluded are the data node hostnames):

[ndbd default]
NoOfReplicas           = 2      # Number of replicas
DataMemory             = 30G    # How much memory to allocate for data storage (most data will be on disk, indexed attributes in memory)
IndexMemory            = 10G    # How much memory to allocate for index storage
MaxNoOfOrderedIndexes  = 1024
MaxNoOfAttributes      = 6000   # 6 times MaxNoOfTables
MaxNoOfTables          = 1000
TimeBetweenLocalCheckpoints = 8
ServerPort             = 2202
NoOfFragmentLogFiles   = 600
NoOfFragmentLogParts   = 8

ODirect                = 1
SchedulerSpinTimer     = 400
SchedulerExecutionTimer= 100
MaxNoOfConcurrentOperations = 100000
RealtimeScheduler      = 1

CompressedLCP          = 1

ThreadConfig=ldm={count=8,cpubind=1,2,3,4,5,6,7,8},main={cpubind=9},io={cpubind=9},rep={cpubind=10},tc={count=4,cpubind=11,12,13,14},recv={count=3,cpubind=15,16,17},send={count=3,cpubind=18,19,20}

The hardware is all quad-core 2.4Ghz Xeons, if that matters. top on the three "ndbmtd" data nodes shows that ndbmtd is using 100% CPU on one of its threads. On the "ndbmtd --initial" data node there is almost no activity.


texts:
I'm running a Windows service, which connects to a MySql database via Entity Framework.

Every 10 seconds it issues a query to the database to see if any new records have been added. If it finds any, it attempts to process them.

Mostly, this works fine. However, if no data is added over an extended period when a row does get added the database throws an error back at me:

  MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution.
  MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered attempting to read the resultset.
  MySql.Data.MySqlClient.MySqlException (0x80004005): Reading from the stream has failed.
  System.IO.IOException: Unable to read data from the transport connection: An established connection was aborted by the software in your host machine.
  System.Net.Sockets.SocketException: An established connection was aborted by the software in your host machine

After a lot of experimentation, I have discovered this is controlled by the wait_timeout setting on the database. If I reduce this to a few seconds, I can easily replicate the error.

Solving it, however, is a different matter. I could increase wait_timeout but this seems like a bad fix for all sorts of reasons. Not only is it generally seen as a good idea to have this set low, but it's not really a solution at all - any idle period that exceeds the new value will still trigger the error.

I'm struggling to understand why I'm getting this error at all, since I'm issuing queries to the database every five seconds. I've tried forcing the code to open a new connection every time, but it still throws the error when wait_timeout is exceeded. I just can't see how that can be happening when I'm fetching a fresh connection.

I've also tried setting keepalive on the connection string, and setting a min/max on the thread pool to 10/20. Neither fixes the issue.

Oddly the only thing which does "fix" the issue is to record a log (it's saved in the same db) on every pass through the service. Again, this isn't a suitable fix because it will fill the log with garbage in no time at all. But it just increases my confusion: the logging framework uses a different connection - why does issuing a log command keep the EF connection alive?

There are threads and questions about this all over the place, most of which just suggest increasing wait_timeout. What's going on here, and how do I fix it?


texts: Percona Xtrabackup (Partial + incremental)

texts:
we have been trying restore Percona (Partial + Incremental) mysql backup, but failing. The process followed is as below:

For Backup

# Step I: Take Partial-Full Backup

    innobackupex --user=root --password=**** --databases="db1" /path/to/basebackupdir --no-timestamp

# Step II: Take Partial-Incremental Backup

    innobackupex --incremental /path/to/incbackupdir --databases="db1" --incremental-basedir=/path/to/basebackupdir/ --user=root --password=**** --no-timestamp

For Restoration

Step I: apply log to full backup

 innobackupex --apply-log --redo-only fullbackup

Step II: apply-log incremental backup

innobackupex --apply-log fullbackup --incremental-dir=/tmp/tmp_inc

Step III: Export Backup

innobackupex --apply-log --export fullbackup

Step IV: CREATE Required database in MySQL

mysql -uroot -p**** -e "DROP DATABASE IF EXISTS db1"
mysql -uroot -p**** -e "CREATE DATABASE db1"

Step V: Import DATABASE Structure

mysql -uroot -p**** db1 < db1.sql

Step VI: Discard tablespace

Step VII: Import table

    cd /tmp/fullbackup/db1
    cp -R *.ibd /var/lib/mysql/db1
    cp -R *.cfg /var/lib/mysql/db1

Step VIII: Import tablespace

Step IX: restart mysql

    service mysql restart

Restoration process works fine, Still we are failing, that One of the tables could not create the structure for FULLTEXT index(it gets corrupted), when we try to create table manually error is table already exists.

Please Help


texts:
I have been coding in C# for a couple of years where I usually try to follow the 'single responsibility principle' which states that each class should ideally be responsible for only 1 goal. Now I want to design a small database for my app users. My initial go at this is as follows:

I am not sure how the single responsibility principle applies to the database world. Should I split this table into 3 tables, one which holds only the Facebook ID, and another for all high score related fields (HighScore, AllTimeHighScore, ScoreDate, ...) and a 3rd for all medal fields, and maybe a 4th table for just the Expert Mode Score, or keep it as is? I know this is probably overkill, but I would like to know the basic principles regarding what can be grouped in a single table.

Note: I would like to take into consideration future proofing. If in the future I decide I want to add a 'platinum medal' field, would that change at all how I would want to design my above table now?


texts: Functional dependencies (hopefully simple question)

texts:
Consider the simple problem of keeping track of Students:

STUDENT(
   StudentNumber,
   StudentName,
   Dorm,
   RoomType,
   DormCost,
   Club,
   ClubCost,
   Sibiling,
   Nickname
)

Would StudentName functionally determine StudentNumber? Is there not enough information? Is there a rule of thumb about this kind of problem?


texts: Is there, or isn't there a boolean data type in SAP ASE?

texts:
ALL,
According to the Embedded SQL/C manual the DBMS supports the boolean type, to which CS_BIT is mapped on the application side.
However, when I tried to use it inside CREATE TABLE() statement, I got an error
Can't find type boolean
Sure enough, the Transact-SQL manual does not list boolean among its data types. So, is there a boolean data type in ASE, or not? If not, what would be the right data type to use in my case? I need a field that will contain true/false or 1/0.
I will be using SAP ASE 16.0 on Linux.
I can try to use integer, but it will be just an overkill.
EDIT:
I found that there is a type bit that is supported by SAP ASE. But it still would be nice to know why boolean is not supported.


texts: PostgreSQL CAST() giving unexpected results

texts:
I can't seem to figure out how this is rounding overflow digits. It is most definitely not any of:

Round up if last digit is 5 or higher.
Round to even if last digit is 5.
Randomly round up or down if last digit is 5.

In fact, whatever logic it is using is definitely 1) deterministic, and 2) not [completely] based on the last digit being 5.

Some example inputs and output of:

CAST ('input' AS DOUBLE PRECISION);

Here, 'input' is not a variable, just a placeholder in this post for a literal numeric value with quotes around it, e.g. below in input column.

input                 output
34.55555555555595     34.5555555555559    *Obviously not rounding up
34.555555555555951    34.5555555555559    *Why not round up here?
34.5555555555559514   34.5555555555559    *Another unexpected round down
34.5555555555559515   34.555555555556     *Unexpected/seemingly inconsistent
34.55555555555595145  34.5555555555559
34.55555555555595146  34.5555555555559
34.55555555555595148  34.5555555555559
34.55555555555595149  34.555555555556     *Huh!?

Version: PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit


texts: SQL Server Ignoring Instance name when using port number of different instance

texts:
I saw a strange case recently and still clueless on what should be the reasoning behind it. On one of my development server, two SQL server instances are running - one is default and other one is UAT instance.
Recently we made the change to the default port of both of these instances and same was communicated to development team and other stakeholders to start their change and test the same. In order to not impact their existing application, port 1433 was kept as it is on default instance. So, below are details of server and port to connect:
Connection for Default --> Server_Name,35683
Connection for Named(SIT) instance --> Server_Name\SIT,35685

Both the instances have similar databases however different set of data as they point to different environment.
One of senior developer was using the connection as Server_name\SIT,35683 meaning mismatch of instance and port. He was using SIT instance however port of default instance and it was connecting him to default instance.
This was caught when I was at his desk and we were testing some code and I noticed this because my change was not reflecting at default instance as I had made changes at SIT instance.
Can someone please explain this behavior and how to sort this out?


texts:
I have installed mongodb using sudo pacman  -S mongodb  in my 32-bit arch .
At first I run this sudo mkdir -p /data/db to create /data/db   ,and then  run $ mongod  ,then I got the following message :

mongod --help for help and startup options
Sat Oct  6 11:45:29
Sat Oct  6 11:4
5:29 warning: 32-bit servers don't have journaling enabled by default. Please use --           journal if you want durability.
Sat Oct  6 11:45:29
Sat Oct  6 11:45:29 [initandlisten] MongoDB starting : pid=1146 port=27017    dbpath=/data/db/ 32-bit host=ftp
Sat Oct  6 11:45:29 [initandlisten]
Sat Oct  6 11:45:29 [initandlisten] ** NOTE: when using MongoDB 32 bit, you are limited to about 2 gigabytes of data
Sat Oct  6 11:45:29 [initandlisten] **       see    http://blog.mongodb.org/post/137788967/32-bit-limitations
Sat Oct  6 11:45:29 [initandlisten] **       with --journal, the limit is lower
Sat Oct  6 11:45:29 [initandlisten]
Sat Oct  6 11:45:29 [initandlisten] db version v2.2.0, pdfile version 4.5
Sat Oct  6 11:45:29 [initandlisten] git version: nogitversion
Sat Oct  6 11:45:29 [initandlisten] build info: Linux brynhild 3.5.3-1-ARCH #1 SMP   PREEMPT Sun Aug 26 09:14:51 CEST 2012 i686 BOOST_LIB_VERSION=1_49
Sat Oct  6 11:45:29 [initandlisten] options: {}
Sat Oct  6 11:45:29 [initandlisten] exception in initAndListen: 10309 Unable to   create/open lock file: /data/db/mongod.lock errno:13 Permission denied Is a mongod instance  already running?, terminating
Sat Oct  6 11:45:29 dbexit:
Sat Oct  6 11:45:29 [initandlisten] shutdown: going to close listening sockets...
Sat Oct  6 11:45:29 [initandlisten] shutdown: going to flush diaglog...
Sat Oct  6 11:45:29 [initandlisten] shutdown: going to close sockets...
Sat Oct  6 11:45:29 [initandlisten] shutdown: waiting for fs preallocator...
Sat Oct  6 11:45:29 [initandlisten] shutdown: closing all files...
Sat Oct  6 11:45:29 [initandlisten] closeAllFiles() finished
Sat Oct  6 11:45:29 [initandlisten] shutdown: removing fs lock...
Sat Oct  6 11:45:29 [initandlisten] couldn't remove fs lock errno:9 Bad file descriptor
Sat Oct  6 11:45:29 dbexit: really exiting now

what does exception in initAndListen: 10309 Unable to create/open lock file: /data/db/mongod.lock errno:13 Permission denied Is a mongod instance already running?, terminating mean?


texts: Why does performing a delete on my partitioned view result in a Clustered Index Insert?

texts:
I have a partitioned view that has the below insert trigger (poor mans partition).
When I perform a DELETE, I get the below query plan:

delete from factproductprice where pricedate = '20170725'

Trigger on the view:

ALTER TRIGGER [dbo].[factProductPriceDelete] ON [dbo].[FactProductPrice]
INSTEAD OF DELETE AS
BEGIN
  IF @@ROWCOUNT = 0 RETURN;

  DECLARE @PriceDate DATE
  SELECT @PriceDate = CAST(PriceDate AS DATE) FROM DELETED
  IF @PriceDate BETWEEN '20140101' AND '20141231'
  BEGIN
    DELETE FROM dbo.FactProductPrice2014
    WHERE ProductId IN (SELECT ProductId FROM DELETED) AND SalesPriceSchemeId IN (SELECT SalesPriceSchemeId FROM DELETED) AND PriceDate IN (SELECT PriceDate FROM DELETED)
  END
  IF @PriceDate BETWEEN '20150101' AND '20151231'
  BEGIN
    DELETE FROM dbo.FactProductPrice2015
    WHERE ProductId IN (SELECT ProductId FROM DELETED) AND SalesPriceSchemeId IN (SELECT SalesPriceSchemeId FROM DELETED) AND PriceDate IN (SELECT PriceDate FROM DELETED)
  END
  IF @PriceDate BETWEEN '20160101' AND '20161231'

.... etc


texts: How large are blocks of data in SQL Server?

texts:
I'm working in SQL Server 2008 R2 and have created a query that gathers and sums the total of data files and log files' sizes. However, I can't find how much actual disk space a single block of SQL data takes up on the disk so I can convert it into something more meaningful.

Here is the script:

DECLARE @DataSize INT
DECLARE @LogSize INT
SELECT @DataSize = SUM(size) from sys.database_files where type_desc = 'Rows'
SELECT @LogSize = SUM(size) from sys.database_files where type_desc = 'Log'
PRINT @DataSize
PRINT @LogSize

How large is one block of space? Would it be easy to convert those two integer variables into something more meaningful for a sysadmin?


texts:
I have an SQL database - I need to back it up everyday (at a minimum) - preferably multiple times a day - automatically so it definitely gets done.

I'm running Windows which the SQL database is stored on

What programs (prefer free ones) can I use to back it up - I'm completely new with SQL so if you're gonna explain something - do it in layman's terms thanks (:

It needs to get backed up to another server (on the lan).

Cheers for any help you can give!


texts: What will GRANT  ON ALL TABLES IN SCHEMA  TO  do?

texts:
It seems obvious that PostgreSQL will GRANT some privileges ON ALL TABLES IN some schema TO some role when I'll command.

But, will it make it permanent for the schema that on all tables within it, they will be granted the indicated privileges to the indicated roles, so new tables will be affected in the future?.

Or it works as a batch command so after a new table is created in the schema I have to GRANT the indicated privileges to the indicated roles again?.


texts:
I am wanting to pull data for a date range in 2017 then pull the same date range but for 2016.  I thought I would get crafty and use DateAdd() feature, but I am missing something here as I get an error of

  Msg 241, Level 16, State 1, Line 14
  Conversion failed when converting date and/or time from character string.

What I want to do is get a COUNT(saledate) that fall between the time period which will give me the number of sales.  This is sample DDL

Declare @startdate varchar(100), @enddate varchar(100), @prevyrstartdate varchar(100), @prevyrenddate varchar(100)

SET @startdate = '01/01/2017'
SET @enddate = '01/31/2017'
SET @prevyrstartdate = CAST(DateAdd(yy, -1, @startdate) As Date)
Set @prevyrenddate = CAST(DateAdd(yy, -1, @enddate) As Date)

Declare @HoldingPattern Table
(
    salesman varchar(100)
    ,saledate date
)

Insert Into @HoldingPattern (salesman, saledate) VALUES
('BOB', '2017-01-23')
,('BOB', '2017-01-30')
,('BOB', '2017-01-24')
,('BOB', '2017-01-30')
,('BOB', '2017-01-30')
,('BOB', '2017-01-30')
,('BOB', '2017-02-30')
,('BOB', '2017-02-27')
,('BOB', '2017-03-31')
,('BOB', '2017-03-27')
,('BOB', '2016-01-23')
,('BOB', '2016-01-30')
,('BOB', '2016-01-24')
,('BOB', '2016-01-30')
,('BOB', '2016-03-30')
,('BOB', '2016-03-30')
,('BOB', '2016-03-30')
,('BOB', '2016-02-27')
,('BOB', '2016-02-31')
,('BOB', '2016-02-27')

Select
salesman
,[2017 Data] = (Select COUNT(CAST(saledate As nvarchar(100))) FROM @HoldingPattern where saledate between @startdate AND @enddate)
,[2016 Data] = (Select COUNT(CAST(saledate As nvarchar(100))) FROM @HoldingPattern where saledate between @prevyrstartdate and @prevyrstartdate)
FROM @HoldingPattern


texts: Generate invoice number from 0 to so on for ordered products in orders table in php mysqli

texts:
I am trying to generate invoice number in orders table, like if user select 1 or multiple products and add it to cart then place order then invoice number should be same in our orders table for that 1 order in multiple rows according to products.
I was using function mt_rand(); but it generates randomly. I want to generate numbers from 0 to so on.
Like, 1, 2, 3, 4, 5, 6, 7 and so on.
I've also tried this to generate invoice number.
$i++

$squery = "SELECT * FROM cart WHERE user_id = '$user_id'";
$sresult = $this->db->select($squery);
if($sresult){
    $totalPrice = 0;
    $i = 0;
    while($svalue = $sresult->fetch_assoc()){
        $i++;
        $pro_id = $svalue['pro_id'];
        $pro_name = $svalue['pro_name'];
        $quantity = $svalue['quantity'];
        $pro_price = $svalue['pro_price'] * $quantity;
        $pro_image = $svalue['pro_image'];
        $query = "INSERT INTO orders(user_id, pro_id, invoice_no, pro_name, status, date) "
                . "VALUES('$user_id', '$pro_id', '$i', '$pro_name', 'processing', NOW())";
        $result = $this->db->insert($query);
        if($result){
            header("location: orders-list.php");
        } else {
            $msg = "
Something's went wrong. Please try again.
";
            return $msg;
        }
    }
    return true;
}

But it doesn't inserts same number in multiple ordered products rows of 1 order.
Please help me how can i generate invoice number from 0 to so on.


texts:
My question is in regards to restoring a DEV database from PROD.

When I take a backup of my Production Database, the LogFile is about 10GB. However, when it actually restores to DEV, it blows up to 60GB (with 50GB of free space) -- Causing major alerts due to a rapid growth on that disk.

Is there a reason something like this would happen?

Both PROD and DEV are Simple Recovery.

Thanks!


texts: SSIS: To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher

texts:
Me and my colleague using the same version of .Net framework (4.5) and installed SSDT in visual studio 2013. He prepared a package (not so complex) and can able to execute the same from SSDT without any issue. He imported the package to the integration services under MSDB for testing. If he 'Run package' from integration services he gets an error stating that "To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher".
If I tried to execute by clicking 'Run package' from integration services (MSDB) which he imported; I can able to execute it without any issues.

We have verified all the installation and everything looks similar. Can you guys help me to resolve this issue?


texts: MariaDB event to automatically move data older than a specific time to another table without temporary table?

texts:
I am trying to setup an event, that automatically moves data older than a specified amount of time from a heavily used table to some "archive" table to improve performance.
So far I've only come across answers, that are based on a WHERE clause that is used for both INSERT INTO SELECT and the DELETE statement. Since time passes between both statements, the WHERE clause could theoretically have different results and thus lead to data loss.
This is my event:
DELIMITER $$
CREATE EVENT IF NOT EXISTS archive_6_month_old_entries
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
DO
begin
START TRANSACTION;

CREATE TEMPORARY TABLE data_to_move
SELECT *
FROM api_log
WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0 LIMIT 5000;

INSERT INTO api_log_archive
SELECT * FROM data_to_move;

DELETE
FROM api_log
WHERE id in (SELECT id FROM data_to_move);

COMMIT;
end $$
DELIMITER ;

Is there any way to do this without a temporary table?


texts: Is there risk importing data with auto increment id #'s that are higher than the current numbers?

texts:
I have to import data into a new database.  That new database already has a small set of data.  I have two large tables I need to import in there.

Is there any risk of a higher id getting over written or causing problems later as new data comes in?

This is the current setup of the database tables.

-- phpMyAdmin SQL Dump
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

Table websites

    CREATE TABLE IF NOT EXISTS `url` (
      `id` int(20) NOT NULL AUTO_INCREMENT,
      `userid` int(16) NOT NULL DEFAULT '0',
      `alias` varchar(10) NOT NULL,
      `special` varchar(160) NOT NULL,
      `website` text NOT NULL,
      `city` text NOT NULL,
      `story` text NOT NULL,
      `date` datetime NOT NULL,
      `pass` varchar(255) NOT NULL,
      `click` bigint(20) NOT NULL DEFAULT '0',
      `meta_title` varchar(255) NOT NULL,
      `meta_description` varchar(255) NOT NULL,
      `ads` int(1) NOT NULL DEFAULT '1',
      `bundle` mediumint(9) NOT NULL,
      `public` int(1) NOT NULL DEFAULT '0',
      `archived` int(1) NOT NULL DEFAULT '0',
      `type` varchar(64) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=127878 ;

Table statistics

    CREATE TABLE IF NOT EXISTS `scores` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `shorr` varchar(255) NOT NULL,
      `curlid` bigint(20) NOT NULL,
      `curluserid` bigint(20) NOT NULL DEFAULT '0',
      `date` datetime NOT NULL,
      `ip` varchar(255) NOT NULL,
      `country` varchar(255) NOT NULL,
      `website` varchar(50) NOT NULL,
      `referal` text NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1807671 ;


texts: Interpreting Decision Time (DT) in temporal databases

texts:
Could anyone give me an insight for the need to model decision time in temporal databases and specifically how and why decision time differs from valid time and transaction time? What would be the semantics of decision time?

For instance, what information would/could the dt_period convey in the  following example which illustrates an employee salary data where range types (PostgreSQL) are used, eg. vt_period instead of two distinct columns such as valid_from and valid_to:

  name   | salary |        vt_period        |       tt_period       | dt_period
---------+--------+-------------------------+-----------------------+-----------
 Okihara |   1000 | [2014-01-01,2014-12-31) | [2014-03-15,infinity) | (!?!??)
 Okihara |   1500 | [2014-12-31,infinity)   | [2014-11-05,infinity) | (what should this column mean?)

where

  vt_period = Valid time period
  tt_period = Transaction time period
  dt_period = Decision time period

from which it may be inferred that Okihara was hired for a salary of $1,000 over the period [2014-01-01, 2014-12-31) as last updated on 2014-03-15 and still current. On 2014-11-05 a new entry with a raise to $1,500 was made for the subsequent validity period [2014-12-31,infinity) (as stated by thevt_period column).

What range values could dt_period hold and what would their semantics be?


texts: MySQL Tables Require Daily Repairs - Server, Table or HD?

texts:
I've been experiencing a weird issue with one of MySQL DBs. Every day, sometimes 2-3 times per day, I'll need to repair the tables. The MySQL DB has 25 tables with 5.6m rows in total.

The bigger ones are:

Table A - 599k rows / 867MB
Table B - 2.1m rows / 146MB
Table C - 2.2m rows / 520MB

It seems table C needs to be repaired pretty frequently, Tables A & B not as much.

When the table needs to be repaired, I'm not seeing it being marked as crashed or in use. But through other tools, I can see the data is not what it should be.

When I do repair the table, I'll see a message similar to:

[table c] repair info Wrong bytesec:  54-55-102 at 368251940; Skipped
[table c] repair warning Number of rows changed from 2127934 to 2127931

or

[table c] repair info Wrong bytesec:  171-30-101 at 341237312; Skipped
[table c] repair warning Number of rows changed from 1984585 to 1984582

I've tried making adjustments in my.cnf but no difference.

The server is a cloud server running both MySQL and Apache. Plenty of space available on all HDs:

Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda2             99G   14G   80G  15% /
tmpfs                 1.9G     0  1.9G   0% /dev/shm
/dev/xvda1             97M   49M   44M  53% /boot
/dev/xvdc1            296G   25G  257G   9% /data

I'm not sure if this is a problem with the cloud HD, the server or the tables themselves. The problem didn't start happening until about 2 months ago and the size of the DB has only changed by 300-400MB until now.

Any idea what I should be looking at to verify where the problem might be?

Using MySQL v5.1.66 and MyISAM

Thanks in advance.

Best, Cent


texts:
I have a product review table in an oracle 12.1 EE database that has different attributes rated on a scale of 1 through 5. How do I query this table to count up all of the reviews and group by the product and the 1-5 rating.

Example Data

WITH DATA_SET
AS
    (   SELECT  1   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                5   AS OVERALL,
                4   AS COMFORT,
                5   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  2   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                4   AS OVERALL,
                4   AS COMFORT,
                4   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  3   AS REVIEW_ID,
                'b' AS PRODUCT_ID,
                4   AS OVERALL,
                5   AS COMFORT,
                4   AS FIT,
                5   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  4   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                3   AS OVERALL,
                2   AS COMFORT,
                2   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  5   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                2   AS OVERALL,
                1   AS COMFORT,
                2   AS FIT,
                1   AS APPEARANCE
        FROM    DUAL
    )
SELECT  *
FROM    DATA_SET;

review_id | product_id | overall | comfort | fit   | appearance
----------------------------------------------------------------
1            a            5         4         5       4
2            a            4         4         4       4
3            b            4         5         4       5
4            c            3         2         2       4
5            c            2         1         2       1

I've looked at doing pivots and various analytic queries. But I can't quite get the desired output below. I'm sure I'm making this too hard. But for the life of me I can't put this together. Any help/direction would be appreciated!

Example Output

product_id | rating | overall | comfort | fit   | appearance
--------------------------------------------------------------
a            1         0        0         0         0
a            2         0        0         0         0
a            3         0        0         0         0
a            4         1        2         1         2
a            5         1        0         1         0
b            1         0        0         0         0
b            2         0        0         0         0
b            3         0        0         0         0
b            4         1        0         1         1
b            5         0        1         0         0
c            1         0        1         0         1
c            2         1        1         2         0
c            3         1        0         0         0
c            4         0        0         0         1
c            5         0        0         0         0


texts: Select Ingredients of a product based on unit type

texts:
I have three tables UnitType,NutritionMaster, NutritionInProduct

UnitTypeID  UnitName
1           100ml contains
2           250ml contains

NutritionID Name    Description
1           Energy  NULL
2           Protein NULL

ID                    NutritionID  ProductID     UnitTypeID Unit
54087                       1       20785           1       2ml(0.5 kcal)
54088                       1       20785           2       4ml
54089                       2       20785           1       10ml
54090                       2       20785           2       50ml

And I want to show the results as

I tried doing it like this

Select ROW_NUMBER() OVER(PARTITION BY unittypeid ORDER BY NutritionInProductid aSC) AS "Row Number",
NutritionInProductid,unittypeid,ProductID,unit from NutritionInProduct
group by  unittypeid,NutritionInProductid,ProductID,unittypeid,unit
having ProductID =20785

but its not what I want ..can you help me.


texts: Select rows for which at least one row per set meets a condition

texts:
I have following table:

create table test (
  company_id integer not null,
  client_id integer not null,
  client_status text,
  unique (company_id, client_id)
);

insert into test values
  (1, 1, 'y'),    -- company1

  (2, 2, null),   -- company2

  (3, 3, 'n'),    -- company3

  (4, 4, 'y'),    -- company4
  (4, 5, 'n'),

  (5, 6, null),   -- company5
  (5, 7, 'n')
;

Basically, there are 5 different companies, each one has one or more clients and each client has status: 'y' or 'n' (might be null as well).

What I have to do is to select all pairs (company_id, client_id) for all companies for which there is at least one client whose status is not 'n' ('y' or null). So for the example data above, the output should be:

company_id;client_id
1;1
2;2
4;4
4;5
5;6
5;7

I tried something with window functions but I can't figure out how to compare the number of ALL clients with the number of clients with STATUS = 'n'.

select company_id,
count(*) over (partition by company_id) as all_clients_count
from test
-- where all_clients_count != ... ?

I figured out how to do this, but I am not sure if it's the right way:

select sub.company_id, unnest(sub.client_ids)
from (
  select company_id, array_agg(client_id) as client_ids
  from test
  group by company_id
  having count(*) != count( (case when client_status = 'n' then 1 else null end) )
) sub


texts: TDE state is changed after DB deployment

texts:
I have database projects in Visual Studio 2013 from which I deploying databases to SQL Server. Also I am using Transparent Data Encryption for these databases and it is in state 3 which means TDE is ON.

Issue becoming when I deploy databases. After database deployment TDE state is 1 which means TDE is OFF. I only know that Database Encryption Key exist in database page level and I supposed that something happened there when DB schema is changed.

My question is how I can control that process? How I can save TDE state to be 3 after deployment?

I can run SQL query which will turn on TDE but is there any other way?


texts: Reconnect "not connected" query windows in SSMS to original connection

texts:
Is there a simple way to reconnect a query window that is "not connected" to the original connection that was used by that query without manually picking the server name needed? If I click the Connect button on the SQL Editor toolbar or right click -> Connection -> Connect... it defaults the server name to the last connection I used in that SSMS session, not to the last connection used by that specific query window.

After taking my laptop home at the end of the day, all the open windows will be set to not connected the next time I use it. Ideally, I'd like to be able to reconnect all to their original connections, but I don't know that even the one query at a time is possible.


texts:
I have always taught that it is incorrect to store formatted data in a table. Examples include:

+----------------+----+
| Data           | OK |
+----------------+----+
| 0370101234     |  ✓ |
+----------------+----+
| 03 7010 1234   |    |
+----------------+----+
| (03) 7010 1234 |    |
+----------------+----+

(Why doesn’t SO support tables?!)

The data is an Australian phone number which is generally 10 digits long, including the area code.

Including the format spacing or characters introduces ambiguity, so it is harder to sort or search.

The question is: is there anything in the standards or in relational theory which covers this issue?

EDIT

This isn’t a question about telephone numbers as such. I was illustrating a point with data that should be stored in its simplest form, even if it is to be displayed with formatting characters.

Other examples include the $ sign which is clearly never stored with a currency value, and the thousands separator which has its own issues.


texts: How can I change the connection descriptor used by Oracle Enterprise Manager?

texts:
I've recently changed the SERVICE_NAME of my 11g database and Enterprise Manager can no longer connect to it:

  2014-01-08 10:36:06,047 [HTTPThreadGroup-3] ERROR eml.OMSHandshake
  processFailure.806 - OMSHandshake failed.(AGENT URL =
  https://Test9.lab.nyc-14.knoa.com:3938/emd/main)(ERROR =
  INTERNAL_ERROR)(CAUSE =java.sql.SQLException: Listener refused the
  connection with the following error: ORA-12514, TNS:listener does not
  currently know of service requested in connect descriptor The
  Connection descriptor used by the client was:
  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Test9.lab.nyc-14.knoa.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test9.lab.nyc-14.knoa.com))))

The SERVICE_NAME in there is indeed the old one. I've updated NETWORK\ADMIN\tnsnames.ora but that didn't fix it. Where does Enterprise Manager read the connection descriptor from?


texts:
I'm trying to set up a test environment for Service Broker communication between 2 SQL Server 2016 SP1 instances. I've created db master keys, certificates and endpoints in the master db for each instance, as in the code listed below. For now my goal is to synchronize data between 2 tables. On the initiator side, I'm inserting a row in a table. A "after insert" trigger sends data on conversation to the target instance. Unfortunately the message remains in the sys.transmission_queue on initiator. The sys.transmission_queue.transmission_status column displays this message: "Connection handshake failed. An OS call failed: (0) (null). State 53." In the SQL Server error log and in the Windows Application event viewer there are a few messages:

Error: 9650, Severity: 16, State: 3. A system cryptographic call failed during a Service Broker or Database Mirroring operation: system error '5(Access is denied.)'. Error: 9641, Severity: 16, State: 1. A cryptographic operation failed. This error indicates a serious problem with SQL Server. Check the SQL Server error log and the Windows event logs for further information. Service Broker login attempt failed with error: 'Connection handshake failed. An OS call failed: (0) (null). State 53.'. [SERVER: target ip number]

I've followed the advice from this stackoverflow post, but the problem persists. Cryptographic Error Using SQL Server Service Broker I've audited access to C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys, C:\ProgramData\Microsoft\Crypto\ and HKEY_LOCAL_MACHINE registry keys on the target side, but I couldn't "catch" any "access denied" event. Can anyone of you help?

Initiator
Windows 10 Enterprise version 1703 OS Build 15063.7026 (my dev physical machine)
SQL 2016 SP1 version 13.0.4206.0
TCP/IP protocol is enabled.

Target
Windows Server 2012 R2 Standard (virtual machine)
SQL 2016 SP1 version 13.0.4001.0
Ports 1433 and 4022 are open.
TCP/IP protocol is enabled.

--initiator master db
USE master;
GO
CREATE LOGIN Login_SB_Source WITH PASSWORD = 'my_strong_pwd'
GO
CREATE USER User_SB_Source FOR LOGIN Login_SB_Source;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'my_strong_pwd'
GO
CREATE CERTIFICATE Certificate_SB_Source
WITH
        SUBJECT = 'SB source',
    START_DATE = '11/01/2017',
    EXPIRY_DATE = '01/01/2100'
    ACTIVE FOR BEGIN_DIALOG = ON
GO
CREATE CERTIFICATE Certificate_SB_Target
    AUTHORIZATION [User_SB_Source]
    FROM FILE = 'C:\Certs\Certificate_SB_Target.cer'
GO
BACKUP CERTIFICATE Certificate_SB_Source
    TO FILE = 'C:\Certs\Certificate_SB_Source.cer'
GO
CREATE ENDPOINT SB_EndPoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 4022)
    FOR SERVICE_BROKER
    (
        AUTHENTICATION = CERTIFICATE Certificate_SB_Source,
        ENCRYPTION = SUPPORTED
    )
GO
GRANT CONNECT ON ENDPOINT::SB_EndPoint TO Login_SB_Source

--target master db
USE master;
GO
CREATE LOGIN Login_SB_Target WITH PASSWORD = 'my_strong_pwd';
GO
CREATE USER User_SB_Target FOR LOGIN Login_SB_Target;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'my_strong_pwd'
GO
CREATE CERTIFICATE Certificate_SB_Target
WITH
    SUBJECT = 'SB target',
    START_DATE = '11/01/2017',
        EXPIRY_DATE = '01/01/2100'
    ACTIVE FOR BEGIN_DIALOG = ON
GO
CREATE CERTIFICATE Certificate_SB_Source
    AUTHORIZATION User_SB_Target
    FROM FILE = 'C:\Certs\Certificate_SB_Source.cer'
GO
BACKUP CERTIFICATE Certificate_SB_Target
    TO FILE = 'C:\Certs\Certificate_SB_Target.cer'
GO
CREATE ENDPOINT SB_EndPoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 4022)
    FOR SERVICE_BROKER
    (
        AUTHENTICATION = CERTIFICATE Certificate_SB_Target,
        ENCRYPTION = SUPPORTED
    )
GO
GRANT CONNECT ON ENDPOINT::SB_EndPoint TO Login_SB_Target


texts: Designing a Database Structure for Permissions: More Columns Vs More Rows

texts:
I have an application that needs to keep track of a collection of bit values that is driving a user's permissions within the application.

Option 1

As for the respective database structure, the first option is to retain all of those values in a single row for the user with a column for every permission. Example Schema:

CREATE TABLE t_user_perm
(
    UserID UNIQUEIDENTIFIER NOT NULL,
    Perm1 BIT NOT NULL,
    Perm2 BIT NOT NULL
);

Option 2

The second option is to have a row per permission for the user and only creating that row when the user has gained that permission. Example Schema:

CREATE TABLE t_user_perm
(
    UserID UNIQUEIDENTIFIER NOT NULL,
    PermName VARCHAR(32) NOT NULL,
    PermValue BIT NOT NULL
);

Considerations

The first option seems the least flexible in terms of growth as it would constantly require a schema, index and procedure updates to add more permissions.  I also feel that it's performance would be costly when it comes to doing updates on one permission at a time, as it's proposed, as it would be updating the entire row even though only one value is changing.  The only benefit that I see is when it comes to reading the data in the application it's one row of data vs having to look through an array of permissions, however I see that as being something the application should take on as it offloads work from the database when it comes to data modification in the table.

The second option seems the most flexible in terms of growth as the columns are fixed and additional permissions can be added at anytime in the application without having to modify the database.  I also see it more efficient in it's indexing as it's indexing based on two columns at most, UserID and PermName, vs having to index on each column individually in order to make reporting on the number of users with each permission.  Additionally it would make reporting less complex as it can just do a rollup on all of the PermNames vs having to add it as a new item to roll up for each permission.

The second option seems to be the best solution for tens of thousands of possible users.  Would there be any reason why option one would be worth investigating further?


texts: MySQL some views are not created when restoring from dump

texts:
I faced strange issue when restoring MySQL database from dump. Dump contains only tables structure without any data. There are tables and views.

If I run following from mysql console everything works fine

mysql> source path/to/dump.sql

But if I execute following:

mysql --user=root --password=PASSWORD dbName < /path/to/file.sql

Then several views are not being created. The same is true when I execute SQL file via php migration.

I need to run it automatically, so how can I get it working without mysql prompt?

[Update 1]

Dump was done with mysqldump from local database and now I'm trying to restore it from on the same mysql instance with the same root user.

All views look the same, but for some reasons they all are created when I import sql file from mysql prompt via source command. Otherwise only some views are created.

This is how each view looks:

/*!50001 DROP VIEW IF EXISTS `my_view`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50001 VIEW `my_view` AS select `a`.`order_id` AS
    `order_id`,`a`.`client_id` AS `client_id`,`b`.`device_platform` AS
    `device_platform` .... from (`tbl_1` `a` left join `tbl_2` `b`
    on((`a`.`client_id` = `b`.`client_id`))) where (`b`.`status` =
   'active') */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;


texts: How to grant access to a specific table across all databases in Mysql?

texts:
I want to Grant access like so:
> GRANT INSERT, UPDATE ON `%`.my_table TO 'user'@'%';
ERROR 1146 (42S02): Table '%.my_table' doesn't exist

This is not possible per Mysql documentation on grant:

The _ and % wildcards are permitted when specifying database names in
GRANT statements that grant privileges at the database level

And nothing is mentioned for table level access.
I found similar questions on stackexchange, but they have different use cases. There is Mysql Bug from 2017 that seems to suggest a fix for this specific use case:

Accept statements which have a wildcard in the database name and a
fixed table name.

Is there a workaround?


texts: How to retrieve the Capture Instance for a given table?

texts:
Working with SQL Server 2012, I have a hierarchical table layout (TPT). All tables "inherit" from the same base table (dbo.DataObjects). All tables have history tracking via CDC enabled.

I am trying to write a generic function that returns the CDC history information for a give type including the relevant information from all base tables.

Supposing my type is User with the table dbo.Users which inherits directly from dbo.DataObjects, my query should look something like this:

DECLARE @Begin_LSN binary(10),@End_LSN binary(10)
SELECT @Begin_LSN = sys.fn_cdc_get_min_lsn('dbo_Users')
SELECT @End_LSN = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_DataObjects(@Begin_LSN, @End_LSN,'ALL') AS a
JOIN cdc.fn_cdc_get_all_changes_dbo_Users(@Begin_LSN, @End_LSN,'ALL') AS b
ON a.__$start_lsn = b.__$start_lsn
WHERE ID = 10;

This will give me the complete history trail for the User with ID 10 including the columns from the base table.

Now I want to build this kind of query dynamically.

One of my problems is that the cdc functions to retrieve the history information include the name of the capture instance:

cdc.fn_cdc_get_all_changes_<capture_instance>

I also need the name of the capture instance in the second line to get the min lsn:

sys.fn_cdc_get_min_lsn('<capture_instance>')

The capture instance name either gets created automatically for me when I enable CDC for a table or is passed manually. If I don't want to keep track of the capture instance name myself, is there a way to retrieve the name of the capture instance for a given table?

I am aware that there can be more than one capture instance per table (max. 2 I think), but I likely always will only use one per table.

PS: I am also aware that the capture instance name is usually built in the way <schema>_<table>, but relying on this assumption seems a bit risky. The naming convention could change for future versions, or someone could supply a manual capture instance name / change the name.


texts: How to print more than 8,000 characters at a time to the SSMS Message window, without compromising text formatting?

texts:
Whenever I write dynamic SQL, I typically include a PRINT @DynamicSQL statement in a comment right above the EXEC sp_ExecuteSQL @DynamicSQL statement so that the dynamic SQL can be easily read and debugged when needed.

I realized the PRINT statement has a limit of 8,000 characters before it truncates the string. I know I can loop over my @DynamicSQL variable the number of times 8,000 divides into it's length and print each 8,000 chunk per iteration, but then you lose the formatting where a statement in @DynamicSQL is across two chunks, which kind of defeats my purpose.

Has anyone found a better way to preserve formatting while printing a string more than 8,000 characters?...perhaps through a custom function or procedure?


texts: Most useful stored procedures for a new DBA?

texts:
I am a new DBA working with several more experienced DBA's.  When I am scratching my head one of them says "oh just run SP_xx_xx", and sure enough that begins the path to solution.

Of course they all just know them, I have decided to start a list.  I have not been writting them all down, so all I have in my list is these three.

sp_Cycle_Errorlog
sp_whoisactive
sp_change_users_login 'report'

I Googled and came up with some very old lists

Useful system stored procedures in SQL Server(SO)
Two Dozen of My Favorite System Stored Procedures, By Kiely, Don

What are the best or most helpful Stored Procedures for a New DBA to have in their tool kit, for SQL 2012/2014?


texts: Get data from both tables what join should I use

texts:
I have T1 as table 1

----------------
field1 | field2
---------------
 val1  | val2

I have T2 as table 2

----------------
field1 | field2
---------------
 val11  | val22

I want to write a query which can produce result like this

 field1 | field2 | T1.T2 |
 ------------------------|
 val11  | val22  | T2    |
 val1   | val2   | T1    |


texts: How to update value in inserted row in PostgreSQL?

texts:
I have a table t1

create table t1 (
  id int primary key not null,
  name varchar(64),
  str2 varchar(64)
);

I want to get the row ID and update a field str2 in this row.

I tried this:

WITH inserted AS (
  insert into t1(id, name) values (38, 'www') returning *
) UPDATE t1 SET str2='aaa' WHERE id IN (SELECT id FROM inserted);
select * from t1;

I expected:

| id | name |   str2 |
|----|------|--------|
| 38 |  www |   aaa  |

But the actual result was:

| id | name |   str2 |
|----|------|--------|
| 38 |  www | (null) |

See this SQLFiddle for a reproduction scenario.

What am I doing wrong?


texts: Oracle Autodop disabled at the table level

texts:
I have a question related to Oracle auto DOP and parallel DML concepts. I have a scenario, if  I set the Auto DOP set to 1 on the table and if I run parallel DML statements on that table through parallel hint, will the query processing happens in parallel execution or since the Auto dop is set to 1 will it be a serial execution?

Can you please provide some insight on this topic.

Thanks for the help!


texts: PostgreSQL streaming replication, incompatible locales between different systems?

texts:
I have a PostgreSQL 11 server on a windows 10 machine. That machine is used in a harsh environment, and a win32 client program is used with it. That program requires cp1250 charsets. The windows is a Hungarian version, and the PostgreSQL server was installed with default locale.

I want to create a streaming replication on a remote server (connected via VPN). The server is Ubuntu 18.04 LTS, running PostgreSQL 11 replication slave in a docker container.

I have created this Dockerfile to create the slave:

FROM postgres:11
RUN localedef -i hu_HU -c -f UTF-8 -A /usr/share/locale/locale.alias hu_HU.UTF-8
ENV LANG hu_HU.utf8

When I start up the slave I get these messages:

PostgreSQL Database directory appears to contain a database; Skipping initialization

2020-04-18 10:14:46.731 GMT [1] LOG:  invalid value for parameter "dynamic_shared_memory_type": "windows"
2020-04-18 10:14:46.731 GMT [1] HINT:  Available values: posix, sysv, mmap, none.
2020-04-18 12:14:46.731 CEST [1] LOG:  invalid value for parameter "lc_messages": "Hungarian_Hungary.1250"
2020-04-18 12:14:46.731 CEST [1] LOG:  invalid value for parameter "lc_monetary": "Hungarian_Hungary.1250"
2020-04-18 12:14:46.731 CEST [1] LOG:  invalid value for parameter "lc_numeric": "Hungarian_Hungary.1250"
2020-04-18 12:14:46.731 CEST [1] LOG:  invalid value for parameter "lc_time": "Hungarian_Hungary.1250"
2020-04-18 12:14:46.731 CEST [1] FATAL:  configuration file "/var/lib/postgresql/data/postgresql.conf" contains errors

It seems to me that the pg_basebackup has created a backup that is incompatible with Linux. I'm not sure how to fix this? The localedef command on Linux cannot be used to generate hu_HU.1250 locale (or anything similar). Or at least I don't know how to do it.

Here are some facts:

the replication master must have default cp1250 charset, because the win32 program uses this charset (and I cannot change this, it is an old program and I cannot rewrite it)
the replication slave must be on Linux, because I don't have a windows server at hand

Is it possible to change the configuration somehow, and do streaming replication?

UPDATE - I have tried to change the parameters in postgresql.conf on the slave to this:

lc_messages = 'hu_HU.UTF-8'
lc_monetary = 'hu_HU.UTF-8'
lc_numeric = 'hu_HU.UTF-8'
lc_time = 'hu_HU.UTF-8'
dynamic_shared_memory_type = posix

In this case, the server starts up, but I cannot connect to it with psql:

psql: FATAL:  database locale is incompatible with operating system
DETAIL:  The database was initialized with LC_COLLATE "Hungarian_Hungary.1250",  which is not recognized by setlocale().
HINT:  Recreate the database with another locale or install the missing locale.

Maybe it is already working as streaming replication slave, but I'm not sure.