texts: I am at a client where they don't allow Adam Machanic's sp_WhoIsActive stored procedure to be installed anywhere on the SQL server. Does anyone have any suggestions for running it as a query instead?


texts: Same table for real persons and fictional one

texts:
In my application stores both real and fictional persons (from movies, etc.) as well as stage names and alter egos.

These entities have almost the same properties and are often referenced via foreign key regardless if the are a real or not.

I'm still concerned if it is a good idea to store this information in one table even if otherwise the model would be more complex.

The table person:

Person(person_id[PK], type, firstname, middlename, lastname, common_name, full_name, address_id, ...)

type is to distinguish between real persons, etc.

common_name is the name by which the person is mostly referenced. In the case of a real person it is the firstname and lastname. full_name is the name inclusive all academic degrees (if any).

A sample for a foreign key:

movie_acting(acting_id[PK], movie_id[FK], role_id[PK], actor_id[FK])

This table describes which actor plays which role in a movie. Both role_id and actor_id are foreign keys to person_id. The fact that person can be both real and fictional allows

that a movie contains a real person and
that the actor is a fictional person (which is not very common but possible)


texts: New SQL Server 2016 install -- confused logging on via the command line

texts:
I just finished installing SQL Server 2016 (The full thing -- not just Express).   I specified Windows authentication.  During the install in the install wizard, in the Database Engine Configuration node and on the Server Configuration tab, I simply clicked the Add Current User button.  I didn't add a specific user for administration at install time.

Before posting, I also took a look at this:  https://msdn.microsoft.com/en-us/library/ms180944.aspx

Anyway, when I run this from a command prompt:

C:\Users\dogzilla>sqlcmd -S dogzilla -U dogzilla

I get this error:

Password: Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'dogzilla'..

dogzilla is the account I use to log into Windows with.  I know the password since I'm able to write this to you.

What's going on with the authentication?  Does the dogzilla password need to be set in SQL Server?  How can I change it there?

Thanks!


texts: Assign object owner to a role other than the creator automatically on creation for a database

texts:
Background:

I'll start by saying I'm fairly new to postgres, I generally work with web apps and I know my way around SQL but I'm new to both postgres and database administration. I have however read through the postgres docs for database roles.

My team at work is rather small and we're responsible for the building and deployment of a web application which uses postgres as our main database. There are about 3 of us who actively work on the database. This includes creating, altering (and occasionally dropping) tables, and doing the same to functions and types.

We've recently run into the problem of object ownership. It seems only the owner of an object can drop it from the database, be that any database object. We want reasonable freedom to be able to manipulate the database as required without messing around too much with object ownership all the time.

At the moment we haven't had the chance to automate the deployment of our database changes via migration scripts and the responsibility of running these falls to the individual leading a feature development and deployment.

Question:

We'd like a way to assign object ownership to a group role (which we would all have access to) without having to do it manually after every object creation. Is there a way to set this up globally for a schema or database, or even for a group role? If in the (likely) event that this is a really silly thing to do, does anyone have any suggestions of how we should be working in this scenario?

Things we've explored:

I have already gone through the alter default privileges but it seems this must be set for every login role (user), which feels like there's some friction meaning we might be going in the wrong direction. Past that, google and stack exchange have turned up little, though I may not be using the correct terminology.


texts: Verify mysqldump backup is corruption free

texts:
We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a backup is corruption free ?

Background: We are running mysql 5.5.* and InnoDB. We are running mysqldump straight from the server.  We are not currently using master / slave or master / master but could change if it would help. The DB is small currently <1mb, it will be <50mb for the foreseeable future, so scale dosnt really need to be taken into account in this instance.


texts: How to real-time sync local Postgres instance to Cloud Postgres

texts:
I have a local postgres database (9.2) running on Ubuntu that is constantly being written to by an application capturing sub-second data. (99.9% of the transactions are inserts).  I also have a postgres database with the same data structure sitting in the cloud.  I need a way to get some of the data (not all of the data) up the cloud in near real-time. (Oh, I might have several local postgres databases pushing to the same cloud database.) I would of course need to ensure all the data I want gets delivered if the connection went down. Ideally I would just like to specify what tables/views to push. Anyone have any ideas?  I would prefer not to have to write my own solution but I am not taking any option off the table.

I have looked at postgres streaming replication, however, from what I have read it's a full database replication not just a subset of the data.

Thanks in advance for the ideas.


texts:
I have a jsonb row with objects/dictionairies : {"a":1, "b":2, "c",3}

I want to query rows that contain a set of keys, ex:

SELECT * from my_table v
WHERE v.my_jsonb_column::jsonb ?| array['b','a']

how can I create an index for this query ?


texts: Replicating tables for reporting purpose

texts:
We have 5 tables in a database on Server A that we would like to replicate to Server B for reporting purposes. We would like to create different indexes on the destination copy so we can get better reporting performance. We considered the following options:

Always On Availability group (Feature of SQL Server 2012): Cannot use this because it only allows to create a Read Only copy of the source database i.e. we cannot create different set of indexes on each server.
Transactional Replication: This looks good for implementing what we are looking to do but since our tables are going to be updated very frequently the performance impact on server is expected to be high and we are looking to avoid any performance degradation.
Snapshot each night: This could have been OK too since we don't need to keep the two databases in sync at all times. But this will cause to recreate the tables every night which means we will have to recreate the indexes as well each night. No guarantee that it will all finish in one night.

The other customized solution that we came up with was a two step process. First we replicate the full database on Server B using Always ON method. Then we will create another database on Server B which we will replicate to using the first destination database by Transactional Replication method. I understand that this is longer method to reach the same destination but this allows us to avoid the performance impact on Server A. We are not so concerned with the performance of Server B. Does this solution sound good or are we overlooking any possible issues with this solution?


texts: SQL Server Management Studio 2012: How to have folders in the SSMS project?

texts:
I'm using SSMS 2012 to talk to the SQL server 2012 and Azure SQL servers we use. I admit I'm not a SQL expert so I've been saving most of my SQL scripts for future reference. I've quickly run into 20 or so .SQL scripts in the SSMS project and they are all living under the same 'Queries' folder.

Is there a way I can create "sub-folders" in the project so I can organize my scripts properly? How do most other folks keep their scripts organized? I'm pretty sure if this bugs a novice like me, it must be a real issue for real admin (with potentially hundreds of scripts?)


texts:
My environment:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.8 (Santiago)
# uname -a
Linux A.B.C 2.6.32-642.4.2.el6.x86_64 #1 SMP Mon Aug 15 02:06:41 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux
# rpm -q mysql-server percona-toolkit
mysql-server-5.1.73-7.el6.x86_64
percona-toolkit-2.2.19-1.noarch
#

I'm running pt-table-checksum and I'm getting following error:

  Skipping chunk X of Y.Z because MySQL chose no index instead of the
  word_sid_typeindex.

mysql> DESCRIBE search_index;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| word  | varchar(50)      | NO   | MUL |         |       |
| sid   | int(10) unsigned | NO   | MUL | 0       |       |
| type  | varchar(16)      | YES  |     | NULL    |       |
| score | float            | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS WHERE Name='search_index'\G;
*************************** 1. row ***************************
           Name: search_index
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3155432
 Avg_row_length: 51
    Data_length: 163250176
Max_data_length: 0
   Index_length: 415514624
      Data_free: 839909376
 Auto_increment: NULL
    Create_time: 2016-09-01 21:14:58
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.13 sec)

ERROR:
No query specified

mysql>

Please advice.


texts:
Sharepoint and SQL Server issue as below

When we change setting on SharePoint (Get-SPEnterpriseSearchServiceApplication).QueryLogSettings.CleanupDays = 30

we basically tell SharePoint to purge all data from Search_LinksStore that is older than 30 days.

What it actually does, it starts (after we start respective SPTimerJobs) stored procedure UAT_SP_CF_Search_LinksStore.dbo.proc_MSS_QLog_Cleanup and passes number of days as a parameter, here is manual start example:

EXEC UAT_SP_CF_Search_LinksStore.dbo.proc_MSS_QLog_Cleanup 30;
GO

When this stored procedure runs, it goes trough the tables and performs SELECT and DELETE, but due to enormous number of rows even small reduction on DB has huge impact on sql transaction logs (they generate 200+GB for only 10GB od DB reduction).

In this case, it tried to delete older than 30days but it always hangs due to low disk space on log partition (i even expanded to 400GB ...) and it cannot be done this way.

I will need help of SQL guys and any of SharePoint guys.

Is it possible to create another stored procedure (or modify this one) to have chunks of delete in smaller amount of rows, that way we can reduce impact on sql logs (but it will be performed longer, but i think we can sacrifice time for the sake of log size)?

    SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_MSS_QLog_Cleanup](
    @Days int
)
AS
    DECLARE @TooOld datetime
    SET @TooOld = DATEADD(day, -@Days, GETUTCDATE())
    BEGIN TRANSACTION
    DECLARE @OldPageImpressions table (
        pageImpressionId bigint NOT NULL);
    DECLARE @MinId bigint = (SELECT MIN(pageImpressionId) FROM MSSQLogPageImpressionQuery WHERE searchTime >= @TooOld )
    IF (@MinId IS NOT NULL)
    BEGIN
        DELETE FROM
            MSSQLogPageImpressionQuery
        WHERE
            pageImpressionId < @MinId
        DELETE FROM
            MSSQLogPageImpressionResult
        WHERE
            pageImpressionId < @MinId
        DELETE FROM
            MSSQLogPageImpressionBlock
        WHERE
            pageImpressionId < @MinId
        DELETE FROM
            MSSQLogPageImpressionQueryRule
        WHERE
            pageImpressionId < @MinId
    END
    ELSE
    BEGIN
        TRUNCATE TABLE MSSQLogPageImpressionQuery
        TRUNCATE TABLE MSSQLogPageImpressionBlock
        TRUNCATE TABLE MSSQLogPageImpressionResult
        TRUNCATE TABLE MSSQLogPageImpressionQueryRule
        TRUNCATE TABLE MSSQLogResultDocs
        TRUNCATE TABLE MSSQLogPageClick
        TRUNCATE TABLE MSSQLogO14PageClick
    END
    DELETE FROM
        MSSQLogPageImpressionQuery
    OUTPUT
        DELETED.pageImpressionId
    INTO
        @OldPageImpressions
    WHERE
        searchTime < @TooOld
    DELETE FROM
        dbo.MSSQLogPageImpressionResult
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogPageImpressionBlock
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogPageImpressionQueryRule
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogResultDocs
    WHERE
        lastReference < @TooOld
    DELETE FROM
        dbo.MSSQLogPageClick
    WHERE
        clickTime < @TooOld
    DELETE FROM
        MSSQLogO14PageClick
    WHERE
        searchTime < @TooOld
    COMMIT TRANSACTION

