texts: Mysql Join to get multiple row value from

texts:
I am new to my Mysql and need help regarding join
I have a table called employee and this table contain some foreign key value like phone, address, sex. Table phone and address have 4 rows in it, I want to get value of all rows. I only know how to get single row value but failed to get multiple row value

this is the code I am using to get single row value from from foreign key:

SELECT e.employee_id, e.first_name, e.last_name,
e.address_id, e.phone_id, e.dob, e.maritial_id,
e.sex_id, e.photo, s.sex_label
FROM employee AS e JOIN sex AS s ON e.sex_id = s.id

SHOW CREATE TABLE phone

CREATE TABLE `phone` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `mobile` varchar(15) DEFAULT NULL,
 `mobile_one` varchar(15) DEFAULT NULL,
 `phone` varchar(15) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

SHOW CREATE TABLE address:

CREATE TABLE `address` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `address_one` varchar(50) DEFAULT NULL,
 `address_two` varchar(50) DEFAULT NULL,
 `state` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8


texts:
I'm looking to compare the dates in the result set

Example:

SELECT * FROM tbl WHERE foo=bar

Output

id, datetime, name
1, 2014-01-01 18:13:45, test 1
2, 2014-01-01 19:01:32, test 2
3, 2014-01-01 20:34:44, test 3
4, 2014-01-03 04:45:22, test 4

I would like to be able to tell between each record if the difference in time is less or more than 24 hours of the previous record.

Example:

1, 2014-01-01 18:13:45, test 1 <-- first record since the previous record
2, 2014-01-01 19:01:32, test 2 <-- second record was less than 24 hours since the previous record
3, 2014-01-01 20:34:44, test 3 <-- third record was less than 24 hours since the previous record
4, 2014-01-03 04:45:22, test 4 <-- fourth record was more than 24 hours since the previous record

thoughts?


texts: PostgreSQL event trigger to force extension installations into particular schema

texts:
I have a dedicated schema extensions (PostgreSQL v 9.6.3) where I intend to install all extensions for my DB. I would like to somehow disallow (or at least remind) users installing extensions without specify particular schema.

So this should fail

CREATE EXTENSION tablefunc;

but this should succeed:

CREATE EXTENSION tablefunc SCHEMA extensions;

For this purpose, I tried creating event trigger:

CREATE EVENT TRIGGER e010_verify_extension_schema_et
  ON ddl_command_end
  WHEN TAG IN ('CREATE EXTENSION')
  EXECUTE PROCEDURE verify_extension_schema();

and in verify_extension_schema() function I am trying to make use of pg_catalog.pg_event_trigger_ddl_commands().
However, the function does not return anything I can use to check if schema has been specified or which schema for that matter...

Does anyone have any ideas how I could accomplish this?


texts: Appropriate multi-language schema, or overkill?

texts:
UPDATE 2: I've actually ended up using this, and it's great after a couple tweaks. Here's my post on its actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/

I am building a web app, and I want it to support multiple languages. This structure has two components:

Connecting locale ('english','Deutch', etc) with terms, and having a rosetta stone connecting terms, and terms in specific language.
Grouping terms by page. I don't want to say, SELECT term1,term2,etc through the 30+ terms I might need on a page. I want to ask by the page they're connected to.

Here is my proposed table structure (note all the id's have relationships/indexes among them to make very efficient queries):

  * locale
      * id
      * value //English, Deutch, etc//
  * terms
    * id
    * value //In English//
  * page
    * id
    * value //Think add entry, menu//
  * page_group //group all terms to a page, for easy pulling//
    * id
    * page.id
    * term.id
  * rosetta
    * id
    * locale.id
    * term.id
    * value //french word for amount, description, etc//

This will allow queries like:

SELECT localization.value,
        terms.value
FROM localization
INNER JOIN terms ON terms.id=localization.termid
INNER JOIN page_group ON page_group.termid=localization.termid
INNER JOIN page ON page.id=page_group.pageid
INNER JOIN locale ON locale.id=localization.localeid
WHERE page.value='add_entry' AND locale.id=custlangid
ORDER BY terms.id

I only have to ask for two items; the language id that I need, and the page I need. It'll serve up all the terms, in the specified language, that are a part of the group of terms for that page.

I think this is a really good structure, but I would love some feedback.

UPDATE: To clarify, we are just talking about localization of the UI components. (labels, navigation, helpful text) All the info the user enters will be stored in unicode, not in this schema.

UPDATE 2: I've actually ended up using this, and it's great. Here's my post on it's actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/


texts:
I did ask same question on stackoverflow, but no response, hence I thought database experts will help me to solve this.

I have following table similar to Oracle user_sequences.

I have logic of sequence prefix/suffix something, but for simplicity, I'm skipping as matters less here.

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
customer_id integer);

Assume in current table there are two records.

insert into my_seq(min_value,max_value,last_value,increment_by,customer_id)
  values(1,99999999,1,1,'foo#',1),(1,999999999,100,1,'foo#',2);

My foo table structure is like,

create table foo(id Auto_increment,foo_number varchar(20),customer_id integer);

Constrained:
I can't use MySQL AUTO_INCREMENT columns as foo contains different customers data, and every customer could opt foo_number auto generation or manual entry and there should be gap if customer opted for auto_generation. So customer=1 has opted for it, foo# should be 1,2,3,4 etc, no gaps are allowed.

So far so good, with auto increment logic that we have implemented if my app runs in single thread. We generate foo_number and populate in foo table, along with other data points.

I simply do a query to get the next auto#.

select last_number from my_seq where customer_id=?;

reads the # and the update the record.

update my_seq set last_number=last_number+increment_by where customer_id=?;

Problem:
When multiple concurrent session tries the run select last_number from my_seq..., it returns same foo_number multiple times. Also, I can't enforce single thread in application because of application side limitation and performance bottleneck, hence need to solve it in database side.

Please suggest, how I could avoid duplicate numbers? Please help, thanks in advance.

I did google, many stackoverflow links suggests get_last_id(), as you could see, I can't use it. Please suggest.


texts: SQL Query taking long to execute especially with offset and fetch for pagination

texts:
This is the query I'm running for a report. It takes about 20 seconds to run without the offset and fetch. With those two, The query doesn't even complete in 10 minutes.

select *
-- h.ActionDate as Date, h.Action, h.Actor , h.PaymentLotNo
-- , sh.OwnerName as OwnersName,sh.TECHID as TechId, sh.Id as SubsidyCardId, sh.TotalSubsidy as SubsidyAmount
-- ,a.ToleEng + '-' + v.NameEng + cast(a.Ward as nvarchar(4)) + ',' + d.NameEng + ',' + s.StateNameEng as Address
from common.CTB_SUBSIDY_APPLICATION_HISTORY h
join fsams.CTB_SUBSIDY_CARD_HEADER sh on sh.LotNumber = h.PaymentLotNo
join fsams.CTB_SUBSIDY_CARD_ADDRESS sa on sa.SubsidyId = sh.Id
join common.CTB_ADDRESS a on a.AddressID = sa.AddressId
join COMMON.CLK_STATE s on s.StateId = a.StateID
join COMMON.CLK_DISTRICTS d on d.DistrictCd = a.DistrictCD
join common.CLK_DISTRICT_VDC v on v.VdcCd = a.VdcCd where 1 = 1 order by h.ActionDate desc
offset 790 rows
FETCH first 10 row only

The following is the execution plan for the query.

How do I optimize this query?


texts:
This is probably very simple but i already exported all but the scheduled jobs i created to subsequent schemas.

How can I create a sql script of the scheduled jobs I created in Oracle? I know in SQL developer, you can use the Database Export wizard to export DDLs and data but you can't use it to export prior scheduled jobs.

I am trying not to have to recreate all my jobs for each environment as I go. Can you please help!


texts: Making Session Keys of SQL instances Identical in SQL Availability Group

texts:
We recently added a master key encrypted database on a two-nodes SQL Server AlwaysOn Availability Group. Whenever there is a failover, the master key gets dropped and we have to manually add the master key back to the database on a new primary node.

I would think the databases is fully synchronized and there won’t be any loss of configuration after a failover. I talked to my vendor and they said during a failover, the client will be using the signed Master Key from the previous instance, on a database with a differently signed Master Key. They said that Session Keys are different between the SQL instances running as part of the group and that we need to make sure that the session keys are identical.

I am not sure of what to make of that:

Is there anything such as session keys in HA group?
If so, can I make them identical for each node (SQL instances) in the
cluster?


texts: How to capture data from a PowerShell script that runs as part of a SQL Agent Job?

texts:
I have a PowerShell script called from an Agent job which opens an Excel file, converts it to .CSV and saves the .CSV file in a particular location, ready for me to import it with BULK INSERT.

What I need to do is count the number of rows in the file before loading it. Where the file is above a certain number of rows, I want to load the file to a different table. This is simple to do in PowerShell, I can get the count into a PowerShell variable no problems.

However, I have no idea how to read that variable into SQL so I can use it in my BULK INSERT process.

So my question is this: When running PowerShell from a SQL Agent Job, how do I read values from the output of that PowerShell script, either into a table, temp table or variable, I don't really mind which?


texts: Scheduled snapshots of views (without using materialized views or Oracle Golden Gate)?

texts:
I have 40  views in an Oracle 18c GIS database that are used in a map in a workorder management system (WMS).

The views are served up to the WMS map via a web service.
There are an average of 10,000 rows per view.

The views have joins to dblink-tables in a separate Oracle database, and as a result, are not fast enough for use in the WMS map (3-second map refresh delay). Furthermore, it seems like a bad idea to compute the views each time a user refreshes the map--since the map does not need to be up-to-date in real-time.

As an alternative, I would like to take snapshots of the views on a weekly basis. The snapshots would be static tables that would perform well in the WMS map.

The Catch:

Unfortunately, due to office politics issues, using technology like materialized views or Oracle's Golden Gate to solve this problem is not an option.

What are my options for taking scheduled snapshots of Oracle views (without using materialized views or Golden Gate)?