GO


texts: Firebird2.5 error with collation in POSITION

texts:
Why does the following query succeed in Firebird 2.1, but fail in Firebird 2.5?

select POSITION(_UNICODE_FSS 'alcu' IN _UNICODE_FSS 'Calculation') from "Election"

The error is

Error: *** IBPP::SQLException ***
Context: Statement::Prepare( select POSITION(_UNICODE_FSS 'alcu' IN _UNICODE_FSS 'Calculation') from "Election" )
Message: isc_dsql_prepare failed

SQL Message : -902
can't format message 13:98 -- message file C:\Windows\firebird.msg not found

Engine Code    : 335544721
Engine Message :
Unable to complete network request to host "*******".
Error reading data from the connection.

Using other collations like _UTF8 have the same results.

However, queries like

select replace (_UNICODE_FSS 'Billy Wilder',  _UNICODE_FSS 'il', _UNICODE_FSS 'oog') from "Election"

work in both Firebird 2.1 and 2.5

I'm using the FlameRobin client.


texts:
I'm designing a database to capture car data. Here's a simplified design for capturing year, make, model, and trim:

This should handle data like this:

2017 Ford Mustang GT

However, it turns out that the GT trim can come in two body types: Coupe and Convertible. So, the data now looks like this:

2017 Ford Mustang GT (Coupe)
2017 Ford Mustang GT (Convertible)

In order to accommodate the body type, I added a FK in the trims table that references the body_types table:

So, whenever I need to reference a specific car, I do so via trims.id, which gives me the makes.name, models.name, trims.name, trims.year and the associated body type. This second design is prone to anomalies if I ever need to update trims.name, however.

In order to combat that, I added a trim_body_types table:

Now, if I want to refer to a specific car, I do so via trim_body_types.id. I think this 3rd design is probably normalized, but will also probably cost me in JOINs.

Assume that the appropriate UNIQUE constraints are in place.

QUESTIONS

Is my 3rd design the correct normalized design?
If so, should I accept the denormalized 2nd design instead in order to save on JOINs?
Any potential pitfalls with the designs I'm considering (specifically Design 2 and Design 3)?
Or am I completely off and need to consider a different solution?


texts: How to list all past remote login information

texts:
Instead of using "Remote Desktop" for connecting to SQL Server, I and my co-workers are using SQL Server Management Studio to apply changes to the remote server (each user has their own credentials).

One of us made some changes on SQL tables that put all the data at risk. I'm pretty sure one of us is guilty. My question is this. How can I prove this? Is there any sort of query to show all past remote connections from a client to a remote SQL Server instance? Maybe with IP addresses, computer names, credentials.

Does SQL Server Management Studio have some sort of log file or any query to show remote server logins?

P.S. No full backup on remote the SQL Server instance. Logs are pretty clean.

This is how we login to SQL Server:


texts: we are developing an application  for a company and this company has 3 branches, we design a SQL server for our application we can put this server in one of the branches but as the connection between these branches is over internet and this connection can be lost some times but the SQL server availability is vital so we decided to mirror the server in each branch, I need a simple solution for how should i config these servers to be mirror of each other with the same privilege to do changes in DBs, all we want to do is configuring 3 SQL server exactly with the same data. we read about merge replication but we want a simpler solution because our scenario is not really enterprise.


texts: MySQL user password vs authentication_string

texts:
I run following commands:

CREATE USER 'dbuser'@'%' IDENTIFIED BY PASSWORD('mypass');
CREATE USER 'dbuserx'@'%' IDENTIFIED BY PASSWORD('mypass');
SET PASSWORD FOR 'dbuser'@'%' = PASSWORD('mypass');

And that results in:

MariaDB [(none)]> select host, user, password,authentication_string from mysql.user;
+------------------+---------+-------------------------------------------+-------------------------------------------+
| host             | user    | password                                  | authentication_string                     |
+------------------+---------+-------------------------------------------+-------------------------------------------+
| %                | dbuser  |                                           | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
| %                | dbuserx | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |                                           |
+------------------+---------+-------------------------------------------+-------------------------------------------+

Is this proper? I read there is PAM in MariaDB by default, but why user creation does not store password in authentication_string right from start?

Could someone explain the difference and possible problems with this?

Mariadb 10.3


texts: SQL Server 2012 restrict CmdExec to sysadmin server role

texts: I'm trying to restricting CmdExec rights to sysadmin server role in SQL Server 2012. I've googled but just found some solution which they were for older version of SQL Server. Can you please help?


texts:
In SQL Server, why is a tinyint stored with 9B in the row. For some reason there seems to be an additional one byte at the end of the NULL bitmap mask.

    USE tempdb ;
    GO

    CREATE TABLE tbl
    (
        i TINYINT NOT NULL
    ) ;
    GO

    INSERT INTO tbl (i)
        VALUES (1) ;
    GO

    DBCC IND ('tempdb','tbl',-1) ;
    GO

    DBCC TRACEON (3604) ; -- Page dump will go the console
    GO

    DBCC PAGE ('tempdb',1,168,3) ;
    GO

Results (I reversed the bytes due to DBCC PAGE's showing the least significant byte first):

Record Size = 9B
10000500 01010000 00
TagA = 0x10 = 1B
TagB = 0x00 = 1B
Null Bitmap Offset = 0x0005 = 2B
Our integer column = 0x01 = 1B
Column Count = 0x0001 = 2B
NULL Bitmap = 0x0000 = 2B (what!?)


texts: mssql server set up fail with errror on debian linux

texts:
I was trying to install SQL server on my Debian Linux. Installation part went well but when I tried to run setup file I am getting following error message. Is there any way to fix this issues. Thanks

File "/opt/mssql/bin/../lib/mssql-conf/mssql-conf.py", line 126, in <module>
exit(mssqlconfhelper.setupSqlServer())
File "/opt/mssql/lib/mssql-conf/mssqlconfhelper.py", line 441, in   setupSqlServer
ret = checkInstall()
 OSError: [Errno 2] No such file or directory

There are few other lines too but most of them are something similar to above and related to .py files.


texts: non clustered index coverage with INCLUDED columns

texts:
If I have two indexes:

IDX_1 = (COL1), (COL2) DESC
IDX_2 = (COL1) INCLUDE (COL3)

will a single defined below cover both queries? does the Descending keyword have any bearing on the usage of INCLUDE statement for covering both queries?

IDX_3 = (COL1) INCLUDE (COL2), (COL3)


texts:
My mongodb server has a database named villageContents

It has a collection named tablebusiness

If I run mongo I saw

MongoDB shell version: 2.0.7
connecting to: test
>

I wonder what "test" is. There is no database named test there.

I tried to execute

> villageContents.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})
Wed Aug 15 09:28:28 ReferenceError:is not defined (shell):1
>

I tried to execute

> test.villageContents.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})
Wed Aug 15 09:29:13 ReferenceError: test is not defined (shell):1
>

What did I do wrong?

Then I did

db.villageContents.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})

Nothing shows up. Indexes aren't even added.

So what's wrong?


texts:
I need to know what is my database version.

I'm using DB2 V9.7 on linux 64 bits.
I'm connected to a database and i need to know if it is 32 or 64 bits.

Can someone help me?


texts:
I have searched online but not found what I am looking for.

The problem/query is to my knowledge a database in SQL Server should have One .mdf file and maybe some .ndf files and One .log file.

I have seen many database with one mdf and multiple ndf files. But recently I came across some database on a server where every database had multiple mdf files.

I took a backup of some database moved them to another server and restored the Primary file as .mdf and all the other files as .ndf obviously except the log file it was restored as .log.

Now my questions are:

Is there any wisdom in having multiple .mdf files for a database?
Is there any drawbacks of having multiple .mdf files?

It was SQL Server 2008 R2 64bit Enterprise Edition

Any advice or pointer in the right direction is much appreciated. Thank you.


texts: Is Oracle DB licensing needed for a test environment?

texts:
I'm planning to setup an Oracle Standard Edition instance on a server in our test environment.

Do I need to purchase a license for this?


texts:
I wrote the following function to query data from tables and columns that I give as arguments:
create or replace function field_summaries(gap interval , _tbl anyelement, _col text)
 returns SETOF anyelement as
 $func$
 BEGIN
 RETURN QUERY EXECUTE
 'select
 time_bucket(' || gap || ', time)::text as hour,
 avg(' || _col ||'),
 min(' || _col ||'),
 max(' || _col ||')
 from ' || pg_typeof(_tbl) || ' d
 where d.device_id in (
     select device_id from sensors)
     group by hour';
 END
 $func$ language sql stable;

The problem is that when call the function like this:
select field_summaries('5minutes', NULL:: m_13, 'temperature');

I receive the following error:
ERROR:  syntax error at or near ":"
LINE 2:  time_bucket(00:05:00, time)::text as hour,                 ...
                       ^
QUERY:  select
time_bucket(00:05:00, time)::text as hour,
avg(temperature),
min(temperature),
max(temperature)
from m_13 d
where d.device_id in (
select device_id from sensors)
group by hour
CONTEXT:  PL/pgSQL function field_summaries(interval,anyelement,text) line 3 at RETURN QUERY

Does anyone have an idea of what this could be?


texts: Error: 9245, Severity: 16, State: 1. / "During the last time interval X query notification errors were suppressed"

texts:
We are receiving this notification several hundred times a day and while we're regularly checking notifications from Severity 16 errors, this isn't something we're really concerned about.

I've read the article and replies at Error: 9245, Severity: 16, State: 1. / During the last time interval XXX query notification errors were suppressed and while that's very informative, I'd like to know how can we suppress it?

We have an automated alert set up for severity 016 errors and we don't exact categorize this one as an "action items" yet are receiving hundreds of them daily.

Is there a way to suppress it?


texts: Columnstore Index Scan Cardinality Estimates

texts:
In SQL Server 2012, I am seeing large cardinality estimate discrepancies for a columnstore index scan. It looks like statistics have been generated for the index, but I don't see any details in DBCC SHOW_STATISTICS (this may not be supported for columnstore indexes).

Can statistics be updated for a columnstore index, or are there other ways to address this large cardinality estimate discrepancy? I have not been able to find definitive documentation but noted that UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index. UPDATE STATISTICS is valid only when used with the STATS_STREAM option exists in sys.sysmessages.


texts: 2 Way replication issue faced of records skipping/not replicating

texts:
We have a replication setup wherein data entry is done at head office and also at multiple branch locations; data replicates from h.o. to multiple branch locations; also data entry done at branch locations gets replicated back at h.o. location and from h.o. replicates to all branches so that each branch can see other branch data as well as h.o. data.

Many times issue faced is that a) even though; the replication monitor does not show any issue; but few records are skipped while replicating from say branch A location to h.o.

b) at other times issue faced is that even though data from say branch C comes to h.o.; it does not replicate to branch B; even though it reaches h.o. Is there a way to decipher logs that will tell which transactions/entries have not replicated at particular location. We are using SQL Server 2008 R2.

Thanks in advance.


texts: SQL Server Transactional Replication Distribution Database not keeping up

texts:
We have following setup at the moment:

8 Cluster servers (SQL Server 2012 Standard Edition) talking to 2,000 store servers in the field via Merge Replication.

The 8 servers are replicating to a reporting DB server (SQL Server 2008 R2 Enterprise).

We have partitioned tables using the server ID from 8 servers on this reporting database so we can have data in one database and not going to 8 individual databases. It using Push Transactional Replication.

We would like to do the following:

Create a New Transactional Publication on this reporting DB server which would replicate to 2 SQL Server 2012 Standard nodes for scale-out.  We've implemented this, and it's working as expected.

Issue we are running into:

One of the articles gets about 3 million updates (i.e. Delete and Insert coming from 8 cluster servers) twice in the morning and twice in the evening.

This causes a lot of activity in the Distribution database under MSRepl_Commands table, which the reporting server is not keeping up with causing the entire replication to the "scale out" nodes to come to a halt.  Commands keep piling up under the Distribution database at the reporting database server.

How can we make sure these Updates under this particular Article are getting cleaned up quickly at the Distribution database and keep replication to scale out nodes in good condition?

UPDATE: I am constantly seeing blocks when Distribution Cleanup job is running:

"Update MSsubscriptions..." is creating block ON this statement "UPDATE MSdistribution_history..."

One of the column in this particular article is VARBINARY(MAX) would that make a difference with large batch of INSERT and DELETE?


texts:
I don't get any output for this simple PRINT statement. What am I missing?

Data supplied in sqlfiddle.

DECLARE @team varchar(20)

BEGIN
   SELECT @team = TEAM
   FROM DISTRIB_LINE
   PRINT 'value is' + @team
END


texts: Oracle Database 12c on Oracle Linux 7 - autostart, enabling connection as sysdba

texts:
I've installed 12c on Oracle Linux 7 for my development work, as per document Oracle Database Installation Guide 12c Release 1 for Linux (E41491-10). My previous experience with administering Oracle Database is version 8 on Novell Netware and 10gR2 on Windows x64 at my prevoius workplace. I have not worked with Linux too much, save for some simple services on Ubuntu.

This time I choose not to create database on installation, so I had to run Oracle Database Configuration Assistant to do so, because I needed some options that default database did not have. I've done everything by the book, but I encountered problem one: ORACLE_HOME or ORACLE_BASE env vars were not set after creation. I had to edit .bash_profile to set these.

Database was created in default location, which is /home/oracle/app/oracle/oradata/<SID>. It was started and configured with listener. Upon completion of database creation, I noticed problem two: I can't connect to oracle instance using sqlplus / AS SYSDBA. I am presented with ORA-12162: TNS:net service name is incorrectly specified. As document Oracle Database Error Messages 12c Release 1 (E49325-06) states, I checked if TNSNAMES.ORA doesn't contain any errors in connect descriptor, but it doesn't. Full contents of this file are (KITET is my instance name):

KITET =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORALINUX7VM)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = Kitet)
    )
  )

I connected only by specifying sqlplus <user>/<password>@<service_name>.

After rebooting my virtual machine, I noticed problem three: listener and oracle do not start automatically. I can start listener by using lsnrctl start, but can't start up the database (can't connect using / AS SYSDBA). I figured I could connect by specifying connection string with service_name, but I was greeted with ORA-12505: TNS:listener does not currently know of SID given in connect descriptor.

After looking here and there on the internet, I found out that by exporting ORACLE_SID, I could alleviate "can't startup" problem, so I did just that by editing (again) ~/.bash_profile.

What I still need done

I need listener and database startup automagically on boot, on Oracle Linux 7. Of course I am in posession of a document Oracle Linux Administrator's Guide for Release 7 (E54669) but there's no information on how to create startup scripts for Oracle Database and Listener. There's just info on starting and stopping EXISTING services. Of course I can't login to my VM box and startup services everytime I power it up.

Please assist.


texts: Updating multiple tables with similar structure in one query in mysql

texts:
I have multiple tables "100+", with the exact similar structure, yes I know I probably should not have it this way but it is what it is now.

What I want to do is, update all tables in one query without having to specify the table to each field.

Like so:

update table1,table2,tableN set field1 = "value" WHERE id = 1;

This of course throws an error "field1 is ambiguous", so I could do it like so:

update table1,table2 set table1.field1 = "value", table2.field1 = "value" WHERE table1.id = 1 AND table2.id = 1;

While this works, I'd rather not have a query that has 100+ table names and 100+ "ands" in the condition statements, I don't even know what's the limit on this thing.

Assuming there is no other way to do it, would it be better performance-wise, to loop through tables names and perform 100 queries instead?

Thanks


texts: Using dynamic date filter causes data not to return

texts:
Sorry for the cryptic title, as I'm not sure how to state this question simply.

The problem is this, I have a date column, called DTDATE, the data stored in it is of DATE type. When I filter against this column using a static string such as '03/APR/17' queries return a result. Perfect.

However, I want to create a dynamic query that will return the first day in the week (Monday in this instance) and the same day last year (e.g. the Monday of this week last year). That is, comparing two sets of data year to date (YTD).

The filter would look something like this:

      DTDATE = (SYSDATE - (TO_CHAR(SYSDATE,'d')-1))
  OR
      DTDATE = (add_months(SYSDATE,-12)-(TO_CHAR(add_months(SYSDATE,-12),'d')-1))

The problem is, the query returns no result!

IF, however, I change the query to the following, I get a result set:

      DTDATE = (SELECT SYSDATE - (TO_CHAR(SYSDATE,'d')-1) FROM DUAL)
  OR
      DTDATE = (SELECT add_months(SYSDATE,-12)-(TO_CHAR(add_months(SYSDATE,-12),'d')-1) FROM DUAL)

You will notice I am now specifically selecting the date for each row of data returned in the filter. While this works, the cardinality of the data set goes from 14,475 in a static filter (e.g. date stated in a string), to 2,387,764!

....That's a bit of an issue for run time XD

So, my question is WHY wont the query run without selecting into DUAL?
I can use SYSDATE by itself as a filter (tested).

Notes about the data: The data set is from an MIS, that loads data on a weekly basis each Monday. As such, datasets are only present from a tag date of Monday of each given week. Hence the date requirement of Monday of the given week.

Note I am using Oracle Developer 4.1.5.21 on an 11g server (we update to 12c later this month).


texts:
I want to update a large number of rows (more than 80k) from an external data source (from file) into an existing mysql table. This database is InnoDB.

Database structure:

CREATE TABLE IF NOT EXISTS `jobs` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `job_date` date DEFAULT NULL,
 `client_code` varchar(100) DEFAULT NULL,
 `department` varchar(100) DEFAULT NULL,
 `job_order` float(10,2) DEFAULT NULL,
 `completion_date` date DEFAULT NULL,
 `achivement_date` date DEFAULT NULL,
 `achivement_value` float(10,2) DEFAULT NULL,
 `current_status` varchar(100) DEFAULT NULL,
 `supervisor` varchar(100) DEFAULT NULL,
 `job_no` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

Update QUERY

UPDATE jobs SET job_date='$job_date',client_code='$value[1]',department='$value[2]',job_order='$job_order',completion_date='$completion_date',achivement_date='$achivement_date',achivement_value='$achivement_value',current_status='$value[7]',supervisor='$value[8]' WHERE job_no='$value[9]

How to optimize this query ?


texts: Cannot rename database (the user in --username is the owner)

texts:
I connect with psql and list my databases:

                                          List of databases
           Name            |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
---------------------------+----------+----------+-------------+-------------+-----------------------
 accyem_20150422           | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 accyem_template           | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 clinicama_template        | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 intercontinental_template | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 pijalarga                 | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 postgres                  | postgres | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 sbu_template              | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 sf_20150505               | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 sf_20150527               | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 sf_template               | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 tarpuq_template           | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 template0                 | postgres | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | =c/postgres          +
                           |          |          |             |             | postgres=CTc/postgres
 template1                 | postgres | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | =c/postgres          +
                           |          |          |             |             | postgres=CTc/postgres
 yucha                     | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |

I am interested on sf_template database.

I want to run the following command:

PGPASSWORD=mypassword  psql --username=openerp --dbname=sf_template --no-password --command="alter database sf_template rename to sf_template_20150605111128"

But get the following error:

  ERROR:  current database cannot be renamed

As I see in the returned results, the user I log in with is the owner of the database. Why can't I rename the database?


texts: Create a User and Password for ADO.net and SQL SERVER Authentication

texts:
I am trying to create a sample application  in asp .net mvc with sql server authentication.

I want to create a user with password in sql server so that i can provide it in connection string in asp .net mvc web.config.
My question is how to create a user in sql server 2012 and later login using that user and password,and create databases and tables for my entity framework in .net

What are the steps to create a user and password??


texts: determine redo log size, number of redo log files

texts:
Having 2 node RAC instance using ASM
Log switches happening are too high about 100 per hour causing db to hang for few minutes sometimes

   GROUP#  THREAD#    MEMBER                                    ARC STATUS   FSIZE(MB)
---------- ---------- ----------------------------------------- --- -------- ----------
1          1 +CCFA_DATA01/ccfada/group_1.327.956874159          YES ACTIVE      50

2          1 +CCFA_DATA01/ccfada/group_2.329.956874159          NO  CURRENT     50

3          2 +CCFA_DATA01/ccfada/group_3.328.956874142          YES ACTIVE      50

4          2 +CCFA_DATA01/ccfada/group_4.326.956874161          NO  CURRENT     50

Could you help determine the right size of redo log files, right number of redo log files and steps to resize redo log files in ASM (DB size is approx. 6.5Gb)