For example, I could make an .SQL script that truncates static tables and inserts the rows from the views into the tables (although, as a novice, I don't know how efficient or risky that option would be, or if there are better alternatives).


texts: Can bad table-scan queries slow down nice index-covered queries at the same instance?

texts:
Let's say we have an instance used by two clients: me and my friend Ivan. Each of us has his own database. Each of us is only reading.

I'm a bad guy, so I'm relentlessly shooting table scans at the db. My tables are also huge, so they don't fit into memory.
Ivan is a nice guy, so all of his queries are covered by an index, so he has no reasons to hit the disk at all.

Will he notice any problems?

AFAIK, I'll have a bunch of threads sitting there in SUSPENDED state waiting for disk, and not generating too much of a load otherwise, so Ivan should do just fine.


texts: How to query against exact values in XML column set

texts:
I have a table that contains 80+ sparse columns along with a column set column, this is a brief example:

DROP TABLE IF EXISTS #ColumnSet
GO

CREATE TABLE #ColumnSet
(
    Id        INT          NOT NULL
  , Value1    VARCHAR(100) SPARSE NULL
  , Value2    VARCHAR(100) SPARSE NULL
  , Value3    VARCHAR(100) SPARSE NULL
  , Value4    VARCHAR(100) SPARSE NULL
  , AllValues XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
GO

INSERT INTO #ColumnSet
(Id, Value1, Value2, Value3, Value4)

VALUES
(1, 'POSITIVE', NULL, NULL, NULL),
(2, 'NEGATIVE', NULL, 'NEGATIVE', NULL),
(3, NULL, NULL, 'NEGATIVE', 'POSITIVE'),
(4, 'NEGATIVE', NULL, 'THIS IS NOT A POSITIVE RESULT', NULL)
GO

I want to query the column set to identify rows where any of the columns has a value of POSITIVE.

Using the value method on the column set will concatenate all the values together into one string and I could use LIKE but I don't want results where the value is within another string.

SELECT
    *

FROM
    #ColumnSet

WHERE
    AllValues.value('/', 'nvarchar(4000)') LIKE '%POSITIVE%'

Are there alternative methods of querying a column set to achieve the above? Using APPLY along with the nodes method provides the same concatenated string output though my syntax could be incorrect.

The required output :

id
1
3


texts: DBCC CheckDB Consistency error on an empty table

texts:
I restored a DB from SQL Server 2000.

I ran DBCC CHECKDB on SQL Server 2000, and it did not report any errors.

What could be the reason behind a DBCC CHECKDB reporting a consistency error on a empty table in SQL Server 2008?


texts:
I have a table storing parent/child records as such:

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6953  | Ajit       |   AB7   | comp2   |  7060      |     1     |
| 6957  | Rahul      |   DE1   | comp3   |  7060      |     1     |
| 6958  | uday       |   TG6   | comp4   |  7060      |     1     |
| 6959  | john       |   HY7   | comp5   |  7060      |     1     |
| 6960  | netaji     |   HY5   | comp6   |  7060      |     1     |
| 6961  | prakriti   |   GT6   | comp7   |  7060      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
| 6963  | santosh    |   KK5   | comp9   |  7060      |     1     |
| 6964  | Ravi       |   PP0   | comp10  |  7060      |     1     |
+-------+------------+---------+---------+------------+-----------+

Is it possible to return records where parent record is Enrolled and related Child record is Not Enrolled?

This returns what I need for 1 specific customer:

select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.company = 'comp1' and a.Enrolled = 1
union all
select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.parentcustid= 7060 and b.Enrolled = 0

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
+-------+------------+---------+---------+------------+-----------+

How can I structure the query to return that type of result set for all parent child records in the table?


texts: MySQL: How turning off the logging affect the performance?

texts:
OS: RHEL5
DB: MySQL 5.1.73 community edition
DB size: ~1GB

I have setup a testing environment and turned off all logging - general logs, binary logs, slow query logs etc.

I am using Oracle application testing suite (OATS) to run the load test. I am getting poor load test result (higher timing and less passed transactions) after logging off. Ideally I should get better result.

Is it correct or I am missing something?
What is the affect of getting log off?

Below is the result of

SELECT * FROM information_schema.global_variables WHERE VARIABLE_Name like 'log\_%' OR variable_name like '%\_log'

gives me:

+---------------------------------+----------------------------+
| VARIABLE_NAME                   | VARIABLE_VALUE             |
+---------------------------------+----------------------------+
| BACK_LOG                        | 50                         |
| LOG_BIN_TRUST_ROUTINE_CREATORS  | OFF                        |
| LOG_WARNINGS                    | 1                          |
| LOG_OUTPUT                      | FILE                       |
| LOG_QUERIES_NOT_USING_INDEXES   | OFF                        |
| LOG_ERROR                       | /var/lib/mysql/rpc4939.err |
| GENERAL_LOG                     | OFF                        |
| LOG_BIN                         | OFF                        |
| RELAY_LOG                       |                            |
| LOG_SLOW_QUERIES                | ON                         |
| SLOW_QUERY_LOG                  | ON                         |
| LOG_BIN_TRUST_FUNCTION_CREATORS | OFF                        |
| LOG_SLAVE_UPDATES               | OFF                        |
+---------------------------------+----------------------------+


texts: Mysql Master-Slave replication catchup and bin_logs

texts:
I have set up MYSql Replication using 2x 128gig ram 24 core cloud servers. we have a massive live database that cannot experience down time due to the high volume of data input.

I have found while dealing with the master slave is if I restart the service the bin_log and position change. Thus the slave needs to be updated and even then will be missing the data for the period it did not have the new bin_log set.

Dealing with my database which is over 400 gigs big, and is required to be live 99% of the time I cannot afford to Lock tables, Dump, transfer and import.

The slave is also very impotent as all reads are done from it and the data is highly relevant to current user interaction and as such cannot experience missing data or down time.

Moreover I am using mysql 5.5

So my questions are as follows:

1) What else if anything can cause the bin_log on the master to change.
2) How can I get my slave to consolidate and catch up the missing data without redoing the whole slave.

Any help will be highly appreciated.


texts: What are the potential issues after data or log disk failure?

texts:
Scenario:

A client recently had an issue with an Azure VM where the (non-OS) 'premium' storage disks were erroneous removed without warning by Microsoft. This is the virtual equivalent to the hard drives being yanked out of a running server.

They have SQL Server 2014 running on this VM with the data and log files placed separately on the two 'failed' drives.

The live, production, non-clustered databases are being used fairly heavily via various services and applications - with the high probability of commands and transactions having bin in-progress or mid-commit when the issue occurred.

After the issue, the VM was forcibly powered-off, and only became 'operational' after a couple of reboots.

Question:

While seemingly no issues have thus far been identified, my question is what are the possible SQL failure modes this could present? Does the transaction log / SQL server architecture account for this type of whole disk failure? Is it possible to have data loss in this situation (without considering corruption of the disk itself). And what checks should/can be performed after such an event?


texts: Support to SQL Server 2000 from Visual Studio 2013(SQL Server 2012)?

texts:
I can't access to a database created by SQL Server 2000 from Visual Studio 2013, I asked this before in StackOverflow, but I don't get an useful answer:
https://stackoverflow.com/questions/21065565/when-i-create-a-connection-to-the-datasource-of-an-old-application-throw-me-an

(SELECT @@VERSION = Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
)

After a search in google I found some workarounds with a similar problem:
http://n8v.enteuxis.org/2009/12/getting-microsoft-visual-studio-2005-to-talk-to-microsoft-sql-server-2008/
http://support.microsoft.com/kb/954961/en-us

But two solutions send me to download Microsoft Visual Studio 2005 Service Pack 1 Update for Microsoft SQL Server 2008 Support.

I can't install that, because I don't have Visual Studio 2005 and I need "VS2013 - Olds (2000)".

I have installed SQL Server 2008 (which I don't use today) and SQL Server 2012.

Don't exist a 'Microsoft Visual Studio 2013 Update for Microsoft SQL Server Olds'. What can I do?


texts: work/cache table issues, heavy inserts with heavy reads

texts:
we have a work table that stores availability of products. This table is updated with millions of inserts every night and medium amounts of inserts throughout the day.

during the updates, we delete all the previous info ,for that product. So over the course of a day we delete and insert 50% of the data, resulting in new rows that account for 50% of the table

The table is also read from heavily to get this information all throughout the day.

Right now we are seeing issues during heavy inserts. We have tracked this down to index splits. here are some questions:

1) The primary key is an auto number and a bunch of other columns. The auto number is first in order. Does this mean all entries will always be added to the end of index resulting in new leafs and not splits, therefore we do not need to worry about setting a lower fill factor? It's currently at 80%

2) The indexes are in random order, so should we use a lower fill factor such as 50%, it's currently at 80%.

3) is there a better pattern/process we can follow. Such as doing the inserts into another table then bulk copying the results in? The insert processes take hours. This way we alleviate some of the issues. Then the concern becomes the inserting of a lot of rows all at once.

Thanks in advance,


texts: Everything is ok and x is not disappearing

texts:
Since some time ago, I've started with MySQL so I don't know Workbench very well.

That little red x next to the number of the line that indicates an error does not disappear, even though everything is okay and I fixed what was wrong.

A simple example: select * project where name='Anna' and I can execute it but x won't disappear unless I delete everything and type again. This is very annoying and I can't concentrate because of always thinking about what should be wrong.