texts:
I try to create a cluster with log in folder using PostgreSQL on Debian 8

First I create db folder, and mark postgres as owner.

Then I create logs folder, and and logs/log file.

Try to create cluster:

$: pg_createcluster -d usr/db/db -l usr/db/db/log --start 9.5 db

Creating new cluster 9.5/db ...
  config /etc/postgresql/9.5/db
  data   /usr/db/db
  locale C.UTF-8
  socket /var/run/postgresql
  port   5434
Warning: systemd does not know about the new cluster yet. Operations like "service postgresql start" will not handle it. To fix, run:
  sudo systemctl daemon-reload
Warning: the cluster will not be running as a systemd service. Consider using systemctl:
  sudo systemctl start postgresql@9.5-db

Run:

$: sudo service postgresql start
$: sudo systemctl daemon-reload
$: sudo systemctl start postgresql@9.5-db
Job for postgresql@9.5-db.service failed. See 'systemctl status postgresql@9.5-db.service' and 'journalctl -xn' for details.
$: journalctl -xn
    Sep 30 12:20:21 artem-debian sudo[30438]: root : TTY=pts/3 ; PWD=/usr/db ; USER=root ; COMMAND=/bin/systemctl start p
Sep 30 12:20:21 artem-debian sudo[30438]: pam_unix(sudo:session): session opened for user root by artem(uid=0)
Sep 30 12:20:22 artem-debian postgresql@9.5-db[30441]: Cluster is already running.
Sep 30 12:20:22 artem-debian systemd[1]: postgresql@9.5-db.service: control process exited, code=exited status=2
Sep 30 12:20:22 artem-debian systemd[1]: Failed to start PostgreSQL Cluster 9.5-db.
-- Subject: Unit postgresql@9.5-db.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql@9.5-db.service has failed.
--
-- The result is failed.
Sep 30 12:20:22 artem-debian systemd[1]: Unit postgresql@9.5-db.service entered failed state.
Sep 30 12:20:22 artem-debian sudo[30438]: pam_unix(sudo:session): session closed for user root

$: pg_lsclusters

9.5 db 5434 down   postgres /usr/db/db         /usr/db/db/log

$: service postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
   Active: active (exited) since Fri 2016-09-30 13:59:41 EEST; 12min ago
  Process: 31652 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 31652 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service

$: sudo pg_ctlcluster 9.5 db start
Redirecting start request to systemctl
Job for postgresql@9.5-db.service failed. See 'systemctl status postgresql@9.5-db.service' and 'journalctl -xn' for details.

$: systemctl status postgresql@9.5-db.service
● postgresql@9.5-db.service - PostgreSQL Cluster 9.5-db
   Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled)
   Active: failed (Result: exit-code) since Fri 2016-09-30 14:17:07 EEST; 2s ago
  Process: 32536 ExecStart=postgresql@%i --skip-systemctl-redirect %i start (code=exited, status=2)

Sep 30 14:17:07 artem-debian postgresql@9.5-db[32536]: Cluster ...
Sep 30 14:17:07 artem-debian systemd[1]: postgresql@9.5-db.ser...2
Sep 30 14:17:07 artem-debian systemd[1]: Failed to start PostgreSQL Cluster ....
Sep 30 14:17:07 artem-debian systemd[1]: Unit postgresql@9.5-finstatement.co....
Hint: Some lines were ellipsized, use -l to show in full.


texts: Remote Connection to SQL-Server for data extract

texts:
I have zero experience with MSSQL Server, but I'm trying to determine the most effective way to extract data from table into flat files from a remote location.
Here's the usage case scenario:

We are a data-integration company and we have some customers who run software ( ERP ) on top of a MS SQL Server. The ERP vendor previously supported a extract and delivery process directly to our ( company ) location. The ERP company has now become unresponsive to our ongoing extract issues ( bugs ) and we're preparing for the possibility of losing customers as a result (even though we have a contract with the software company).

I'm assuming that there's a way for us to connect directly to the MSSQL server and extract the data, bypassing the ERP vendor extract solution altogether, but I'm not familiar w/ MSSQL or Connections / Connectors at all.

Would anyone be willing to share with me if this sort of thing is possible and what the best route for me to continue my research might be?


texts: Why did full backup file size grow after data movement for Availability Group replica was suspended?

texts:
Found an error log informing that data movement to a secondary availability replica was suspended. I'm not sure whether the cause of the suspension is relevant to answering my question, but the cause was "Error 3456 ... could not redo log record" etc. This is a known issue with a resolution involving patching SQL Server.
However this condition wasn't addressed for a few days. The nightly full backup file sizes (taken against the primary replica) began increasing by about 10% per night. The database also has log backups made every 15 minutes for most hours in the day, but log backups are disabled during a window at night while indexes are rebuilt and full backups made.
To get the secondary back online it was required to restore from backup. In this instance some issues were encountered - first an attempt was made to rejoin the AG but that hung for ages. Then a command was given to have that replica database removed from the AG but of course it hung too. Killed both those processes but could not kill the session that had faulted in redoing the log records (as it is not a user session - although its ID was above 50 by the way). I therefore restarted SQL Server on that replica before eventually restoring from backup and rejoining the AG.
Having resolved the issue, the next nightly full backup file size (on the primary, again) had reduced back to normal size.
Why would the full backup file increase in size until this issue was resolved? Is it because the log could not be cleared and this was being included in the full backup? If so, then what was getting backed up in the 15-minutely log backups? Imagine for a moment that the primary had suffered a failure during this period - could I not have restored from the available full and log backup files I had (ie. because obviously something remained in the log such that it wasn't clearing)? And if it was a case of the log not clearing, then why (because the secondary had been suspended, so surely the primary no longer waits for the secondary before committing its own log)?
Afterthought: the system process that I could not kill without a server restart was the one that faulted while attempting to apply the logs to the secondary - leading the secondary to be suspended. I suppose this is a "long running transaction"? If this is the reason, can someone still please clarify for me the earlier questions - particularly about how "at risk" the primary was until this was resolved?


texts: Can we generate SQL Server MDS Model from an existing database schema?

texts:
I have googled this quite a bit and am surprised to not find an answer.

I'm considering using SQL Server MDS to manage our canonical schema, but I certainly don't want to key in my entire database schema to create the Model.

Does SQL Server MDS support creating a Model by reverse engineering a database?


texts: How can I make a database backup go faster?

texts:
I recently attended an interview for my next job and was asked this:

How can I speed up the backup of a database sized close to 1TB or 800 GB in SQL Server?

My Answer: We can use Database Compression or use a third-party tools like Light Speed.
But they expected this answer which I was not aware of:

Solution : Increase throughput by adding more destination files

Example
I created this sample script by adding more backup destination files:
DBCC TRACEON (3605, -1)
DBCC TRACEON (3213, -1)

BACKUP DATABASE [AdventureWorks2008] TO
    DISK = N'E:\ADWBackup\AdventureWorks2008-Full.bak',
    DISK = N'E:\ADWBackup\AdventureWorks2008-Full-File2.bak',
    DISK = N'E:\ADWBackup\AdventureWorks2008-Full-File3.bak'
WITH
    NOFORMAT, INIT, NAME = N'AdventureWorks2008-Full Database Backup',
    SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

DBCC TRACEOFF(3605, -1)
DBCC TRACEOFF(3213, -1)

Can you please answer the below questions:

What is the use of following this method instead of using a single file?
Will there be any time difference if I do like this?
How can I restore the DB using the multiple split files?
Will it really increase the speed of the backup?


texts: How to apply transpose method in MYSQL, Getting rows as collumns

texts:
have a table of ideas that stores responses from evaluators and stores as one row per evalautor For ex : Evalautor 1 evaluated idea A then in table record gets added as
IDEA A, COMMENTS , EVALUATOR ID

as single idea have 4 evaluator response and i need to show data in this format
IDEA ID ||. EVALUATOR 1 || EVALUATOR 2|| EVALUATOR 3 || EVALUATOR 4
1 --        Comments --    Comments --   Comments --    Comments

Need assistance in creating logic to get information in format given above.
Query i am running :
 SELECT * from Toy_ideas GROUP BY evaluator id

but getting results as
Ideaid -- comments -- evaluator id


texts: How to find out which data I just imported into Oracle?

texts:
I imported two dump files (no idea what they contained, my goal is precisely to find out what they contain) into Oracle, but now I am struggling to identify what is the data I imported, and what data was originally there. On a fresh install of Oracle 11g.

The number of schemas does not seem to have changed, 37. I have browsed around and most of what I see seems to be Oracle's own data (note: I am an Oracle beginner).

To import, I use the first command for the first file, then the second because I was told by imp that DBA was needed for that file:

imp nico/nico file=C:\data\FILE1.DMP
imp system/thepassword file=C:\data\FILE2.DMP full=yes

Does the command line output of imp show the identifiers of all imported data?
Is there a sort of history logs where I can see which tables/rows have been imported recently?

I have spotted one imported table in SYSTEM, but would like to have an exhaustive list.


texts: Can't understand results of pg_stat_statements view

texts:
I configured my postgresql.conf (my version is 9.1) with the normal persistent configuration:

shared_preload_libraries = 'pg_stat_statements,plperl'
custom_variable_classes = 'pg_stat_statements,plperl'
pg_stat_statements.max = 100
pg_stat_statements.track = all
pg_stat_statements.save = on

I let one month to run within a database which is constantly accessed with read/write queries.

After a month (or generally enough time to be sure lots of queries were executed) I do the documentation query with top five slowest queries, and get this result:

-[ RECORD 1 ]---------------------------------------------------------------
query       | UPDATE XXXXXXXX
calls       | 63486
total_time  | 238.271792999997
rows        | 40740
hit_percent | 99.9938060778852878
-[ RECORD 2 ]---------------------------------------------------------------
query       | SELECT XXXXXXXX
calls       | 67347
total_time  | 0.852444000000713
rows        | 67347
hit_percent | 100.0000000000000

... and so on. I see the real number of calls as total_time is (very) much bigger for this queries than this view says. I don't understand how this works, because I expected hours in the total_time field as my server is running for more than 3 months without restarting.

In general is a no-change-from-doc configuration, and I get nonsense results.


texts: Database ID of a non-existent database - time-out waiting for buffer latch on page appearing in error log

texts:
Background:
I've recently started at a new shop with lots of databases in various states. I arrived this morning (having just been added to the SQL DBA mailing group) to find an email about the CHECKDB job failing last night on one of the servers (one I've been warned has been problematic).

I won't dwell on the details of this here, it appears to be down to a number of time-outs waiting for buffer latches - by itself it's hard to tell why this is, as there should've been (and appears to have been) very little system usage last night when this was run.

There are ongoing problems with I/O requests longer than 15 seconds in the logs, and the vmware guys say that there have been communication problems with storage (on-going network issues), so this goes someway to explain the problem I think.

Important Note: I reran CHECKDB this morning and it completely quickly, finding 0 errors and as far as I'm aware nothing has happened since the failure last night, so (correct me if I'm wrong), I'm pretty confident there is no database problems, and last night was an anomaly, possibly triggered by network or storage problems (I'll know more about this when the network guys get back to me... if they do).

The actual point of this:
In the logs, right at the point where last night's CHECKDB job failed, there is a time-out waiting for a buffer latch - the strange thing to me is that the database id of the page it's supposedly trying to latch is 11, and there is no database with that id on the instance - ids only go up to 10.

This is followed up by the CHECKDB line with 1 errors found, 0 repaired, pointing to an Internal Database snapshot having a split point (see below for error log)

Specific Questions:

Why would there be a reference to a database ID that doesn't exist (do Internal database snapshots get assigned a database ID at some point and that's what it was referring to)?
Is there anyway of finding out what the database snapshot in question is (is it something to do with the way CHECKDB works, or is it something else)?
Is there a way of finding what happened to the snapshot in question (as it obviously wasn't a problem when I ran CHECKDB again this morning).

Error log:

01/04/2017 23:50    spid101
Unknown DBCC CHECKDB (zenworks_UAL) WITH no_infomsgs executed by ARTSLOCAL\svc_zcmsql found 1 errors and repaired 0 errors. Elapsed time: 0 hours 28 minutes 51 seconds.  Internal database snapshot has split point LSN = 00adf51f:00019bf4:0001 and first LSN = 00adf51f:00019bf3:0001.

01/04/2017 23:50    spid101
Unknown Unable to read and latch page (1:2067184) with latch type SH. Latch failed.

01/04/2017 23:50    spid101
Unknown Error: 8966 Severity: 16 State: 2.

01/04/2017 23:50    spid101
Unknown A time-out occurred while waiting for buffer latch -- type 2 bp 00000004FFFF4A80 page 1:2067184 stat 0xc2040d database id: 11 allocation unit Id: 0/281474976710656 task 0x000000015BA6A088 : 0 waittime 300 flags 0x100000001a owning task 0x000000015BA6A088. Not continuing to wait.

Note:
As far as a know, no one else was working the database or running anything significant between the failure last night and me running CHECKDB this morning. The system was in use, but not significantly. Nothing is showing in the logs.

To confirm, there is no DB of ID 11, and I don't think there ever was (and as certain as I can be not between last night and this morning

Query:

SELECT db_name(11)

Results:

NULL

Query:

SELECT * FROM sys.databases

Results:

╔════════════════════╦═════════════╗
║        name        ║ database_id ║
╠════════════════════╬═════════════╣
║ master             ║           1 ║
║ tempdb             ║           2 ║
║ model              ║           3 ║
║ msdb               ║           4 ║
║ ReportServer       ║           5 ║
║ ReportServerTempDB ║           6 ║
║ z_xxxxxx_L         ║           7 ║
║ z_xxxxxx_p         ║           8 ║
║ S_xxxxxx_t         ║           9 ║
║ z_xxxxxx_t         ║          10 ║
╚════════════════════╩═════════════╝


texts: Should I use utf8_unicode_ci or utf8_slovak_ci?

texts:
I was using utf8_slovak_ci collation until now, but I read that utf8_unicode_ci sorts very wide range of languages. Does it mean, I do not need to use separate collation for every language and I can simply always use utf8_unicode_ci?

Lets say all content on my website is in Slovak language - is there difference between using utf8_unicode_ci or utf8_slovak_ci?

I use MariaDB 5.5 (they say it is functionaly identical to MySQL 5.5).


texts:
I  cannot comment on https://stackoverflow.com/questions/8437957/difference-between-3nf-and-bcnf-in-simple-terms-must-be-able-to-explain-to-an-8, so I have to post my question here. It is about the relations on this table cited as the most favored answer on that thread:

 Pizza    Topping     Topping Type
 -------- ----------  -------------
 1        mozzarella  cheese
 1        pepperoni   meat
 1        olives      vegetable
 2        mozzarella  cheese
 2        sausage     meat
 2        peppers     vegetable

According to the source of the answer, it is in 3nf. However, I thought there is only one candidate key which is (Pizza,Topping). (Pizza,Topping Type) cannot be a candidate key because a Pizza can have multiple Topping Type values.

A candidate key must be able to identify a distinct tuple, and (Pizza, Topping Type) does not meet this condition. So it seems to me that the above relation is not in 2nf, and therefore not in 3nf. Topping Type only tells something about Topping, but not Pizza. Am I missing something?


texts:
I've had an error message telling about index corruption on the .myi file

Reparing the table fixed the issue

This table is accessed with select only, so what is likely to corrupt indexes ?

CREATE TABLE `my_table` (
 `col1` mediumint(1) unsigned NOT NULL,
 `col2` mediumint(1) unsigned NOT NULL,
 `fk_brand_trans` smallint(1) unsigned NOT NULL,
 `ref_trans` varchar(25) COLLATE latin1_general_ci NOT NULL,
 `col3` smallint(1) unsigned NOT NULL,
 `attribute` smallint(1) unsigned NOT NULL,
 `ref` varchar(25) COLLATE latin1_general_ci NOT NULL,
 `pos` tinyint(1) NOT NULL,
 KEY `veh` (`col2`,`col1`),
 KEY `trans` (`ref_trans`,`fk_brand_trans`) USING BTREE,
 KEY `brand` (`fk_brand_trans`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=DYNAMIC

50M rows, 1.8GB data, 1.4GB index


texts: Change current working directory from MySQL client prompt

texts:
Is there anyway to change the current working directory from the MySQL client prompt?

That would allow me to use relative paths for the tee, source, and another command which I don't recall just now (I just noticed that the post got truncated my copy/pasting, and I had listed a few example commands in the original text file draft -- being new to MySQL, I'm that familiar with the commands off the top of my head).

I found from other apps that changing the working directory is extremely useful in general, e.g., in Vim and Matlab.


texts: Where can I find guidelines for building a SQL Server machine specifically for DBCC CHECKDB work?

texts:
I'm working on defining specs for a new server to run DBCC CHECKDB scans on all databases in our portfolio (85 instances, 1300 DBs, 15 TB allocated). I have a custom application that copies dump files to a single server, then performs the restore and DBCC and required logging for reporting, but sequential I/O throughput is my bottleneck (I was working with set of RAID 6 LUNs from our SAN using 7.2K spindles, getting 300 MB/s read but only 50 MB/s write).

Is there a whitepaper or other resource available to help spec out a server for Maximum Consumption Rate specifically for DBCC? I looked at the MSFT whitepaper for Fast Track Data Warehouse servers, but I'm not sure it's the right fit.

Thinking outside the box, I'm contemplating going to a DAS enclosure like PowerVault and using RAID 0. The system DBs will be on RAID 1 on local storage but using RAID 0 for the restore and DBCC work makes sense because as soon as I finish testing a DB I drop the DB to make room for the next one. I can keep a few spindles as hot spares to rebuild a down array if needed and pick up where I leave off.

Any suggestions would be deeply appreciated!


texts: what does || (Parallel sign) mean in DB2 Sql?


texts: i want to show the attendance of a student in percentage

texts:
In my project attendance management system. I want to show the attendance in percentage after entering enrollment of a particular student
table in database
fsignup(name,email,password,subject)
ssignup(name,email,enrollment,semester,password,branch)
attendance(enrollment,subject,attendance,date,faculty_id)


texts: SQL Server 2005 Database move and rationalisation - Recommended maintenance processes

texts:
We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct.

I inherited this database and it came to me in a bit of a mess truth be told - on top of that I've basically learnt to be a SQL Server developer on this server (no educational background in computing) so I've made plenty of my own mistakes on it. We're underfunded as a department (public sector), and so lacking expertise, pulling ourselves up by our bootstrings. The database started out as a back room thing but has become very important to our organisation and given this the performance is frankly embarrassing.

So we have issues such as

history of Autoshrink usage
History of undersized storage - never really enough space on the server for the db to grow so I'd guess the files are probably quite internally fragmented.
There are multiple collation types (for no good reason),
Inappropriate text datatypes all over the place (lots of Nvarchar, nchar, ntext when we have no need of ascii characters, and text where varchar would be more appropriate)

The new server is a 64bit virtual server, storage on RAID.

My plan after installing and upgrading is to:

Remove a vast amount of tables we've identified (with confidence) as redundant (about 1/3 of db size!)
Normalise collations to 1 type (after some analysis and testing we're certain this won't cause us problems - we have no requirement for case sensitivity)
Normalise text data types to varchar/char
Set database file sizes to the expected usage for the coming 2 years plus 50%

Is there anything else I should be considering after/before these processes - and do any of decisions here look off mark? For example the file size issue - is this something I need to worry about on RAID?


texts: How to check that stored procedure is valid

texts:
As we know, it is possible to compile and to have so stored procedures:

CREATE PROC dbo.MyProc1
AS
  SELECT * FROM Not_Exists_Or_Removed_Table

or

CREATE PROC dbo.MyProc2
AS
  SELECT
   Id
   ,Removed_Column
 FROM MyValidTable

The table Not_Exists_Or_Removed_Table is not exists or was removed (MyProc1) and  column Removed_Column was removed (MyProc2).

How to detect and prevent creating so stored procedures?


texts:
I have recently converted one of our tables to Memory table and have setup heap size to 30GB. This tables only gets updates and not inserts, so I was wondering if the table rows are updated frequently would the heap fills up, as we get 10k updates a minute on this table and rows just gets update with new data.

Would this fill up heap all the way to 30gb or since no new rows are added and MEMORY usage remains constant throughout ?


texts: I'm importing several gigantic csv files into a table - so I'm wondering what would happen if I ran COPY FROM jobs concurrently?


texts:
I am having a terrible time finding an appropriate way to store this data in a database.

The current excel (simple) version looks something like this:

http://i.stack.imgur.com/BEZOw.png

Staff and Client also have additional data associated with them, so it makes sense to use a foreign key relationship there. However, I am unsure how to store the hours data. Obviously you could make a column for each Time Period, however this seems like a very bloated approach.

I then thought that each "cell" that contains hours data can be represented like this:

http://i.stack.imgur.com/4VTXQ.png

However, this seems like the Staff and client key values are stored too many times. I have never designed a proper database before and would greatly appreciate suggestions.

*Sorry for not directly linking images, I do not have enough rep on this sub-site


texts:
I'm having trouble understanding Postgres's CASCADE DELETE behavior.

If table_A REFERENCES table_B, and the column referenced could have multiple instances of the same value, will the table_A row be DELETEd even if there are still valid references upon a table_B DELETE?

If so, how can table_A only be DELETEd by CASCADE when there are no valid references left?


texts:
Does having no brackets at "WHERE" produce same results as having brackets? I have same result, just wanted to confirm, because from my memory it always best to use rounded brackets.

SELECT `document`.*, `document_info`.`name`
FROM `document`
LEFT JOIN `document_info` ON `document_info`.`document_id` = `document`.`id`
WHERE `document`.`user_id` = 4358 AND document.status = 4 OR document_info.status > 0

vs

SELECT `document`.*, `document_info`.`name`
FROM `document`
LEFT JOIN `document_info` ON `document_info`.`document_id` = `document`.`id`
WHERE `document`.`user_id` = 4358 AND (document.status = 4 OR document_info.status > 0)


texts:
I'm trying to figure out if it is bad practice to have more than one path between tables that are related via many-to-many relationships.

I have 3 user-facing tables: Dupe, Cue, and Take.  There is a many-to-many relationship between Dupes and Cues, and there is a many-to-many relationship between Takes and the join table Dupe_Cues.

The user can add, delete, and modify Cues, Dupes and Takes, and she can make links between Cues/Dupes and Cues/Takes, but there are also certain situations when the user will want to directly add a Take to a Dupe, without creating a cue.  So I figure the simplest thing to do is create a Dupe_Take table.  The problem is, I'm worried it will cause referential issues and isn't good database design.

If I don't create a Dupe_Take table, I suppose I could add a field to the Cue table, like "b_CreatedByUser".  Then, whenever the user does a query, I filter out Cues where b_CreatedByUser=FALSE so that Cues that are only used to link Takes to Dupes are never shown to the user, but that sounds sloppy and difficult to maintain.

What is the best practice?  What factors should I be considering?

Thanks for any advice.


texts: SQL 2008 R2 creates user/schema when Windows user creates tables

texts:
We added a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity:

USE master
go
CREATE LOGIN [DOMAIN\AppUsers] FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
USE AppDb
go
CREATE USER [DOMAIN\AppUsers] FOR LOGIN
[DOMAIN\AppUsers]
go
EXEC sp_addrolemember N'db_owner', N'DOMAIN\AppUsers'
go

When the DOMAIN\User1 account logs onto the app, User1 queries tables in the dbo schema just fine because User1 is a member of DOMAIN\AppUsers, but this app allows the user to create tables too. When creating these tables without specifying a schema, SQL Server does the following:

Creates a 'DOMAIN\User1' user in AppDb that uses a 'DOMAIN\User1' login not listed in SSMS\Security\Logins for the instance.
Creates a 'DOMAIN\User1' schema in AppDb.
Creates those tables using in the new 'DOMAIN\User1' schema.

I am completely baffled by these results. Here are my questions:

I would expect the table creation to fail rather than create additional objects. Can someone point me to the part of Books Online that explains this?
Why doesn't the server create a 'DOMAIN\AppUsers' schema and add the new tables to that schema if it is going to add schemas?
Also, how does the database use a login not shown in SSMS\Security\Logins?
Looking at the 'DOMAIN\User1' user in SSMS\Databases\AppDb\Security\Users, the user icon has a small red arrow pointing down. What does that mean?

We are just starting to use Windows Authentication within an organization that preferred SQL Authentication for simplicity, so I am sure my question comes from being ignorant of the differences. This code was written long before we considered using Windows Authentication, so I sure we need to improve our understanding of creating new schemas when logged on using Windows Authentication as anyone other than the database owner.

In case you can't tell, I am the one pushing for the use of Windows Authentication over SQL Authentication. If we don't get to a solid understanding of this, we will revert back to SQL Authentication.


texts: Can I index all columns of a table in MySQL safely?

texts:
I have one table with 4 columns. One is primary key and other three columns are references to columns of other tables. I am using join on these four tables for a search filter. These joins are taking time.

So I was thinking to have indexes on columns because I read that I should create indexes on columns of join conditions. My question is will there be problem if all columns of the table are indexed? Is there any other way to decrease time complexity of these search filters.

More Hint:

Table1(main search)-1000 million entries primary_key fk1 fk2 fk3
Table2-800 million  entries pk1 ..(8-9 columns)
Table3-700 million entries pk2 ..(10-12 columns)
Table2-850 million entries pk3 ..(7-8 columns)

I am using MyISAM engine.


texts: Extended Events XEL file reader; Is there a FileName property avaialble in QueryTableXEventData or SqlBulkCopy

texts:
Using the QueryTableXEventData class in Powershell I am able to parse 100's of XEL files very quickly and export their content to a table in SQL Server using SQLBulkCopy
Example:
$events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData
("\\some_file_path\XELog_Files*.xel")

I don't see though a File property in the the $fields array.
$event | Select-Object -ExpandProperty Fields

is_cached     System.Boolean              True
is_recovered  System.Boolean             False
is_dac        System.Boolean             False
database_id   System.UInt32                 73
packet_size   System.UInt32               8000
options       System.Byte[]  {32, 0, 0, 40...}
options_text  System.String
database_name System.String

Or in $events
Name      : login
UUID      : 13e22e12-3cb8-49bf-a3e1-131faa95601c
Package   : Microsoft.SqlServer.XEvent.Linq.Internal.XEventInteropPackage
Metadata  : Microsoft.SqlServer.XEvent.Linq.Internal.XEventInteropEventMetadata
Timestamp : 18/08/2020 03:41:37 +00:00
Fields    : {is_cached, is_recovered, is_dac, database_id...}
Actions   : {server_instance_name, database_name, database_id, client_hostname...}
Location  : Microsoft.SqlServer.XEvent.Linq.EventLocator

Is there a property I can use in SQLBulkCopy or QueryTableXEventData that has the file name currently being read or exported?


texts: How to replicate particular table in MySQL master-slave configuration?

texts:
How do I replicate a particular table in a MySQL master-slave configuration?
I have tried replicate-do-table and replicate-wild-do-table.
I tried in my.ini file; is there any alternative to replicate without using replicate-do-table and replicate-wild-do-table?
I need a binary log statement to replicate a single table.


texts: Frustrating SQL Server and VS Express 2013 Issue

texts:
I recently reinstalled Windows and am trying to set up SQL Server again with an existing database. I've tried installing SQL Server 2012 and 2014, and continuously get the same error. The error says that the database is version 782 and this version of SQL Server is only compatible with 706 and below. I get this error with both SQL Server 2012 and 2014. I thought 2014 was compatible with 782+.

How can I attach the database?


texts: Are duplicated NVARCHAR values stored as copies in SQL Server?

texts:
I'm designing a table that will contain a lot of rows. So need to be careful not to store to much information. One of the columns is a NVARCHAR(MAX) column and it contains the address of our customers. As addresses do not change often, this column will contain many repeated values and thus contains quite some redundancy.
So I was wondering if I need to normalize this myself by maintaining some sort of look-up table to address strings (note that if an address changes I need to maintain history - so it's not a matter of usual normalization), or if SQL Server is pointing to the same reference of the string behind the scenes. Or maybe it offers a column option to do so. Another approach that came into my mind is to use COMPRESS but I guess this does not make sense as the data itself (i.e. the address) is not long.
Reading/writing performance is not so much of a concern as the data will be accumulated over time.


texts: Deleting the MySQL 'root' user on purpose

texts:
I created a new mysql user with all the same privileges as the current 'root' user. For security reasons I thought why not create another user for this so people at least won't know the username of my super user.

I then dropped the root user.

Immediately my DB started throwing connection refused errors for all of my other users.

I quickly recreated the original 'root' user and everything magically started connecting again.

My question is then, is it possible to delete the root user in a MySQL database? And if so how?

Thanks.

EDIT 1:

All other security options are in place. We are not just securing our system by trying to remove the root user. We have some over zealous security people here and removing the MySQL root user was just an extra step.

I agree with @Pleun for the reasons I tried to remove it.


texts: SQL Server 2012 Reporting Services: "Unable to connect to the remote server"

texts:
I am trying to install SQL Server 2012 Reporting Services on Windows Server 2008, but all the web interface says after adding the feature and confirming all the default settings in Reporting Services Configuration Manager is

  Error: Unable to connect to the remote server

In the logfile there is this message:

  System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:80

This worked just few months ago on the same system.


texts: Is it possible, in MySQL, to run a 'login profile' similar to the bash/ksh profile, when connecting with the mysql client? That is to say, something similar to mysql -e 'select * from a_table', but where mysql doesn't terminate after executing the statement?


texts: Processing Dimension completed but error occurs

texts:
I have problem while processing cube in SSAS, everytime I have an error:

I tried to process every dimension separately and it looks like something bad was going on after processing 'Date' dimension.

Any ideas why?

Best regards,
Ana


texts: Identical Servers but different query execution plans

texts:
We have two server with identical hardware configuration and SQL server configuration.
The only difference in the server's being:

Server SQL1 - 256 GB RAM

Server SQL2 - 128 GB RAM

The same database has been restored on both servers. The query execution plan for a query on both servers is different.The MAXDOP on both servers is 4. But the query runs in 10 mins on SQL1 but takes 1 min on SQL2.

What would cause the query execution plans to be different? SQL1 where the query is slower, does have more load on it when compared to SQL2. Does load matter when SQL server decides on a query execution plan?

We set MAXDOP = 1 on SQL1 and we saw the query complete in 1 minute.

What would explain this?

Thanks


texts: MsSql Bulk Insert With commit size 1 vs Simple Insert

texts:
Using SSIS OLE DB Destination, what is the difference between doing fast load with commit size 1 or inserts without the fast load option.

I understand that using fast load is going to invoke the bulk insert command, while the second option is just a simple insert. The actual question is whether there is any performance overhead doing bulk insert with maximum commit size equal to 1.

An explanation of what is happening under the hood in each case will be very appreciated, and whether there is any difference across various versions, starting with 2008.


texts:
Despite a lot of searching, I can't seem to find a solution to this problem we are having.

Our system enables users to submit certain processes to be run in background while they continue working. These processes are submitted as jobs using DBMS_SCHEDULER and we have a couple of job classes for serial and parallel processing depending on the job the user needs doing.

When we take the system down for maintenance, we stop running jobs and reenter them on the queue, and disable all scheduled jobs. This is fine for a standard maintenance window as without our system online, users have no ability to submit new jobs. After maintenance, all jobs are re-enabled and everyone's happy.

However, there are times when we would like to be able to disable the queue while the system is still live - often to free up some resources in peak times. The only thing we can do at the moment is disable all jobs currently scheduled, but obviously that doesn't stop people submitting new jobs to the queue.

Does anyone know of a way to either

stop people entering jobs on the queue
force any new jobs to be entered on the queue in a disabled state (preferred option)?

Many thanks in advance


texts: MDX misunderstanding: complex FROM clause

texts:
I have a MDX query which syntax is familiar to me:

SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS,
       NON EMPTY { ([Projects].[Id].[Id].ALLMEMBERS * [Priorities].[Id].[Id].ALLMEMBERS ) }
FROM [Reports]
WHERE [CreatedOn].[Date].&[2010-01-01T00:00:00]:[CreatedOn].[Date].&[2010-02-01T00:00:00]

But I can rewrite the query such way:

SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS,
NON EMPTY { ([Projects].[Id].[Id].ALLMEMBERS * [Priorities].[Id].[Id].ALLMEMBERS ) } ON ROWS
FROM (SELECT [CreatedOn].[Date].&[2010-01-01T00:00:00]:[CreatedOn].[Date].&[2010-02-01T00:00:00] ON COLUMNS
FROM [Reports])

New query returns the same results, but FROM section looks obscure to me. How query can have two FROM section? Where can I find information about this syntax?


texts: Best practice for other Table Data in an Order

texts:
At the moment I try to create a good Database structure for storing all Data belonging to an Order/Inquiry.

I use MySQL and Python at the moment and for now it isn't an real DB, just learning and testing!

The Order contains Information from the following Tables of my DB:

Customer
Address
Person
Product --> Copied to an Order Product

My questions are:

With the Order Product i copy all Product Data to an new Table and "attach" it to the OrderID, that was my best guess to make the Order Products independent from changes to the product later on. Something like a new Price for the Product which should not affect old orders to be changed. Is that a good way to achieve this?
I think i have the same problem with Addresses, Persons and also the Customer it self. If a customer changes its address i dont want to transfer that change to old orders. What is the best way to store such data independent or is there any other way of doing this?
We have Idicators for our IDs to make it easier to recognize them. An ID for an Address Looks like this "AD0000001" or for an Contact Person "CP0000001" i noticed that i have to seperate the number and the Characters to do auto_increment in my DB. Is it a good practice to save the indicating Characters as an Default Value to my Table? And put them together in my Select Queries?

If i have missed some information or anyhing else, let me know! I will add it to my post!


texts:
I'm trying to set up streaming replication between two Postgres servers. The master server's pg_hba.conf file is configured this way:

host    replication     ${REP_USER}     0.0.0.0/0               md5
host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5

And the initializer script in the slave container runs the following at startup to connect to it:

cat > ~/.pgpass <<EOS
${MASTER_PORT_5432_TCP_ADDR}:5432:${REP_USER}:${REP_PASS}
EOS
chmod 0600 ~/.pgpass

echo "Cleaning up old cluster directory"
rm -rf ${PGDATA}/*

echo "Starting base backup as replicator"
pg_basebackup -h ${MASTER_PORT_5432_TCP_ADDR} -D ${PGDATA} -U ${REP_USER} -vPw

All the env values are set. The problem is I keep getting this error:

pg_basebackup: could not connect to server: fe_sendauth: no password supplied

I'm not sure why this keeps happening. I have the password file in place with the right permissions. I also have the password in the recovery file, which doesn't get created because pg_basebackup fails all the time.

Any help would be appreciated...


texts: Oracle update statement using with clause takes for ever

texts:
I am trying to write an update statement, which utilizes the result of a series of aggregations of with statement as below.

update final_table
set(destination_col1,destination_col2)=
(
select c4,c5
from
(
  with temp_table1(c1,c2,c3) as
  select c7,c7,c9 from source_table,

  temp_table2(c4,c5,c6) as
  select "some aggregation on c1,c2",c3 from temp_table

  select * from temp_table2
)
where c6= final_table.primary_key
);

This runs forever.

Whereas, when I try to break the same into 2 queries it works.

Creating a table using the inner query.
Use the above-created table to update like,

update final_table
    set(destination_col1,destination_col2)=
    (select c4,c5 from step1_table where c6=final_table.primary_key)

As suggested here, I tried to check if there are any locks. But I don't have any.

Am I abusing the with statement? Why exactly my expectation is not met?


texts: Research Database: Access and Ownership Policies

texts:
I'm a PhD student in an academic research laboratory. One part of my project involves collecting samples and generating data. Another part involves establishing a research database to store the results. As the project has grown, a Masters student and two post-doctoral fellows are also working on the samples and generating data. All data relating to the samples I collected is to be added to the database, regardless of who generated the data. Each entry in the database has an "owner" field to keep track of this. Our supervisor would like the database to be used more extensively by people working on other projects as well.

I am meeting some resistance from members of our group (both those workings on "my" project and on their own/other's projects) regarding submitting data/results to "my" database. At a recent meeting, I explained once again the concepts of a (central) database, that queries can easily extract data, that the "ownership" field keeps track of whose data point it is, and reminded everyone of the benefits of a central database which stores all data generated. I highlighted the kinds of analyses which can be undertaken comparatively on different data sets, how meta-analyses can be done, and so on. I then offered to do some research around the issue of database management and ownership. Some people in the group do not want to submit data to the database until their papers are published, despite the fact that it is impossible for anyone else in the group to simply throw together a "competing" paper from the data alone and publish it "first". This is also impractical because it means that data which is never published is never added to the database. It also means that no-one else has access to it UNTIL the paper is published, thereby slowing down other research. Internal sharing of this kind of data should be routine.

I would appreciate any suggestions of resources or comments around these issues. Should everyone sign a "waiver" regarding their data going INTO the database and another one regarding the use of data FROM the database? We're not a particularly mistrustful group, but everyone else in the group are "wet lab" researchers, so the concepts of databases and sharing data is possibly a little foreign to them.


texts: Migrating SQL 2012 Analysis Services to another server

texts:
We have a SQL Server 2012 installation that was built on...let's say a compromised Windows installation.  It's had so many installs/uninstalls done on it that nothing runs consistently, and we're moving off it to a new clustered instance.  However, I can't seem to get the Analysis Services databases moved.  All the articles I find are about migrating from 2005 or 2008 to 2012, and reference MigrationWizard, which apparently does not exist with SQL Server 2012.

Has anyone moved Analysis Services from one platform to another, both under 2012?


texts: PostgreSQL: changing data directory location (sshfs)

texts:
I'm trying to change my PostgreSQL data directory initially on my Debian server to my NAS (running also on linux).
To do so, I've mounted it on a specific directory through sshfs (in /mnt/titanBDD).

Now I get this error when starting postgre (using service and pg_ctlcluster):

  Error: could not exec /usr/lib/postgresql/9.4/bin/pg_ctl
  /usr/lib/postgresql/9.4/bin/pg_ctl start -D
  /var/lib/postgresql/9.4/redirect_TITAN -l
  /var/log/postgresql/postgresql-9.4-main.log -s -o  -c
  config_file="/etc/postgresql/9.4/main/postgresql.conf"

Here is the modified part of postgresql.conf:

data_directory = '/var/lib/postgresql/9.4/main'

modified in:

data_directory = '/var/lib/postgresql/9.4/redirect_TITAN'

Where redirect_TITAN:

0 lrwxrwxrwx 1 root root 18 nov.   1 16:23 /var/lib/postgresql/9.4/redirect_TITAN -> /mnt/titanBDD/main

I've used the following command to move my data:

root@machine:~# cp --preserve=all -R /var/lib/postgresql/9.4/main/ /mnt/titanBDD/

Acutally, in /mnt/titanBDD I have:

root@machine:~# ls -als /mnt/titanBDD/
total 12
4 drwxr-xr-x 1 postgres postgres 4096 nov.   1 16:19 .
4 drwxr-xr-x 3 root     root     4096 nov.   1 14:05 ..
4 drwx------ 1 postgres postgres 4096 nov.   1 15:59 main

What is going wrong? I guess it's a context issue but don't have any idea where to search.


texts: How to best distinguish between "types" of missing data?

texts:
I am working on large tables (> 100 fields each) currently organized in flat-files in csv-format. An Oracle DB is to be created to store all the data and allow for several more or less intricate analyses.

Some of the fields are Strings, a large portion of fields are of numeric data types though. The system in the end should allow querying large individual tables (I do not expect frequent joins) and carrying out basic arithmetic operations on respective columns.

However, in terms of missing values, I have to be able to distinguish between multiple different types of NULL (denoting different reasons for why data is missing). How would I best encode this with the least possible effect on overall DB performance?

Options I thought of:

Every field as String and carry out all computation in the
application layer (testing for NA1, NA2,... + casting to type).
Extending Types by e.g. NA1, NA2, NA3,...
For each field create an
additional field with a default value of 0 (no missing data),
otherwise describing why data is missing while using NULL in the
original field.

Personally, I feel like Option 3 should be the way to go. However, this would increase the table size even more (several hundred fields) - which, then again, could be solved by splitting the table. How would you go at it?


texts: Drastic performance difference in View Dependencies between SQL Server 2005 and 2008 R2

texts:
Background
I am a fledgling DBA who was hired in the middle of a migration from SQL Server 2005 x64 to 2008 R2 x64. We also migrated from a local server to a colo virtual server, and moved some of our databases over to a second virtual server, hoping to ease some of the load from the main SQL Server which hosts a Sage MAS 500 system.
Unfortunately, despite the virtual server having more resources than the old 2005 server, we have been experiencing performance problems, and I have been tasked with fixing these. I haven't made much progress, but today I noticed something strange:
Question
I've been checking dependencies for various tables and views, and noticed the time it takes to get results (especially when viewing objects which depend on the view or table in question) from 2008 R2 is much longer than it takes on the old 2005 server. Also, in some cases, different results are returned even though there has been no change in the tables/views since migrating. At the time I checked on the 2008 R2 server, there was virtually no activity so resources shouldn't have been a problem.
It seems to me this is symptomatic of our problems. Does anything come to mind why there would be such a drastic difference in View Dependency performance, or can anybody point me in a direction to investigate?


texts: How would I model a product to have multiple image URLs and have only one as the default?

texts:
Background: I'm using Postgres with Golang GORM library by jinzhu

Here is a basic representation of what I'm looking for, however I dont think it would be right to have a circular reference like this.  Is there a better way to achive what I'm looking for?  One idea of mine is to omit the primary_pictures_id and add a boolean field to the images table to flag a picture as the default (just not sure yet on how to enforce only one to be true)

products
    id
    name
    price
    primary_picture_id FK:images(id)

images
    id
    url
    products_id FK:products(id)


texts: Installation of PostGIS extension not working on a docker image (seems not found by further commands)

texts:
Context

I have a PostgreSQL code for installing the PostGIS extension which is working great on my machine but which doesn't work on a docker image. I cannot understand why.

Here are the details.

Working code (own machine):

Here is the psql code on my Ubuntu 18.04 laptop with PostgreSQL 10.12 and PostGIS 2.4;

$ sudo -u postgres psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# select version();
                                                                version
----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)

postgres=# create database foo;
CREATE DATABASE
postgres=# \c foo
You are now connected to database "foo" as user "postgres".
foo=# CREATE SCHEMA IF NOT EXISTS postgis;
CREATE SCHEMA
foo=# CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA postgis;
CREATE EXTENSION
foo=# CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION
foo=#

Everything went fine.

Not working code (docker image):

$ sudo docker run -d --rm --name pgrouting -e "POSTGRES_HOST_AUTH_METHOD=trust" pgrouting/pgrouting:12-3.0-2.6.3
$ sudo docker exec -it pgrouting psql -Upostgres
psql (12.2 (Debian 12.2-1.pgdg100+1))
Type "help" for help.

postgres=# select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.2 (Debian 12.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

postgres=# create database foo;
CREATE DATABASE
postgres=# \c foo
You are now connected to database "foo" as user "postgres".
foo=# CREATE SCHEMA IF NOT EXISTS postgis;
CREATE SCHEMA
foo=# CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA postgis;
CREATE EXTENSION
foo=# CREATE EXTENSION IF NOT EXISTS postgis_topology;
ERROR:  type "geometry" does not exist
foo=#

The same error appears with this docker image having an older PostgreSQL version (9.6): kartoza/postgis:9.6-2.4, so it's not a PostgreSQL 12 issue.

I didn't remember having made fancy installation steps; the PostGIS I installed on my machine was simply installed using apt. Same for PostgreSQL.

Question

What could be wrong here, why is my own installation working out-of-the box and not the docker images?

May be related: https://gis.stackexchange.com/questions/361715/strange-postgis-errors-after-having-altered-its-extension-by-specifying-the-post

Edits:

Here is the content of the postgis_topology.control file on my laptop:

$ cat /usr/share/postgresql/10/extension/postgis_topology.control
# postgis topology extension
comment = 'PostGIS topology spatial types and functions'
default_version = '2.4.4'
relocatable = false
schema = topology
requires = postgis

And this is the content of the same file but on the docker image:

# cat /usr/share/postgresql/12/extension/postgis_topology.control
# postgis topology extension
comment = 'PostGIS topology spatial types and functions'
default_version = '3.0.0'
relocatable = false
schema = topology
requires = postgis


texts:
Im extracting data from salesforce using COzyroc SSIS component. This package is scheduled as job in MSSQL server which runs on daily basis.

This job failes on alternate days. One day it is executed successfully and another day it fails.

What might be the reason ? I have attached the error screenshot for your reference.

PLease someone help me


texts: Determine size of mysqldump prior to dumping

texts:
This question is a followup on this previous question.

Q: How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?

I have tried using the most upvoted answer but it doesn't seem to be working in my case. I am actually trying to determine a specific database's size prior to dumping, so I am using this query

SELECT
    Data_BB / POWER(1024,1) Data_KB,
    Data_BB / POWER(1024,2) Data_MB,
    Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema ='db_name') A;

I am getting a filesize of ~4.5mb whereas my (uncompressed) dump takes less than 2mb. I have also tried excluding VIEWs from my dump as such:

SELECT
    Data_BB / POWER(1024,1) Data_KB,
    Data_BB / POWER(1024,2) Data_MB,
    Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema ='db_name' AND engine IS NOT NULL) A;

but this made no difference. Am I doing something wrong here?

(Most of my tables are InnoDB except for two who are MyISAM but contain almost no data)


texts: Where can I find a syntax reference for Oracle SQL Developer's "Generate DB Doc" function?

texts:
Oracle SQL Developer (I'm using v3.2) has a feature called "DB Doc", which generates documentation for database objects. I mostly want to use it to generate documentation for my stored procedures, functions, packages and types. However, I can't find any documentation for it, describing what syntax I should use.

I have worked out that I should use a /* ... */ comment on the line(s) immediately above my procedure/whatever, and (by borrowing from JavaDoc) I have successfully used @param and @returns statements, but I'm not sure what else I can use. For example, are there fields for author, version, data modified, etc.?

I've done numerous searches of this site, Google, and Oracle's documentation, to no avail!


texts: Need suggestion on optimizing the query which calls a scalar function

texts:
--gets the configuration (team or client or default)

CREATE FUNCTION [dbo].[get_config]
(@TEAM_CONFIG BIT, @CLIENT_CONFIG BIT, @DEFAULT_CONFIG BIT)
RETURNS BIT
AS
    BEGIN

    DECLARE @bit BIT = 0

    select @bit =
        CASE WHEN @TEAM_CONFIG IS NOT NULL
                THEN @TEAM_CONFIG
            WHEN @CLIENT_CONFIG IS NOT NULL
                THEN @CLIENT_CONFIG
            ELSE @DEFAULT_CONFIG
        END

    RETURN @bit
END

--add config for questions in the same order

    select
  @parameterstring = CONCAT(MAX(CASE WHEN q.QUESTION_ID = 'question1'
                                 THEN dbo.get_config(team.CONFIG, client.CONFIG, q.DEFAULT_CONFIG)
                                 ELSE 0
                                 END),
                            MAX(CASE WHEN q.QUESTION_ID = 'question2'
                                 THEN dbo.get_config(team.CONFIG, client.CONFIG, q.DEFAULT_CONFIG)
                                 ELSE 0
                                 END),
                            MAX(CASE WHEN q.QUESTION_ID = 'question3'
                                 THEN dbo.get_config(team.CONFIG, client.CONFIG, q.DEFAULT_CONFIG)
                                 ELSE 0
                                 END),
                            MAX(CASE WHEN q.QUESTION_ID = 'question4'
                                 THEN dbo.get_config(team.CONFIG, client.CONFIG, q.DEFAULT_CONFIG)
                                 ELSE 0
                                 END),
                            MAX(CASE WHEN q.QUESTION_ID = 'question5'
                                 THEN dbo.get_config(team.CONFIG, client.CONFIG, q.DEFAULT_CONFIG)
                                 ELSE 0
                                 END))
  from query

-- query itself is performing good (if there are no concatenations involved, but, there might be another way of concatenating strings with config bits in the same order...


texts: Unable to access tables in sql server using oracle dblink

texts:
I have created a Heterogeneous  connection between oracle and sql server. and tested with below query,

SELECT * FROM sys.tables@SQLSERVER;

Then I have created a new Database in SQL server called "TESTCONNECTION" and created a table called "dbo.TEST". After that I changed the default database of DSN to "TESTCONNECTION". But below query fail with following error message.

SELECT * FROM dbo.TEST@SQLSERVER;

  [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
  'DBO.TEST'. {42S02,NativeErr = 208}[Microsoft][ODBC SQL Server
  Driver][SQL Server]Statement(s) could not be prepared.
  {42000,NativeErr = 8180

}

How to overcome this error and access tables created under "TESTCONNECTION".


texts: Select from two tables with not direct relationship

texts:
OK i found myself in a dead end, and i know must be a way but my brain is just about to explode. This is the case: I have two tables with tons of rows, one for works done (lets call it works table), something like this:

ID | Home_Work_ID | task_id | Person_id
1  | 23           | 1       | 30
2  | 23           | 2       | 31
3  | 23           | 3       | 30
4  | 876          | 1       | 31
5  | 123          | 3       | 32

and another table to report the fixes to do on the works mentioned before, lets call it fixes table

ID | Home_Work_ID | Person_reporting_id | Task_id | Details
1  | 23           | 93                  | 1       | Fix this
2  | 23           | 85                  | 3       | Fix that
3  | 123          | 86                  | 3       | Fix something

As we can see, in the fixes table there are home works with fixes reported, and those home works was done by the person_id 30 and 32 (from the work table). The results that im trying to achieve would be:

Person                               | Fixes
John (lets say this is person id 30) | 2
Kate (lets say this is person id 32) | 1

The problem is, i need to create a report that show me who was the person responsable of the work done in the works table that have reported fixes in the second table and how many fixes was reported for that person. The only link is the home work id and probably the task id, so i was testing something like this

SELECT P.person_name AS Person, COUNT(F.id) AS fixes
FROM fixes F
INNER JOIN homeworks H ON F.home_work_id = H.id
INNER JOIN works as W
INNER JOIN people AS P ON W.person_id = P.id
INNER JOIN tasks AS T ON T.task_id = F.task_id
WHERE F.task_id = W.task_id
AND F.home_work_id = W.home_work_id
AND W.home_work_id IN (SELECT home_work_id FROM fixes GROUP BY home_work_id)
GROUP BY P.person_name ORDER BY fixes DESC

Ok there are three more inner/left joinable tables with the id and name of the person, home work and task. This show me the person responsable but the number of fixes for that person and that home_work/task dont match the ones in the fixes table, i guess im doing wrong inner joining the work table that way but i dont see the light yet. Any help will be really appreciated.

Regards


texts: What can be done to prevent CVE-2016-6662?

texts:
This assumes mysqld_safe script that starts the MySQL

root     14967  0.0  0.1   4340  1588 ?        S    06:41   0:00 /bin/sh /usr/bin/mysqld_safe

mysql    15314  1.2  4.7 558160 47736 ?        Sl   06:41   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

two processes where first can be modified based on my.cnf
as explained in the document.

--malloc-lib=LIB

http://legalhackers.com/advisories/MySQL-Exploit-Remote-Root-Code-Execution-Privesc-CVE-2016-6662.html