Could somebody tell me what to do to fix this? :(


texts:
We are having a database full with set ID's. To fill the database, we have to select one single ID every time. This may only be an ID where the rest of the values are set with NULL (empty).
How to do this?

SELECT `id` FROM `ids` WHERE `datetime` IS NULL

returns all empty ID's. I just have to select one of them at random.


texts: USING construct in JOIN clause can introduce optimization barriers in certain cases?

texts:
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.

I mean this key word:

SELECT *
FROM   a
JOIN   b USING (a_id)

Just in more complex cases.

Context: this comment to this question.

I use this a lot and have never noticed anything so far. I would be very interested in a test case demonstrating the effect or any links to further information. My search efforts came up empty.

The perfect answer would be a test case to show USING (a_id) with inferior performance when compared to the alternative join clause ON a.a_id = b.a_id - if that can actually happen.


texts:
I have a sql server batch file containing a lot of queries like:

    update_query_1;
    insert_query_2;
    update_query_3;

The query sequence matters, meaning they should be executed one by one. My question is, will sql server automatically optimize the query by parallel processing, which might destroy the order. If so, how to ensure the sequence?


texts: In the link mentioned here --> http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html it says there is increased virtual memory footprint when running my applications in conjunction with Instant Client.! Why is that so? If that is the case I would rather install the native Oracle client which takes time to install but still end of the day performance matters right? Why should I prefer instant client when there are memory leaks with it?


texts: Security Concerns of SQL Server Express for Web e-Voting web application

texts:
I am building a database driven web application. The web application is basically an E-Voting website, where users can register and vote on things that concern their community ... etc.

The database engine in use is SQL Server Express 2008. I know that my Domain Model and Application Code does not allow any silly security breaches, e.g. a User viewing the vote of other users ... etc.

But that aside, are there things (free) I can do to make my database secure? So even if a hacker gets access to my Db, he can't do anything with it? or make it very hard for him.

I realise this question can be a little bit open ended, but some simple tips would be greatly appreciated.


texts: Do I have to format all my drives into xfs format to better use mongodb?

texts:
From the mongo official site, xfs is highly recommended,

  Linux/Unix: format your drives into XFS or EXT4. If possible, use XFS as it generally performs better with MongoDB.
  With the WiredTiger storage engine, use of XFS is strongly recommended to avoid performance issues found when using EXT4 with WiredTiger.
  If using RAID, you may need to configure XFS with your RAID geometry.

I have a ext4 format drive mounted on the root path(/) and plan to add another xfs drive to hold all the mongo data. Do I also need to format my root drive into xfs format or not?


texts:
Recently I changed my default backup tool from mysqldump to mydumper because it was FAST! I was quite happy with the results, and a few days ago I decided to import one of my dumps. For my surprise, it spit 1 critical error importing a mysql table. Then I decided to download another backup and import it again using myloader. This time it spit 3 critical errors, 2 for mysql tables and one for my system table.
The command that I used to make the backup was just mydumper -c, and to import the databases I used the myloader -h 127.0.0.1 -u root -p PASSWORD -o -d /tmp/export-20201020-142917/
Example of the error:
** (myloader:23007): CRITICAL **: 08:48:59.778: Error restoring mysql.engine_cost from file mysql.engine_cost.sql.gz: The value specified for generated column 'default_value' in table 'engine_cost' is not allowed.

** (myloader:23007): CRITICAL **: 08:54:17.649: Error restoring plaustra.ftp from file plaustra.ftp.sql.gz: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

** (myloader:23007): CRITICAL **: 09:13:08.184: Error restoring mysql.server_cost from file mysql.server_cost.sql.gz: The value specified for generated column 'default_value' in table 'server_cost' is not allowed.

Not sure if I should just use a few other flags in addition to mydumper, or if I should stick with mysqldump?
Both servers are running mysql 8. The problems are particular to the specific dump. Today dump has 3 errors, Yesterday did not had any error. This makes me believe that I am using mydumper wrong.


texts:
What is the purpose of the "system" parameter when cataloging tcpip nodes?

>>-CATALOG--+-------+--+-TCPIP NODE--+--nodename---------------->
            '-ADMIN-'  +-TCPIP4 NODE-+
                       '-TCPIP6 NODE-'

>--REMOTE--+-hostname-----+--SERVER--+-service-name-+----------->
           +-IPv4 address-+          '-port number--'
           '-IPv6 address-'

>--+----------------+--+--------------------------------+------->
   +-SECURITY SOCKS-+  '-REMOTE_INSTANCE--instance-name-'
   '-SECURITY SSL---'

>--+---------------------+--+-------------------------------+--->
   '-SYSTEM--system-name-'  '-OSTYPE--operating-system-type-'

>--+------------------------+----------------------------------><
   '-WITH--"comment-string"-'

What is the difference with "hostname"? I always ignore this value, but I want to know its purpose. Is it just informative? where can I see the effect of including or ignoring it?


texts: How can I rebuild an index on table with 100 million rows without a wait time out?

texts:
I have the following table with ~100 million rows:

CREATE TABLE Result (
    MetricId int NOT NULL,
    PeriodId int NOT NULL,
    Value decimal (18, 2) NOT NULL,
    CONSTRAINT PK_Result PRIMARY KEY CLUSTERED (MetricId ASC,PeriodId ASC)
)

CREATE NONCLUSTERED INDEX IX_Result_PeriodId ON Result (PeriodId ASC)

My analysis of this situation (and I'm by no means a DBA) is:

The NC index should have Value as an included column. We are
seeing queries that filter by PeriodId use the NC index but then
need a Key Lookup on the PK to get the value. I think this will give
us much better query performance.
The NC Index does not need MetricId in the key after PeriodId
because SQL Server implicitly includes any columns in the clustered
PK index.
As an aside: the main filter we use in queries is PeriodId therefore it would
be best to have the PK around the other way (ie. PeriodId,MetricId).
I also believe PeriodId is more selective (?) in that it would
make sense to filter first by PeriodId to get rid of the most
rows during a query.

My question is then - how can I create or rebuild the following index?

CREATE NONCLUSTERED INDEX IX_Result_PeriodId ON Result (PeriodId ASC) INCLUDE (Value)

I have tried editing the existing index through Management Studio which requires a rebuild, and that times out after 5-10 minutes. It's a 'Wait Timeout' I believe.

I have tried creating a brand new index through Management Studio with the same result.

I have tried putting the database in Single User mode and creating the new index. Same result.

Upgrading to Enterprise is not an option for me.

How can I create this index on such a large table?


texts: What would be an appropriate schema for this real life scenario?

texts:
A Company C provides an enterprise product to organisations (O1, O2, O3... On) and their sub-organisations (SO1 ... SOn). The product offers the organisations a database of content which is created by C. This content is accessible by all organisations O1 - On and SO1 - SOn.

At the same time an organisation O1 can create their own content that is not accessible by O2 - On.

These organisations, each have multiple sub organisations (SO1 - SOn). Content created by O1 is accessible to all suborganisations SO1 - SOn but content created by SO1 is only accessible to O1 and not accessible to SO2 - SOn

I have attached a sample schema that I think might work.

Content created (either by organisations or company) is always represented by the same entity
Organisations can get their content specific content by org_id
Organisations can get the product content by root_org_id
The Company C is represented as root_org, which is an organisation itself but has some special system privileges.

What would be an appropriate schema design to cater for the scenario mentioned? My main issue is how can Company C be represented in the schema as it is an organisation for all intentions and purposes but it also has special system / product privileges which no other organisations have. For e.g. creation of organisations (O1 - On)

Example scenario - Company C has it's own administrative dashboard to

create content and make it available to all organisations (O1 - On)
administer organisations (O1 - On) i.e. CRUD organisations
provide support - sometimes login as the organisations (O1 - On) to troubleshoot and support


texts: Daily restore from sql server transaction log

texts: Our client will ship a database full dump on day 1 and daily transaction log from day 2. We will restore the full dump on day 1 and plan to restore the transaction log daily since day 2. Is it possible to just restore the daily transaction log? Because I read in order to restore a transaction log backup, the database has to be in a restoring state which means that a full backup or a full backup and differential backup were first restored using the NORECOVERY option to allow additional backups. Does it mean on everyday in order to restore the daily transaction log we have to first restore the full dump and leave the db in NORECOVERY mode and restore all the transaction logs before current day and leave the db in NORECOVERY mode?


texts: Issue replicating updates to primary key data with SymmetricDS

texts:
I am replicating from MSSQL (SQL Server 13) to PostgreSQL (9.5) using SymmetricDS.

The table that is replicating has a composite key of 7 different columns. Everything works perfect from an initial load to inserting and updating data.
However, I run into a problem whenever I run an update that modifies data in one of the 7 columns that comprise the primary key. On the MSSQL side, it updates the row, no problem. On the Postgres side, rather than updating the column, it inserts an additional row.

If I modify the sym_transform_column entry to have 0 for pk the specific column then it will update the data correctly but will not utilize that column as a primary key to determine which row to update.

Example Generated SQL with pk=0 for sym_transform_column:

update table set pk1 = 0, value1 = 'test', value2 = 'test' where pk2 = 0 and pk3 = 0

Example Generated SQL with pk=1 for sym_transform_column:

update table set value1='test', value2='test' where pk1 = 0 and pk2 = 0 and pk3 = 0

I realize that it is generally accepted that PK should be immutable but to cover all contingencies, is there a way to replicate the update to primary key data from MSSQL to PostgreSQL using SymmetricDS?


texts:
I'm designing a Django Rest Framework API with PostgreSQL. In the database I'll be recording information about documents which can be requested for download. I want the request to include a unique id, so the server knows which document to retrieve. Every document can only be requested once by the one user who has its id value.
However, I read that using primary key values (in my case would be a UUID generated by Python) outside of the database is insecure.
The question
So, can I use a UUID as primary key in the database and let users request the document by this UUID or should I generate another id for the user to request the document by? Any help is appreciated!


texts: Undo Database Restore in SQL Server 2008

texts: I accidentally restored a SQL Server 2008 database with a wrong backup. Is there any way to undo this and restore to previous version?


texts:
In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres.

It looks like the names of such columns are contained in pg_constraint. According to the docs, the column relevant to my problem is called conkey, which happens to be an array of ints.

The point is, the query I've come up with gives me the wrong results, and I'm pretty sure it's because I'm joining on conkey in the wrong way. The query is as follows:

SELECT
    pg_attribute.attname,
    pg_constraint.*
FROM
    pg_attribute
INNER JOIN pg_constraint
        ON pg_attribute.attnum = ANY (pg_constraint.conkey)
WHERE pg_constraint.conrelid = (
    SELECT
        oid
    FROM
        pg_class
    WHERE
        relname LIKE 'test_table'
)
AND pg_constraint.contype = 'u'; -- to filter out non-unique constraints

Here's a table DDL for a quick repro:

CREATE TABLE "test_table" (
"date" date DEFAULT now() NOT NULL,
"foo" varchar COLLATE "default" NOT NULL
CONSTRAINT "test_table_foo_key" UNIQUE ("foo")
)
WITH (OIDS=FALSE);

ALTER TABLE "test_table" OWNER TO "postgres";

Now, can anyone tell me what I'm doing wrong? Is there an easier way to get the information I want altogether?

I'm trying to retrieve the names of constrained table because the app I'm writing needs to work around the current lack of UPSERT in Postgres. The idea is to do the insert-or-update with a CTE, but since I don't know what the constrained columns are beforehand, I need to do this bit of introspection.


texts: Using concat inside a stored procedure parameters

texts:
I have a stored procedure and function that accepts a varchar as a parameter and i have this code where i am using concat function in the parameters of such function and stored procedure.

DELIMITER //
CREATE PROCEDURE ja_logan_proc()
BEGIN

SELECT id, dest_msisdn, text_message INTO @the_id, @the_number, @the_message FROM incoming_sms where job_status = 0 AND text_message LIKE '%logan%' order by rand() limit 1;

if((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'true')then

call register_lookup_r4(CONCAT(''','@the_id','''),CONCAT(''','@the_number','''));

elseif((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'false')then

select 'nothing to do here';

end if;

END //
DELIMITER ;

This line is identified as the one with an error

call register_lookup_r4(CONCAT(''','@the_id','''),CONCAT(''','@the_number','''));

and this other line

if((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'true')then

What am i doing wrong in the parameters field?.


texts: SQL Reporting Services - T2Embed.dll missing

texts:
When generating a report in SSRS, I receive the following error:

There was an error generating your report. Please refer to item 673778174106859 for additional diagnostic information. There was an error during the render phase of the report generation. There was an error generating the document Network Statistics There was an error generating the document Network Statistics An error occurred during rendering of the report. --->

Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException: An error occurred during rendering of the report. --->

Microsoft.ReportingServices.OnDemandReportRendering.ReportRenderingException: An error occurred during rendering of the report. --->

System.DllNotFoundException: Unable to load DLL 'T2Embed': The specified module could not be found. (Exception from HRESULT: 0x8007007E) An error occurred during rendering of the report. --->

Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException: An error occurred during rendering of the report. --->

Microsoft.ReportingServices.OnDemandReportRendering.ReportRenderingException: An error occurred during rendering of the report. --->

System.DllNotFoundException: Unable to load DLL 'T2Embed': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

Running a repair install of the specific SQL and RS instance did not help.  Any suggestions?


texts:
I have four tables.

UserMaster
----------
UserKey - PK
UserName
UserID - UNIQUE

UserLogTransaction
------------------
UserID - FK:UserMaster.UserID
LoginTime
LogoutTime

CallCenter
----------
CallKey - PK
PickedUserKey - FK:UserMaster.UserKey
PickedTime
StartTime
EndTime

JobCardMaster
-------------
JCKey
RecordedUserKey - FK:UserMaster.UserKey
CreatedTime
CallKey - FK:CallCenter.CallKey

This is the scenario: Each user logs in and attends calls. Job cards are created for some of the calls not for all calls.

Problem: I want to get a report which has the following information

UserName | LoginTime | LogoutTime | NoOfCallsAnswered^ | NoOfJobCardsCreated^ | AverageCallDuration^

^ These columns are calculated per session per user. A session is a row from the UserLogTransaction table.

I tried with several attempts and they tend to take around 1 minute to run. So, I think there is some problem with my approachs.

SELECT UM.UserID, ULT.LogInTime, ULT.LogOutTime, COUNT(CC.PickedUserKey)
FROM dbo.UserMaster UM
JOIN dbo.UserLogTransactions ULT
 ON ULT.UserID = UM.UserID
JOIN dbo.CallCenter CC
 ON CC.PickedUserKey = UM.UserKey
JOIN dbo.JobCardMaster JC
 ON JC.RecordedUserKey = UM.UserKey
WHERE (CC.PickedTime > ULT.LogInTime
 AND CC.PickedTime < ULT.LogOutTime)
 OR (JC.RecordedUserKey > ULT.LogInTime
 AND JC.RecordedUserKey < ULT.LogOutTime)
GROUP BY CC.PickedUserKey, UM.UserID, ULT.LogInTime, ULT.LogOutTime

I am stuck on this query for several days. Appreciate any helps or hints.


texts:
On production server(linux machine mysql 5.6.17), I tried to find my.cnf to change the system variables, but my.cnf was not in /etc/my.cnf location. It was in usr/ directory.
Also that file contains only comments like -

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

Please tell me where should I copy my.cnf and how I can change variables like -

innodb_buffer_pool_size = 200M
max_connections = 300


texts: Latest of group in subquery is bottleneck, how to optimizing query?

texts:
See Original question

I'm fetching the activity data for the latest activity for each company as part of a bigger query.
I've been using the method provided by ypercube, and it works fine, but I find it too slow on larger results.

SELECT
d.*,
a.*
FROM difi d
LEFT JOIN (
  SELECT orgnr,
  MAX(resolved) as maxdate
  FROM activities
  WHERE resolved IS NOT NULL
  AND deleted IS NULL
  GROUP BY orgnr
) as x ON x.orgnr=d.orgnr

LEFT JOIN activities a ON a.deleted IS NULL
  AND a.orgnr=x.orgnr
  AND a.resolved=x.maxdate

This performs at 1.3 seconds with a LIMIT 500, and 2.4 seconds with a LIMIT 1000.
Can this query be optimized to run with thousands of results, faster?

I figure the bottleneck is the subquery, as it is running for each company(?).

Here's an explain with LIMIT 1000:

+----+-------------+------------+------+------------------------+---------+---------+---------+---------+----------------------------------------------+
| id | select_type | table      | type | possible_keys          | key     | key_len | ref     | rows    | Extra                                        |
+----+-------------+------------+------+------------------------+---------+---------+---------+---------+----------------------------------------------+
|  1 | PRIMARY     | d          | ALL  | NULL                   | NULL    | NULL    | NULL    | 1001146 |                                              |
|  1 | PRIMARY     | <derived2> | ALL  | NULL                   | NULL    | NULL    | NULL    |   33241 |                                              |
|  1 | PRIMARY     | a          | ref  | orgnr,resolved,deleted | orgnr   | 4       | x.orgnr |       1 |                                              |
|  2 | DERIVED     | activities | ref  | resolved,deleted       | deleted | 9       |         |   33166 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+------------------------+---------+---------+---------+---------+----------------------------------------------+
4 rows in set (0.30 sec)

Using MySQL 5.5.16

Update

I've been at this all weekend and I think I've found the solution (method from tosin here)

SELECT
*
FROM difi d

LEFT JOIN activities a ON a.id=
    (SELECT ax.id
     FROM activities ax
     WHERE ax.orgnr=d.orgnr
     AND ax.deleted IS NULL
     AND ax.resolved IS NOT NULL
     ORDER BY ax.resolved DESC
     LIMIT 1)

As far as I can tell, this gets me the latest activity for each company. And it performs at  1,001,146 results in 0.0025 sec.
Any caveat on this?


texts:
I Have configured the Database Mail in INSTANCE1 and INSTANCE2. Whenever i try to send a mail using the following code, i get output as "Mail queued" in INSTANCE1.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Vinesh',
@recipients = 'VineshSenthilvel@gmail.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message - Default' ;

But the mail is not delivered to my mailbox. And i tried executing the above query in INSTANCE2, the mail got delivered. I have verified that Database Mail configuration seems to be perfect in both the instances.

I can able to find the Database mail process is shutting down in the Database Mail Log. Also found an error mentioned below.

1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The Transaction not longer valid.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void ValidateConnectionAndTransaction()
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction()
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction()
   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec)
   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel, Byte[] encryptionKey, Int32 connectionTimeout)

Is there is any solution for this? Thanks in Advance.


texts: How can I resolve a database trigger's name with built-in functions?

texts:
I have a database trigger that I use to prevent me from creating certain procedures in user databases.

It appears in sys.triggers, with an object_id, but I can't use the object_id function to find it.

SELECT OBJECT_ID(t.name, t.type) AS object_id, *
FROM   sys.triggers AS t;

Likewise, I can find it in sys.dm_exec_trigger_stats. I can't get object_name to resolve, but object_definition does.

SELECT OBJECT_NAME(dets.object_id, dets.database_id) AS object_name,
       OBJECT_DEFINITION(dets.object_id) AS object_definition,
       *
FROM   sys.dm_exec_trigger_stats AS dets;

Is there a function that will accept a database level trigger's object id, and return its name?


texts: How To Import Binary Dump File To SQL Server

texts:
I am trying to import a dump file which is in binary format to SQL Server using sqlcmd command. After running the import command, I am getting this error, Msg 2812, Level 16, State 62 I'm suspecting that it is because the file is in binary format. Is there a way to directly import binary dump file to SQL Server? I tried using the -u flag but I'm getting the same error.

Sorry, forgot to mention, I am using SQL Server 2017 on Ubuntu 16.04

Command: sqlcmd -S localhost -d TestDB -U SA -u -i /home/user1/Downloads/myspecialpresentforyou

Error Message: Msg 2812, Level 16, State 62, Server user1, Line 1
Could not find stored procedure 'TAPE'.


texts: DBCC SHRINKFILE - Necessary to run more than once to achieve shrink?

texts:
We have a 2008R2 production DB in full recovery mode.

To produce a minimised dev version, for SQL Server 2014, the following steps are run:

RESTORE DATABASE BigDB FROM  DISK = N'C:\Share\BigDB.bak' WITH  FILE = 1,
MOVE N'BigDB_Data' TO N'C:\Database\BigDB_Data',
MOVE N'BigDB_Audit]' TO N'C:\Database\BigDB_Audit',
MOVE N'BigDB_Log' TO N'C:\Database\BigDB_log.LDF',  NOUNLOAD,  REPLACE,  STATS = 5

alter database BigDB set recovery simple with NO_WAIT
alter database BigDB set compatibility_level = 120

go

-- {truncate / delete statements.. with no additional 'go'}

DBCC SHRINKFILE (N'[BigDB_Audit]' , 500)
DBCC SHRINKFILE (N'BigDB_Data' , 1000)
DBCC SHRINKFILE (N'BigDB_Log' , 1000)
DBCC SHRINKFILE (N'BigDB_Data' , 1000) -- Without this repeat, file remains same
DBCC SHRINKFILE (N'BigDB_Log' , 1000) -- Without this repeat, file remains same

I've observed that without the repeat of the shrinkfile on the main data file, and the log, one or both does not actually shrink.

Can this behaviour be expected, and if so why?


texts: using mongorestore for a db present in my computer

texts:
I just started using mongodb so my question might be a little basic.
I have a folder with the collections I want to restore(this was a zip file I downloaded from my email).
How do I use mongorestore to restore this db into my local db?


texts: Revoke write privileges vs setting database to readonly

texts:
I am planning to do a database migration to a new server. During the transition stage, I do not want Apache user to be able to write anything to the database. I have two options.

Revoke write privileges:

REVOKE INSERT, UPDATE ON `mydb`.* FROM 'apache'@'localhost';
FLUSH PRIVILEGES;

Set database to read-only mode:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;

Are there any practical considerations for choosing one over the other?


texts: MySQL: Very slow query, not using index, no joins involved

texts:
I am running what I think is a very simple query:

SELECT SQL_NO_CACHE m.mbr_nbr
FROM tran.member m
WHERE m.cardnbr = 000099999930

The table member is a poorly written one, with 133 columns and there are about 2 million rows. The query is being run in a program to gather the mbr_nbr for 20,000 card numbers. So, the query is being run 20,000 times. Is there a better way to handle this or should I try to optimize the query? Maybe by creating an index on the two fields I need?

Rebuilding a better database isn't an option.

Neither field is the primary key.


texts: MySQL How to get first row from subquery when using aggregate function

texts:
This is my Query:

SELECT
    COUNT(runs_scored) - SUM(IF(extra_type_id = 4, 1, 0)) AS Balls,
    SUM(runs_scored) + SUM(IF(extra_type_id IN (2 , 4),
        extra_runs,
        0)) AS Runs,
    SUM(IF(kind_out IN (1 , 2, 4, 6, 7, 8),
        1,
        0)) AS Wickets
FROM
    ball_by_ball BBB
        LEFT JOIN
    `database`.batsman_scored BS USING (match_id , over_id , ball_id , innings_no)
        LEFT JOIN
    `database`.extra_runs ER USING (match_id , over_id , ball_id , innings_no)
        LEFT JOIN
    `database`.wicket_taken WT USING (match_id , over_id , ball_id , innings_no)
WHERE
    bowler = 70
GROUP BY match_id
ORDER BY Wickets DESC , Runs;

This is Output:

+-------+------+---------+
| Balls | Runs | Wickets |
+-------+------+---------+
|    24 |   28 |       3 |
|    24 |   23 |       2 |
|    24 |   32 |       2 |
|    24 |   23 |       1 |
|    18 |   27 |       1 |
|    12 |   10 |       0 |
|    12 |   17 |       0 |
|     6 |   18 |       0 |
|    18 |   19 |       0 |
|    18 |   28 |       0 |
+-------+------+---------+

This is my Final Query:

SELECT
    SUM(Balls) AS Balls,
    SUM(Runs) AS Runs,
    SUM(Wickets) AS Wickets
FROM
    (SELECT
        COUNT(runs_scored) - SUM(IF(extra_type_id = 4, 1, 0)) AS Balls,
            SUM(runs_scored) + SUM(IF(extra_type_id IN (2 , 4), extra_runs, 0)) AS Runs,
            SUM(IF(kind_out IN (1 , 2, 4, 6, 7, 8), 1, 0)) AS Wickets
    FROM
        ball_by_ball BBB
    LEFT JOIN `database`.batsman_scored BS USING (match_id , over_id , ball_id , innings_no)
    LEFT JOIN `database`.extra_runs ER USING (match_id , over_id , ball_id , innings_no)
    LEFT JOIN `database`.wicket_taken WT USING (match_id , over_id , ball_id , innings_no)
    WHERE
        bowler = 70
    GROUP BY match_id
    ORDER BY Wickets DESC , Runs) AS A;

This is Output:

+-------+------+---------+
| Balls | Runs | Wickets |
+-------+------+---------+
|   180 |  225 |       9 |
+-------+------+---------+

But I want to get the Maximum Wickets and Runs like below:

+-------+------+---------+---------+
| Balls | Runs | Wickets | Max(W/R)|
+-------+------+---------+---------+
|   180 |  225 |       9 |    3/28 |
+-------+------+---------+---------+

  Here 3 are Wickets and 28 are Runs which I got from Subquery first Row. Now how can i do that from my Final Query? I need to get Subquery first Row values which already sorted according to my requirement. I tried WINDOWS FUNCTION that not working.


texts:
Can I secure SQL Server symmetric key from [sa] account? Can I create symmetric key using local user certificate via MMC?

Thanks


texts: If my understanding of BCNF is correct, how did we know that R1 was not in BCNF?

texts:
Given that R(ABCDE)

  AB->C

  BC->D

  CD-> E

  DE->A

Since, B is the only attribute absent on the right-hand side, we would start with B.

{B}+ = B . Not a CK.

Next, we combine different attribute with B.

{AB}+ = AB = ABC = ABCD = ABCDE. This is a CK.

{BC}+ = BC = BCD = BCDE = BCDEA. This is a CK.

{BD}+ = BD . Not a CK.

{BE}+ = BE . Not a CK.

{BDE}+ = BDE = BDEA = BDEAC. This is a CK.

So, total number of CK/minimal keys are 03 (three).

Since, in some FDs, attributes are not dependent on keys, R is not in BCNF.

(1) Finding the violating FDs:

CD -> E and DE -> A are the violations of BCNF. Coz, in these FDs, attributes are nor dependent on keys.

(3) Decomposition:

Start with any one of the invalid FDs:CD -> E .

(i) Find the closure of CD and decompose:

R1 = {CD}+ = CD = CDE = CDEA = ACDE

R2 = (R - R1) + {CD} = (ABCDE - ACDE) + CD = BCD

Now, we will have to check to see whether both R1 and R2 in BCNF.

  R1(ACDE), R2(BCD)

  AB->C

  BC->D

  CD-> E

  DE->A

I have a question, how did we know that R1 was not in BCNF and needs to be decomposed?

Since, CD->E is already applied in a decomposition, we don't need to use that again. Coz, it will produce the same result.

(1) Finding the violating FDs:

DE->A is violating the BCNF and it is also not applied yet.

(2) Decomposision:

Start with DE->A.

(i) Find the closure of DE and decompose:

R3 = {DE}+ = DEA = ADE.

R4 = (R1 - R3) + {DE} = (ACDE - ADE) + {DE} = CDE.

So, our final BCNF decompositions are,

R2{BCD},
R3{ADE},
R4{CDE}.


texts: Does SQL Server Cache Aggregate Results When Duplicated Across Columns?

texts:
Suppose we have a table Orders containing the columns order_id, total, discount

Then we write a query similar to the following

SELECT
    COUNT(order_id) AS num_orders
    , SUM(total) / COUNT(order_id) as avg_total
    , SUM(discount) / COUNT(order_id) AS avg_discount
FROM Orders

Is the value for COUNT(order_id) Preserved across columns or re-computed (Eg, is there a performance hit)? Or is it better to determine the computed value(s) first and used those in the query, for example:

DECLARE @order_count AS INT

SELECT
    @order_count = COUNT(order_id)
FROM Orders

SELECT
    @order_count AS num_orders
    , SUM(total) / @order_count as avg_total
    , SUM(discount) / @order_count AS avg_discount
FROM Orders

Note that, while writing this question I noticed that since SQL Server 2008 AVG() is supported. However, I continued this question and intend this to be more general to wanting to understand how SQL server handles Identical aggregates across columns as I do sometimes run into this in other forms.


texts: Percona XtraDB Cluster: How to skip SST when starting?

texts:
Is there a way to skip State Snapshot Transfer (SST) at startup on Percona XtraDB Cluster Server?

wsrep_sst_mode=skip doesn't work:

121003  6:49:55 Percona XtraDB (http://www.percona.com) 1.1.8-rel28.1 started;
    log sequence number 82208067084
121003  6:49:55 [ERROR] /usr/sbin/mysqld: unknown variable 'wsrep_sst_mode=skip'
121003  6:49:55 [ERROR] Aborting

I'm using Percona-XtraDB-Cluster-server-5.5.27-23.6.356.rhel5.


texts:
While I've spent many years as a SQL developer, I'm not really a DBA, but I'm the closest thing that my company has to one. We have recently started moving many of our PostgreSQL databases from Heroku to our own Digital Ocean servers. One of the differences that I recently noticed was that all of the Heroku datbases' timezone setting are configured to 'UTC', and all of our Digital Ocean databases are set to 'localtime'.

What is best practice here? I've done some 'Googling', but can't really find a clear cut answer.  Is there an advantage to configuring them all to be UTC?

We are using PostgreSQL 9.4 on Ubunut 14.04 servers.


texts:
i have one table for all person (with fields ex. name, last name) because each person can has one or more role (customer, lead/prospect, supplier, employee).
And i have tables (customers, leads, suplliers, employees) for each role with custom fields.
Also I have a roles table and a table for person and his roles.

Questions:

Is correct save leads/prospects in the same person table? Because i can get thousands of leads. But this can down sql query performance?

Other question, some person (customer and employee) can be a user (can be login). Then i create other table named users, but the email is saved in person table. I can use a username for login but i want too the users can login with email.

What do i?
I use only use username (in user table), other option copy email (from person table) to user table or for last option a query using the two tables: person and user?

Thanks


texts:
I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005.

my input Data

  ONAY_ID  sip_RECno    KULLANICI       ONAY
   1           1         user1          true
   2           1         user2          true
   3           1         user3          false
   4           1         user4          true

Output would be

 sip_RECno  user1     user2    user3     user4

  1          true     true    false    true


texts: MariaDB/MySQL: Dump current transaction data

texts:
Suppose, I have a transaction that contains some delete, insert and update queries with a lot of select-joins and values inferred from other tables. Sometimes even outright math inside queries. This makes forensics very hard, but reengineering the queries isn't going to happen any time soon.
How can I, within the transaction, see (or, preferably, dump) the actual data (in tables and rows) that is being changed within the transaction before committing it?
In short, I want a transaction delta.
I use MariaDB 10.3.23


texts: Is there a better way to summarize a table containing events of different types by identifier?

texts:
I have this table definition for sensor events:
                      Table "public.sen_sensor_event"
   Column    |            Type             | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
 dtype       | character varying(31)       |           | not null |
 id          | bigint                      |           | not null |
 created     | timestamp without time zone |           |          |
 type        | character varying(255)      |           | not null |
 temperature | double precision            |           |          |
 sensor_id   | bigint                      |           | not null |
 updated     | timestamp without time zone |           |          |

I'm looking to get a summary for each sensor for each event type. Currently I'm doing it via this materialized view which gives the created timestamp of most recent event for each type (mapped to an enum in code) and a total event count.
CREATE MATERIALIZED VIEW public.sen_sensor_summary AS
 SELECT x.belongs_to,
    x.sensor_id,
    x.name,
    y.event_count,
    y.temperature,
    y.temperature_created,
    y.human_motion_created,
    y.presence_created,
    y.heartbeat_created,
    y.startup_created
   FROM (public.sen_sensor x
     FULL JOIN ( SELECT COALESCE(z.sensor_id, a.sensor_id, b.sensor_id, c.sensor_id, d.sensor_id, e.sensor_id) AS sensor_id,
            z.event_count,
            a.temperature,
            a.created AS temperature_created,
            b.created AS human_motion_created,
            c.created AS presence_created,
            d.created AS heartbeat_created,
            e.created AS startup_created
           FROM (((((( SELECT DISTINCT ON (z_1.sensor_id) z_1.sensor_id,
                    count(z_1.sensor_id) AS event_count
                   FROM public.sen_sensor_event z_1
                  GROUP BY z_1.sensor_id
                  ORDER BY z_1.sensor_id) z
             FULL JOIN ( SELECT DISTINCT ON (a_1.sensor_id) a_1.sensor_id,
                    a_1.created,
                    a_1.temperature
                   FROM public.sen_sensor_event a_1
                  WHERE ((a_1.type)::text = 'TEMPERATURE'::text)
                  ORDER BY a_1.sensor_id, a_1.created DESC) a ON ((z.sensor_id = a.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (b_1.sensor_id) b_1.sensor_id,
                    b_1.created
                   FROM public.sen_sensor_event b_1
                  WHERE ((b_1.type)::text = 'HUMAN_MOTION'::text)
                  ORDER BY b_1.sensor_id, b_1.created DESC) b ON ((z.sensor_id = b.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (c_1.sensor_id) c_1.sensor_id,
                    c_1.created
                   FROM public.sen_sensor_event c_1
                  WHERE ((c_1.type)::text = 'PRESENCE'::text)
                  ORDER BY c_1.sensor_id, c_1.created DESC) c ON ((z.sensor_id = c.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (d_1.sensor_id) d_1.sensor_id,
                    d_1.created
                   FROM public.sen_sensor_event d_1
                  WHERE ((d_1.type)::text = 'HEARTBEAT'::text)
                  ORDER BY d_1.sensor_id, d_1.created DESC) d ON ((z.sensor_id = d.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (e_1.sensor_id) e_1.sensor_id,
                    e_1.created
                   FROM public.sen_sensor_event e_1
                  WHERE ((e_1.type)::text = 'STARTUP'::text)
                  ORDER BY e_1.sensor_id, e_1.created DESC) e ON ((z.sensor_id = e.sensor_id)))
          ORDER BY COALESCE(z.sensor_id, a.sensor_id, b.sensor_id, c.sensor_id, d.sensor_id, e.sensor_id)) y ON ((x.id = y.sensor_id)))
  ORDER BY x.name
  WITH NO DATA;

I like the resulting view, which I map to a SensorSummary object in Java via Hibernate. However, for each new event type I add I need to add a new FULL JOIN section to the view definition.
Is there a more dynamic way to define the materialized view so that when a new type value appears it is automatically added to the view?


texts:
I am using VS2012 and creating a package on a 64bit machine to import some data from a .xlsx file.  My question is that I am getting an error for the Excel connection manager, do I need to install some kind of excel drive or excel itself on the machine in order to be able to import the data?

The error is Could not retrieve the table information for the connection manager 'Excel Connection Manager'.  Failed to connect to the source using the connection manager.

Thanks


texts: postGIS schema qualifier - best practice

texts:
There are a number of cases where the EXTENSION for postGIS is created iwth a schema qualifier.

CREATE SCHEMA postgis;
CREATE EXTENSION PostGIS WITH SCHEMA postgis;

why is that?  what are the tradeoffs in using the schema?


texts:
I need to modify a procedure to make statistics on my database.

I got a temporary table which contains all the data that I need to make those statistics.
It contains all the relevant actions FormIdNb of the working units NumCavalier in one day :

-- Every action from all units will be stored in this temp table
-- DateHeure = Time of action
-- NumCavalier = ID of working unit
-- FormIdNb = Action type
CREATE TABLE #tmp_DailyMissions (
DateHeure datetime,
NumCavalier  nchar(3),
FormIdNb int
)

INSERT INTO #tmp_DailyMissions
SELECT        DateHeure, Cavalier.NumCavalier, FormIdNb
FROM            ECN4, Cavalier
WHERE        (DateHeure BETWEEN @StartTime AND @EndTime) AND (Cavalier.Terminal = @terminal) AND (Cavalier.NumCavalier = ECN4.NumCavalier)

This temporary table will store all actions that all the working units made in one entire day (just as a logging system).
The significant values for FormIdNb are :

  FormIdNb > 3 ==> The working unit was active
  FormIdNb = 3 ==> The working unit was idle
  FormIdNb < 3 ==> The working unit was inactive

The request that I got right now (Thanks to @AndriyM), returns the number of working units who were ACTIVE, IDLE and INACTIVE on each interval of time of the day (15min) :

WITH time_cte(StartTime, EndTime) AS
  (
    SELECT
      StartTime = @starttime,
      EndTime   = DATEADD(mi, 15, @starttime)

    UNION ALL

    SELECT
      EndTime,
      DATEADD(mi, 15, EndTime)
    FROM
      time_cte
    WHERE
      EndTime < @EndTime
  )
SELECT
  t.StartTime,
  ActiveUnitCount   = COUNT(DISTINCT(CASE WHEN d.FormIdNb > 3 THEN d.NumCavalier END)),
  IdleUnitCount     = COUNT(DISTINCT(CASE WHEN d.FormIdNb = 3 THEN d.NumCavalier END)),
  InactiveUnitCount = COUNT(DISTINCT(CASE WHEN d.FormIdNb < 3 THEN d.NumCavalier END))
FROM
  time_cte AS t
  LEFT OUTER JOIN #tmp_DailyMissions AS d
    ON d.DateHeure >= t.StartTime
    AND d.DateHeure < t.EndTime
GROUP BY
  t.StartTime
;

The problem is, as I said above, that the temporary table I use store the data as a logging system.
This means that it can contain, for a working unit on a same interval of time, any value of FormIdNb.
For example : 1 row FormIdNb = 2, 1 row FormIdNb = 3 and 1 row FormIdNb = 4.

My question is :
How to make a request who get rid of irrelevant values ?

More precisely :
If a working unit was ACTIVE, but was also IDLE and/or INACTIVE on an interval of time, how to "remove" this working unit from the IDLE and INACTIVE counts, and only keep the maximum value of FormIdNb for each intervals of time ?


texts: Is it possible to auto generate ALTER scripts for differences between existing DB and updated schema?

texts:
I built a internal web application for a client that manages applications to programmes for their employees.

The system is Classic ASP on a set of clustered and load balanced Windows 2000 Servers using MS SQL 2000 as their database.

All is fine and I've been asked to do some additions for Phase 2. The additions require changes the tables, some stored procedures and possibly the addition or change of some basic Insert triggers.

Now my question was:

If I make changes to the database is there anyway I can generate a script that will create ALTER statements for them to run against their live databases ? Reason I need this is because I do not have access to the internal live system for security reasons (I am contracted not within their IT department).

I work on my local development database using SQL Server Enterprise manager (2000) which runs within Oracles  Virtual Box running Windows Server 2000 Advance (Version 5).

I will have to provide them with the modified web files and a set of scripts to alter the schema.

I have to assume that they will run the scripts using old software (Minimum circa 2000) but possibly will use newer versions.

Many thanks in advance.
John


texts: How to join to a single list of IDs from two different tables

texts:
I have worked with T-SQL in multiple DBs, but I have never designed a database before. I am designing a new database in MySQL which will track humans and organizations, including financial transactions received from them. I would like to easily be able to treat them as one group for the purpose of joining to other tables, such as tables tracking charges applied to their balances, payments made by them, and so on.

I have seen other databases track both humans and organizations in a single table, but since they are so unlike, this always seemed sloppy to me. For example, a human should have a last name that is NOT NULL, but an organization has no last name, so you end up needing a column just to track whether it's a human or an organization and then code to enforce rules based on that column's value. Querying from such tables also requires more code.

I would like to create a human table with the PK being human.id_number and an organization table with the PK being organization.id_number. I want to then somehow funnel all of the id_numbers into a third table, let's just call it constituent for this question. I then want to join other tables in the DB to constituent, not to human or organization. For example, some constituent primary key, maybe constituent.id_number, would join to payment.constituent_id_number.

The problem is that I'm not sure how to funnel human.id_number and organization.id_number into a single table. I have two questions:

Is my method a good choice? Is there a better option I should use?
How can I accomplish what I've described above?


texts:
Currently we have an Amazon RDS Oracle 12c instance and we need to upgrade it to Oracle 18c in a new RDS instance.

What is the correct approach to do this?

Can we set up an 18c instance staright away and then restore a 12c snapshot or do we need to Set up a new 12c instance and restore a snaphot and then upgrade to 18c?
Can we use AWS DMS to migrate from RDS 12c to RDS 18c?


texts: Finding dependencies on a SPECIFIC COLUMN (modern way, without using sysdepends)

texts:
I need to find all views & stored procedures that consume not just a certain table, but a specific column in a table.

The following "seems" to work, but there are numerous warnings to be careful with this method (not reliable for a variety of reasons, soon to be deprecated, etc):

SELECT object_name(so.id) TableName, sc.name ColumnName, OBJECT_NAME(sd.id) DependentObjectName,
(SELECT xtype FROM sysobjects so WHERE so.id = sd.id) Object_Type
FROM sysobjects so INNER JOIN syscolumns sc
ON so.id = sc.id
INNER JOIN sysdepends sd
ON so.id = sd.depid and sc.colid = sd.depnumber
WHERE
    object_name(so.id) = 'MyTableName'
AND sc.name = 'MyColumnName'
order by object_name(so.id), Object_Type

Some alternative approaches that are often referenced are sys.sql_dependencies and sys.sql_expression_dependencies, but neither of these have column level granularity.

Does anyone know a way of doing this?  (Or even if you know definitively that it literally cannot be done, it would be helpful to know.)


texts: Help with Table Performance & Nightly Maintenance Job

texts:
I'm running into a strange issue where a table's (~14 mil records) performance is degraded at various periods of time. It appears to be one problematic query, however once I optimize the indexes and update statistics, the table's performance increases dramatically. I've added WITH (NO LOCK) to the problematic query. I also optimize the indexes recommended here - https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html through 8 steps of IndexOptimize (with fragmentation levels in increments of 10) and then I update statistics on all of the tables in our database.

Example: 80% - 90% fragmentation optimization

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 80,
    @FragmentationLevel2 = 90,
    @MaxDOP = 0,
    @SortinTempDB = 'Y',
    @LogToTable = 'Y'

Example of updating statistics:

EXEC sp_MSforeachtable'PRINT ''?''; UPDATE STATISTICS ? WITH FULLSCAN'

Does anyone see anything blatantly wrong with these steps? I wonder if our statistics are getting out of date and not automatically updating. I am stumped.


texts: How to enable INMEMORY JOIN GROUP in Oracle 12.2

texts:
I have read official Oracle documentation on INMEMORY JOIN GROUP and run code from their tutorials. However I unable to successfully create INMEMORY JOIN GROUP.

Installed database is Oracle 12.2
INMEMORY TABLE and INMEMORY COLUMN were created successfully.

Exception message of INMEMORY JOIN GROUP creation below:
Error starting at line : 1 in command -
CREATE INMEMORY JOIN GROUP employees_departments
    (employees(department_id), departments())
Error report -
ORA-00900: invalid SQL statement
00900. 00000 -  "invalid SQL statement"
*Cause:
*Action:

My code is
CREATE INMEMORY JOIN GROUP employees_departments
    (employees(department_id), departments());

Are there any Oracle initialize parameters that should be specified in order to turn on support of INMEMORY JOIN GROUP?
Oracle SQL Developer highlights INMEMORY as incorrect syntax:


texts: Unknown column in 'where clause' when using an alias for a subquery in MySQL

texts:
This simple query:
select exam.id as exam_id,
(select count(*) from dataset where dataset.id = exam.id) as n_dataset
from exam
where n_dataset = 0

returns the following error:
ERROR 1054 (42S22) at line 1: Unknown column 'n_dataset' in 'where clause'

Why?


texts: Can SQL Server 2000 and SQL Server 2012 CoExist?

texts: We have OLTP Databases on SQL Server 2000 on my client's Prod Server. Planning to create Data Warehouse on the same Server but use SQL Server 2012. Can I install without any issues?


texts: How do I analyse a set of SQL Profiler-generated rolling trace files?

texts:
I am trying to diagnose an issue with a production SQL Server instance. I have asked the DBA to provide a trace and have been given a set of SQL trace files (1185 files to be precise).

When I open the first file, SQL Profiler detects presence of additional trace files and prompts me to load the one by one - this approach does not scale to 1185 files.

Ultimately, what I want is to import all that trace data into SQL server so I can query it.

Can you recommend an approach or tool that would allow me to do this?


texts: Select records where difference between datetime column1 and column 2 is greater than X amount of time

texts:
As the title indicates, I'm trying to return records where the datetime difference between two datetime columns is greater than X amount of time. I'm having trouble wrapping my head around this concept. I know how to do where column1 is greater than column2, but not calculate the difference between columns and then return results that are greater than or less than a specific amount.

I have built the schema here: http://sqlfiddle.com/#!9/1effe

Can I get some help on this? Basically I'm trying to get records from this table where the difference between publish_date and submission_date is greater than X amount of time, like 1 hour for example. Can I get a hand with this please? I don't even know where to begin.

I'm thinking this is what I want but I'm not sure

SELECT
  *
FROM
  test_table
WHERE
  timestampdiff(hour, publish_date, submission_date) > 1


texts: Upgrade SQL Server 2016 R Service (in-database)

texts:
I am just wondering whether this is doable. I have installed R service (in-database) together with SQL Server 2016 (patched at SP1+ CU1).

I notice R service version is at 3.2.2, which you can run the following script to check

declare @v varchar(100);
exec sp_execute_external_script @language=N'R'
, @script = N'v <- toString(getRversion())'
, @params = N'@v varchar(100) output'
, @v = @v out;

print @v;

-- returns 3.2.2

But I also installed Microsoft R client, and notice its R service engine is versioned at: 3.3.2.

So my question is "does SQL Server R service (in-database) now support R version 3.3.2 ?" if so, how can I upgrade it? if not, I guess I will wait until MS ships the update.

I read MSDN, and in it, it mentions using sqlBindR.exe to do the update, but this tool is available only with Windows R server, which I did not install and I even doubt that if I installed it, whether it would update the in-database R service.


texts: Postgresql server appears not to be running after install

texts:
I installed ubuntu 12.6 on Ubuntu 20.10. I had a postgresql version 10 once running on my system. I purged it before installation with sudo apt --purge remove postgresql postgresql-contrib. The package list shows:
$ dpkg -l | grep postgresql
ii  libreoffice-sdbc-postgresql                                 1:7.0.3-0ubuntu0.20.10.1            amd64        PostgreSQL SDBC driver for LibreOffice
ii  pgdg-keyring                                                2018.2                              all          keyring for apt.postgresql.org
ii  postgresql                                                  12+216                              all          object-relational SQL database (supported version)
ii  postgresql-12                                               12.6-0ubuntu0.20.10.1               amd64        object-relational SQL database, version 12 server
ii  postgresql-12-postgis-3                                     3.0.2+dfsg-2ubuntu2                 amd64        Geographic objects support for PostgreSQL 12
ii  postgresql-12-postgis-3-scripts                             3.0.2+dfsg-2ubuntu2                 all          Geographic objects support for PostgreSQL 12 -- SQL scripts
ii  postgresql-client-12                                        12.6-0ubuntu0.20.10.1               amd64        front-end programs for PostgreSQL 12
ii  postgresql-client-common                                    216                                 all          manager for multiple PostgreSQL client versions
ii  postgresql-common                                           216                                 all          PostgreSQL database-cluster manager
ii  postgresql-contrib                                          12+216                              all          additional facilities for PostgreSQL (supported version)

When I try to run sudo -i -u postgres psql I get:
psql: error: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

nmap localhost confirmed that port 5432 was not listened at. It is explicitly open for in and out in the firewall.
ystemctl status:
$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2021-03-26 11:49:43 CET; 2s ago
    Process: 21416 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 21416 (code=exited, status=0/SUCCESS)

Mar 26 11:49:43 joosken systemd[1]: Starting PostgreSQL RDBMS...
Mar 26 11:49:43 joosken systemd[1]: Finished PostgreSQL RDBMS.

This seems that everything is ok, but it seems to finish immediately. I don't understand why.
Looking for logs in /var/log/postgresql. That is weird: i only find logs from postgresql-10 and they are from months ago. pg_lsclusters does not show any cluster.
In short: my postgres installation appears to be ok and a mess. What can I do to rectify this?


texts: How do the Oracle DB instance and the local listener communicate?

texts:
It seems this my other question needs some background:

Basically, on Windows, how do the Oracle DB instance process and the listener process communicate? Do they use TCP? Is the configuration of this process done via listener.ora or via some other setting in the DB? Does the listener service "contact" the DB service or the other way round?

A rough sketch and/or very specific pointers to Oracle documentation would be really helpful.


texts:
I'm setting up a new SQL Server 2008 r2 in AWS with IO1 SSD EBS volumes.  My smaller EBS volumes are aligned correctly when they were formatted but the larger volumes, 3TB, are not because they used GPT instead of MBR.

I'm going though the setup check list in Brent Ozar's First Responder Kit and referencing another post that is asking generally about SSDs as well a document from AWS to ensure I have it optimized however I'm not finding anything of relevance when it comes to disk partition alignments for GPT or if it's necessary to do for IO1 SSD EBS Volumes in AWS.

Is partition alignment needed when it comes to AWS' SSD EBS volumes?
Is there a best practice for what the alignment should be for GPT formatted volumes?


texts:
How to find out when a query was executed? I ran a query couple of days ago, I'm not sure about the exact time. I want to know at what date and time the query was executed.
Is it possible to get that information?
How to check the server status when a query is running? What information should be checked?

version -oracle 11g


texts: How to troubleshoot COPY FROM inserting less rows then expected?

texts:
I have a CSV (tab-delimited in fact) file with 7,590,051 lines, according to wc -l, which I want to import using COPY FROM. I removed the table's primary key, so it has no constraints.

I ran COPY customer FROM '.../customer.dsv' WITH DELIMITER E'\t' CSV HEADER; and it reported 7,588,671 rows imported and no errors, so it's missing 1,379 rows (already discounting the header line).

Since PostgreSQL didn't report any errors, how do you suggest I troubleshoot which rows are missing (and why they are missing)?


texts: separate data from one column to 2 columns

texts:
I have three columns viz Name, Value,  Valueid.

I need to split the contents of value into two columns Valueof1 and Valueof2 based on the remaining two columns. Valueid has only two values either 1 or 2.

╔═══════╦═══════╦═════════╗
║ Name  ║ Value ║ Valueid ║
╠═══════╬═══════╬═════════╣
║ test1 ║   132 ║       1 ║
║ test2 ║   322 ║       2 ║
║ test3 ║   342 ║       1 ║
║ test1 ║   433 ║       2 ║
║ test2 ║   123 ║       1 ║
║ test3 ║   244 ║       2 ║
╚═══════╩═══════╩═════════╝

I'd like to see that data displayed like this:

╔═══════╦═══════╦═════════╦══════════╦══════════╗
║ Name  ║ Value ║ Valueid ║ Valueof1 ║ Valueof2 ║
╠═══════╬═══════╬═════════╬══════════╬══════════╣
║ test1 ║   132 ║       1 ║      132 ║      433 ║
║ test2 ║   322 ║       2 ║      123 ║      322 ║
║ test3 ║   342 ║       1 ║      342 ║      244 ║
║ test1 ║   433 ║       2 ║      132 ║      433 ║
║ test2 ║   123 ║       1 ║      123 ║      322 ║
║ test3 ║   244 ║       2 ║      342 ║      244 ║
╚═══════╩═══════╩═════════╩══════════╩══════════╝


texts:
I am transfering a managment application from Access to SQL Server and from vba to .net. Still in study and learning phase. I am wondering if one of the solutions I developped previously can not be improved using a more normalised technique but could not find out if and how to improve.

I have a unique centralised documents table (and conected form) containing a row for every document (word, pdf or image) refered to by the application with besides document specific data it contains two columns referring to the application's element linked to that document = where it was added like Bank statement, invoice, budget, relation, contact, Project, Investment, Account with as purpose allowing a shortcut to the origin of the document i.e. enabling to open directly the form from where the document was originally added to the database.

I did it dedicating two columns one (a one char FK) "Origin" refers to the PK of a table containing the data to access the table or open the form where the document was originaly created the other "OriginID" contains the Origin table Primary Key (they can all be refered to through a unique integer clustered primary key).

That way of working although efficient does not comply with normalisation but I did not find a way to normalise those relations as there are 9 entities that can create a document record.

Now that I am reviewing and rewriting the application for SQL server I wonder if there could be a better way to handle those relations.


texts: SQL Server Management Studio slow opening new windows

texts:
After upgrading both SQL Server and Management Studio from 2008 to 2012, Management Studio 2012 is very slow when new query windows and dialog boxes are opened.

Even right clicking on tables is slow. There is typically at least a 5 second delay before I can start doing anything within a window. This happens every time, even if I open the same window twice in a row. Why does this happen and how can I fix it?

Other applications establish connections to the database very fast.

Things I have tried that did not help:

A hit on Google where I'd need to modify my hosts file
Resetting SSMS "user-defined settings"
Updating video drivers, turning off hardware acceleration, disabling DirectX
Disabling the Biometric Authentication Service (I don't have it installed).

My computer should be more than fast enough, and I also have 16GB RAM. My hardware should definitely not be a problem. It looks like SSMS is waiting for something - I can operate other programs smoothly while this is happening.

I don't have the opportunity to install the SQL Server 2012 CU1 update because there seems to be some risk of errors installing hotfixes and I can't risk that right now.


texts: SQL Server 2016 memory optimised table: Precreated files constantly growing

texts:
I have a memory optimised table in SQL Server 2016, with file durability enabled. The table is only 100MB.
SQL Server is constantly creating new files on disk, which start off at a few hundred MB, but then grow constantly until the drive runs out of space then they clear down and the process starts again.
The files are growing at about 900MB an hour.
Running the SQL below shows that all the space is in Precreated Data - so it's not even actually storing large amounts of data, just allocating space to use later.

SELECT state_desc, file_type_desc, COUNT(*) AS [count],
  SUM(file_size_in_bytes) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type

File growth on the file group is set to unlimited, so I'm assuming I can set a value on this and stop it growing, but I can't find anything to say why it's allocating so much space in the first place. The data in the table does change very rapidly (a lot of inserts and deletes as the data is constantly processed, but it the data that isn't Precreated never gets over 4GB.

Is there a setting in SQL Server that controls this file precreation?
Is it safe to set a limit on the autogrowth for the file group?


texts: Publishing stored proc execution in transactional replication

texts:
In SQL 2008 R2, I am going to update a table which will affect 25 million rows, table is currently replicated (transactional replication). To minimize the impact on replication can I create a stored procedure, wrap the update statement inside the stored proc and add this stored procedure for the replication?

By doing this when stored proc executed it will replicate the execution of the stored procedure. My question is how SQL server knows to replicate only the stored proc execution (i.e. actual exec myupateproc t-sql command) instead of underlying table data that is being  updated on the publisher (i.e. actually replicating 25 million update statement)?


texts: How to use variables inside a select (SQL Server)?

texts:
If I want to compute a column and use the result in more than 1 column, how do I do this without making the computing twice?

Example:

SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
      ,hashbytes('SHA1', LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com') as HashedEmail
FROM sometable

How do I avoid writing this twice without using more than one select?


texts: PostgreSQL function to get levels (hierarchy)

texts:
I need a function which returns description of level from cascade (hierarchy) tables.

CREATE TABLE level_1(
 id_1 SERIAL,
 description_1 text
);

CREATE TABLE level_2(
 id_2 SERIAL,
 description_2 text
);

CREATE TABLE level_3(
 id_3 int4 SERIAL,
 description_3 text
);

CREATE TABLE levels(
 id_levels int4 SERIAL,
 id_1 int4,
 id_2 int4,
 id_3 int4
);

I need this function:

SELECT f_get_level('Level 1', 10);

'Level 1' ..  which level I want
10 .. value of id_levels

With this result:

description_1

The table levels is cross table between the others. Example:
Level 1 - Site
Level 2 - Sectors in Site
Level 3 - Lines in Sector

Levels: Level 1 - Level 2 - Level 3

id1  - Site (S1) - Sector (NULL) - Line (NULL)
id3  - Site (S1) - Sector (Sec1) - Line (NULL)
id4  - Site (S1) - Sector (Sec1) - Line (L1)
id5  - Site (S1) - Sector (Sec1) - Line (L2)
id6  - Site (S1) - Sector (Sec1) - Line (L3)
id7  - Site (S1) - Sector (Sec2) - Line (NULL)
id8  - Site (S1) - Sector (Sec2) - Line (L1)
id9  - Site (S1) - Sector (Sec2) - Line (L2)
id10 - Site (S2) - Sector (NULL) - Line (NULL)
id11 - Site (S2) - Sector (Sec1) - Line (NULL)

I need a function where parameters are id and Level.
Examples:

Select f_get_level('Sector', 5)  -- where 5 is id5

Result: 'Sec1'

Select f_get_level('Site', 11)   -- where 11 is id11

Result: 'S2'

Select f_get_level('Line', 6)    -- where 6 is id6

Result: 'L3'

Select f_get_level('Sector', 6)   -- where 6 is id6

Result: 'Sec1'


texts: Change SQL Server backup destination while retaining server's knowledge of location

texts:
I currently have a number of SQL Server (2008 R2) databases that are receiving tlog and differential backups.

The backups are currently being stored on a NAS that is going to be repurposed. I need to start sending the backups to a new NAS that has a different path from the current NAS.

What I would like to do is forklift all of the current files from the current NAS to the new NAS, and reconfigure the current backup jobs to begin saving to the new NAS.

I know this won't break the restore chain, but I'm concerned this move will break SQL Server's awareness of where to find backup files in the backup chain. (It will try to find old backup files in in the old NAS location, not the new NAS location.)

Can this be accomplished by updating the records in the backupmediafamily table alone, or is there more to it than this?


texts: Why transaction blocking simple insert in MySQL?

texts:
transaction Query
    mysqli_begin_transaction($conn);
    $sql = "UPDATE foldertable SET trashed = 1 WHERE serverToken = (SELECT serverToken from servertoken where userToken = ? limit 1) and

    (folderId = ?
    or
    RootFolderPath LIKE
                CONCAT (
                    (SELECT RootFolderPath FROM foldertable WHERE serverToken = (SELECT serverToken from servertoken where userToken = ? ) AND folderid = ? limit 1)
                    ,'/'
                    , ?
                    ,'%'

                )
    )";
    $stmt =  mysqli_stmt_init($conn);
    mysqli_stmt_prepare($stmt, $sql);
    mysqli_stmt_bind_param($stmt, "sisii", $data["userToken"], $id, $data["userToken"], $id, $id);
    if (!mysqli_stmt_execute($stmt)) {
        echo mysqli_stmt_error($stmt);
        $serverError = true;
        return;
    }
    // echo "\n\n affected rows " . mysqli_affected_rows($conn);
    if (mysqli_affected_rows($conn) > 0) {
        sleep(15);
        // Insert into bin folder
        $sql = "INSERT INTO binfolder (serverToken , folderId) value ( (SELECT serverToken from servertoken where userToken = ? limit 1) , ?)";
        $stmt =  mysqli_stmt_init($conn);
        mysqli_stmt_prepare($stmt, $sql);
        mysqli_stmt_bind_param($stmt, "si", $data["userToken"], $id);
        if (!mysqli_stmt_execute($stmt)) {
            echo mysqli_stmt_error($stmt);
            $serverError = true;
            return;
        }
        if (mysqli_affected_rows($conn) > 0) {
            $success = true;
            mysqli_commit($conn);
        }

    }

Insert Query
INSERT INTO foldertable(serverToken) SELECT (123456)

Problem
if execute the insert query  after transaction started but the insert does not have to wait for transaction complete . Because the update query on transaction does lock row involved with insert query.
What I wanted
the Insert query will run immediately if it does not contain the same serverToken needed in Insert query.
Thank You in Advance.


texts: ER Diagram - Choose One or Both of the Entities

texts:
So I am attempting to create an ER Digram for a certain catering service. Two of the entities are "Lunch Menu" and "Dinner Menu". Each catering reservation (entity) must contain either one of the two, or both.

How should I go about modelling this relationship?


texts: Algorithm to match two distant data sets for best performance in SQL

texts:
I have the following data sets:

A list of users.

Tom, Lisa, Karen

These users can manage any shop (one or more):

Barba, Rentraum, Kisabran, Tespot and Lowvel

These shops can provide any services/products:

Yoga, Coffee, Mechanics, Gym

These services can receive a complaint, which can only BE for ONE service at a given SHOP!

Complaint 1 -> Yoga at Kisa
Complaint 2 -> Yoga at Tesport
Complaint 2 -> Cofee at Barba
etc

Now, I want at any time to PASS the user_id (Tom, Lisa, Karen) to a query, and get all COMPLAINTS eligible to that user.


texts:
I am getting error

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '"insert into all_booking(name,address,event_type,hallNo,arrival_time,arrival_da' at line 1

Query I tried

insert into hall_booking(name,address,event_type,hallNo,arrival_time,
arrival_date,dep_time,dep_date,identity,emailid,
contact,total_members,desc,catering,service,
decoration,other)   values
('$nm','$add','$typ','$roomno','$arrv',
'$arrivaldate','$departure','$dDate','$idt','$emailid',
'$cont','$desc','200','200','200',
'200')"


texts: MySQL XtraDB/Galera Cluster High Availability RamDisk *SuperFast*

texts:
Ok, So this is going to be a speculative question, mostly design oriented and a rather long one. I'd grab a cuppa' covfefe, if I were you.

Preface: So I have been researching into databases and wanted a really fast (Like really-really) database(engine) with the following must haves,

ACID Compliance
In-Memory-ish for blazing fast IO.
Persistant (well...duh)
Scalable as in cluster/master-slave/etc
High-Availability(HA)
MySQL Drop-In replacement
Open Source
Should run on commodity server (IYKWIM)

So judging by my over optimistic list of requirements you'd already jump to ....ummm

Alright, alright jokes aside, I know if innodb_buffer_pool_size is tweaked properly it'll run off of the memory most of the time, but I say

It ain't in-memory yo!

But you'd say Hey, its 2k18 people might have already created some 100% in memory DBs, Right? Umm... actually they have but each have their own tradeoffs.

VoltDB Community Edition
Everything seems fine until you realise it isn't a drop in replacement. It needs some stored procedures-ish commands in java which requires you rewriting whole of your application or atleast the db layer/driver/etc of your php app. So? DEALBREAKER!!!
MemSQL, Well this seems a pretty strong contender for our "Best OpenSource In-Mem Scalable SQL Acid DB ever" contest. Only for, the memSQL Boss be like...

Needless to say, memSQL needs atleast 4 cores and 8Gigs of RAM at minimum, and recommeded is pretty insane at 4 cores and 32 gigs per core!!!!
Also the community version of memSQL(which btw, isn't fully OpenSource!, its just free) doesn't support high availability, as its a paid feature. Also its NoSQL. So? DEALBREAKER!!!

All other NoSQLish dbs like membase, Redis, Memcached, etc are prelly much ruled out.

So now for my genius idea!!!

I was wondering if we could run an XtraDB/Galera Cluster with all the instances running off of a RAMDisc with regular snapshots?

It gets all the checkboxes ticked.

Just hear me out, First addressing the elephant in the room, We know that running full mysql Dbs off of RAMDiscs is pretty umm...bold, put in the most polite of ways. So what happens if the server crashes/shuts off/etc we loose a node. While all our DB Cluster as a whole is still alive and kicking a**. All we have to implement is booting the db up from last snapshot and syncing back with the cluster which btw the clusters are pretty good at, inherently!

OK, peeps, Don't go all salty on me, if you see a flaw in my implementation, guide me.


texts: In case of inserted a fixed value into autoincrement how to automatically skip into the next sequence value without causing insert to have an error?

texts:
In my postgresql 9.2 database I created the following table:

CREATE TABLE dummy(id SERIAL PRIMARY KEY,text TEXT);

And I inserted a record via:

INSERT INTO dummy(text) VALUES ("Hello Word");

Then I inserted a record like that:

INSERT INTO dummy(id,text) VALUES (2,'SAYONARA Word');

Afterwards when I tried to insert a record like that:

INSERT INTO dummy(text) VALUES ('<3 Word');

I get the following error:

  ERROR:  duplicate key value violates unique constraint "dummy_pkey"
  DETAIL:  Key (id)=(2) already exists.

But when I reinsert the value:

INSERT INTO dummy(text) VALUES ('<3 Word');

No error is thrown.

So as I see the potgresql autoincrements a value for id via an internal counter regardless whether the value is sucessfully inserted or not. Also the error has occured because postgresql does not "punches holes" on exising counter id.

So how I can tell postgresql if the autoincrement value exists just to skip into the next one and avoid using the existent autoincrement value. (By existing I mean values that have already set as primary key).


texts: Can I iterate a value depending if the previous is what I want?

texts:
Suppose that I have this table in PostgreSQL 9.4:

CREATE TABLE trajs (
    id serial NOT NULL PRIMARY KEY,
    obj_id integer,
    traj_id integer,
    geom geometry,
    t timestamp without time zone
);

Here is what the data looks like:

 obj_id, traj_id,  geom,     t
    1       1      point     '2016-04-29 09:35:00'
    1       1      point     '2016-04-29 09:40:00'
    1       1      point     '2016-04-29 09:55:00'
    2       1      point     '2016-04-29 09:35:00'
    2       1      point     '2016-04-29 09:42:00'
    2       1      point     '2016-04-29 09:50:00'
    3       1      point     '2016-04-29 09:35:00'
    3       1      point     '2016-04-29 09:41:00'
    3       1      point     '2016-04-29 09:43:00'

For each 'obj_id', can I iterate 'traj_id' depending on the 't' distance between the previous?

An example: for each 6 minutes of difference (between the first object with a time, like 3rd column and 1st in that table), this should be a new traj_id (+1) - and restart for a new obj_id. Here is what result should look like:

 obj_id, traj_id,  geom,     t
    1       1      point     '2016-04-29 09:35:00'
    1       1      point     '2016-04-29 09:40:00'
    1       2      point     '2016-04-29 09:55:00'
    2       1      point     '2016-04-29 09:35:00'
    2       2      point     '2016-04-29 09:42:00'
    2       3      point     '2016-04-29 09:50:00'
    3       1      point     '2016-04-29 09:35:00'
    3       2      point     '2016-04-29 09:41:00'
    3       2      point     '2016-04-29 09:43:00'

I started doing this, but my solution doesn't work. And I can't see how it can iterate by every time that the condition is met.

update trajs t
set traj_id = traj_id+1
where t.t > (select ant.t + interval '6 minutes'
             from trajs ant
             where  t.obj_id=ant.obj_id);

How to solve this?


texts: How to transpose rows into columns in SQL?