texts: Which Query of JOIN with DISTINCT & JOIN with Subquery has better Performance?

texts:
In the following two queries: http://sqlfiddle.com/#!9/081f9e/11

#1ST QUERY
SELECT a.id,
       g.names as gamma,
       o.names as omega
FROM alpha a
     JOIN (SELECT ao.alpha_id,
               GROUP_CONCAT(o.name) AS names
           FROM alpha_omega ao
               JOIN omega o on o.id = ao.omega_id
           GROUP BY ao.alpha_id ) o ON o.alpha_id = a.id
     JOIN (SELECT ag.alpha_id,
               GROUP_CONCAT(g.name) AS names
           FROM alpha_gamma ag
               JOIN gamma g on g.id = ag.gamma_id
           GROUP BY ag.alpha_id ) g ON g.alpha_id = a.id
WHERE a.id = 1;

#2ND QUERY
SELECT a.id,
       GROUP_CONCAT(DISTINCT g.name) AS gamma,
       GROUP_CONCAT(DISTINCT o.name) AS omega
FROM alpha a
     JOIN alpha_omega ao ON ao.alpha_id = a.id
     JOIN omega o ON o.id = ao.omega_id

     JOIN alpha_gamma ag ON ag.alpha_id = a.id
     JOIN gamma g ON g.id = ag.gamma_id
WHERE a.id = 1
GROUP BY a.id;

Which one of them is better to be used to achieve the optimal performance and efficiency?

The First one uses JOIN with SubQueries while the Second one uses JOIN with DISTINCT, In the First one I get exactly 8 values out from gamma & omega columns,

But in the Second one, I get duplicated values up to the double, Which makes me get 16 value, Then I use DISTINCT to make it 8 values

Keeping in mind that I've set the correct INDEXes to achieve the maximum speed from INDEX.

Which is better to be used on the long-term exactly?

Let's say I have 50,000 record in each Table, And the Query is executed 20,000 time per Sec.


texts: How to model Week Commencing / Week Ending scenario

texts:
I’m reasonably new to data warehousing and I’m struggling to get a feature of my time dimension working. I’d like to model a Week Commencing and Week Ending type of scenario.

I’ve created a DimTime table as per below.

SELECT TOP 10000 identity(INT,1,1) AS N
INTO #TallyTable
FROM
    master.dbo.SysColumns c1
CROSS JOIN master.dbo.SysColumns c2

ALTER TABLE #TallyTable ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

SELECT
    [N] 'TimeKey',
    dateadd(dd, N-1, @startDate) 'LongDate',
    CONVERT(date,dateadd(dd, N-1, @startDate)) 'ShortDate',
    dateadd(YYYY,datediff(yy,0,dateadd(dd, N-1, @startDate)),0) 'YearKey',
    datepart(YYYY,dateadd(dd, N-1, @startDate)) 'YearNumber',
    dateadd(mm,datediff(mm,0,dateadd(dd, N-1, @startDate)),0)'MonthKey',
    datepart(mm,dateadd(dd, N-1, @startDate)) 'MonthNumber',
    datepart(dd,dateadd(dd, N-1, @startDate)) 'DayOfMonthNumber',
    datename(mm,dateadd(dd, N-1, @startDate)) 'MonthName',
    datename(dw,dateadd(dd, N-1, @startDate)) 'DayName',
    dateadd(qq,datediff(qq,0,dateadd(dd, N-1, @startDate)),0) 'QuarterKey',
    datepart(qq,dateadd(dd, N-1, @startDate)) 'QuarterNumber',
    dateadd(ww, DATEDIFF(ww,0,convert(date,dateadd(dd, N-2, @startDate))), 0) 'WeekCommencing',
    dateadd(dd,6,DATEADD(ww, DATEDIFF(ww,0,convert(date,dateadd(dd, N-2, @startDate))), 0)) 'WeekEnding'
INTO
    #CalendarTable
FROM
    #TallyTable
WHERE
    dateadd(dd, N-1, @startDate) < '2024-01-01'
ORDER BY
    N

SELECT * FROM #CalendarTable ct

So looking at TimeKey 1 you can see that the 1st of Jan 2004 fell into the week commencing 29/12/2003 and week ending 04/01/2004.

For our purposes a week starts on a Monday and ends on a Sunday.

The requirement is to have a number of named sets including:

Current week - which given today’s date would be anything that falls between 28/01/2013 and 03/02/13.
Previous week - which given today’s date would be anything that falls between 21/01/2013 and 27/01/13.
Current year up to end of previous week - which given today’s date would be anything that falls between 01/01/2013 and 27/01/13. Currently my DimTime dimension in SSDT is as per screenshot DimTime.png

My attribute relationships are defined as in AttribRel.png

Is it possible to model a week commencing/ending type of scenario or am I trying to achieve something that isn’t possible? And if so how? I’ve tried a bit of research on google but not coming up with anything that solves this problem. Plenty of examples for “Week of year” type scenarios but nothing for week commencing.

Thanks,

Simon


texts: SQL Server 2012 compress column for whole table

texts:
I was wondering if SQL Server supports a kind of dictionary compression for a whole table, and not just a page.

The system I'm working on wasn't initially created for the large amounts of data it processes today. The problem I'm having at the moment is the following:

The application allows users to create legal contracts. These contracts are standardized, however the user is allowed to adjust the contents (text) of the contract if he wishes.

To facilitate this, each contract makes a copy of the standardized contract text. In reality we've discovered that users almost never edit the contract texts so we've ended up with a table with a lot of duplicate data.

Usually I would change the DB model to fit the use case, however this is a legacy system and such a change is pretty expensive. There's being worked on a replacement for it, so investments like these aren't done as easily.

Is it possible to have column dictionary compression for the whole table, and not just 1 page?

We're using SQL Server 2012 in a on premise SQL cluster.

The problem is that the table is 80GB in size, the whole DB is 180GB in size. This table takes up a lot of space and we don't have enough memory so SQL server keeps unloading the data.

The data of this table is used to generate a PDF which represents the contract. Each time a user modifies the state of a contract a new PDF is generated and stored for auditing purposes, this generates a lot of reads on this table.

The reads go down to disk (since SQL server keeps unloading the table from memory). This is creating a huge IO strain on our SAN.

The memory problems are being worked on, but that will take a couple of weeks. It suffices to say for now that simply plugging more memory in isn't an option at the moment.

My thinking is: for a short term solution - compress the data which will drastically reduce the size of the table and thereby making it possible for SQL Server to keep the table in memory which reliefs our IO strain on the SAN.


texts:
In MySQL 5.5, I have a table which is recreated on a daily basis and for which the process of adding an index is taking about 50 minutes which I'm hoping to reduce.  At the time when the index is created the table usually has about 25 million records and there is plenty of disk space and RAM.
While the index is being added the process status is "copy to tmp table" for most of the time.

my.ini file settings

[mysqld]
basedir = C:/Bitnami/WAMPST~1/mysql
datadir = D:/mysql\data
key_buffer_size = 384M
max_allowed_packet = 16M
tmpdir = E:/mysql/tmp/
tmp_table_size = 1024M
max_heap_table_size = 1024M
table_cache = 256
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
net_buffer_length = 8K
myisam_sort_buffer_size = 8M

Table structure:

CREATE TABLE `log_access` (
  `_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type_id` int(11) NOT NULL,
  `building_id` int(11) NOT NULL,
  `card_id` varchar(15) NOT NULL,
  `user_key` varchar(35) DEFAULT NULL,
  `user_name` varchar(25) DEFAULT NULL,
  `user_validation` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`_id`),
  KEY `log_access__user_key_timestamp` (`user_key`,`timestamp`)
  KEY `log_access__timestamp` (`timestamp`)
) ENGINE=MyISAM

Statement to add the index

ALTER TABLE `log_access` ADD INDEX `log_access__user_key` (`user_key` ASC);


texts: Getting SQL Server Agent to run multiple instance of a job at a time

texts:
As I understand it, SQL Server Agent runs only one instance of a job (SSIS Package in this case) at a time.

I need it to Start a job 15 times, and then let them all run at the same time.

Up to now, I have accomplished this quite successfully, manually, by right clicking on the job and selecting "Execute". But i need to automate this now.

I have tried to schedule the job to start 15 times,  but it only
works sequentially. (1 job after the other)
I created 15 separate jobs and started them, but the first one starts
and the others not, stating the job is already in use.

Should I look at another scheduling tool to accomplish this?

Any pointers would be welcome.....


texts: Run MySQL replication by schedule or on demand

texts:
I have a master-slave replication currently configured. I know this setup runs real time. Is it possible to sync only the databases on schedule (daily or weekly) or by demand?

Thanks!


texts: A join aggregate behaviour I can't understand

texts:
Query 1:

 SELECT c.class_id,
       SUM
       (
        CASE
         WHEN s.student_id IS NULL THEN 0
         ELSE 1
        END
       ) as total_students
  FROM classes c LEFT JOIN students s ON c.class_id = s.class_id
GROUP BY c.class_id;

result:

453 100
461 10
462 19
464 0
471 0
499 133

great !

Query 2: (added where condition to filter student types)

 SELECT c.class_id,
       SUM
       (
        CASE
         WHEN s.student_id IS NULL THEN 0
         ELSE 1
        END
       ) as total_students
  FROM classes c LEFT JOIN students s ON c.class_id = s.class_id WHERE s.student_type='instate'
GROUP BY c.class_id;

result:

453 89
461 5
462 19
499 43

All the figures are right, except the ones with 0 counts vanish :!

why is that ?


texts: Access SQL Server Express LocalDB from local area network

texts:
I am developing a software which requires sql server. But for 50 MB application it is not good idea to tell customers to install SQL Server. So I think it is better to use SQL Server Express LocalDB.

I want to give feature of accessing my app from Local area network. But I am not getting any related topics to do this.

Is it possible to access SQL Server Express LocalDB on Local Area Network?

What are the limitations of SQL Server Express LocalDB compared to SQL Server?


texts: How do I select distinct one column and return the other column data that has been distincted?

texts:
I am newbie to SQL and PHP programming so please be patient with me. :)

For example I have one table with three fields: ID, ITEMNAME, SIZE, and data like this:

+----+--------------+-------+
| ID |   ITEMNAME   | SIZE1 |
+----+--------------+-------+
|  1 | Blue T-shirt |   M   |
+----+--------------+-------+
|  2 | Blue T-shirt |   L   |
+----+--------------+-------+
|  3 |  Red T-shirt |   M   |
+----+--------------+-------+

And I want to display it using SQL command as:

+--------------+-----------------+
|   ITEMNAME   |       SIZE      |
+--------------+-----------------+
| Blue T-shirt | select tag(M/L) |
+--------------+-----------------+
|  Red T-shirt |        M        |
+--------------+-----------------+

The items with the same name will merged and create a select tag with the option of the size column data. How can I do that?

Any other way will be appreciated.


texts: So far I always used date ranges to partition my tables, however now I like to do it by state or a selected list of cities where all others that are not explicitly listed in an "other" partition, is this possible with SQL Server ?


texts:
Advisable to run MariaDB's AriaDB engine's /var/lib/mysql/[databases] folder over Amazon EFS with transaction = 1 for the database creation process?
Since AriaDB performs table level locking and only .MAD, .frm, .MYI files, I have mounted AWS EFS as an external nfs drive mounted for /var/lib/mysql/[databases]
Rationale of doing so is so that the mariadb database can grow without worrying overcapacity over conventional methods. Any gurus want to give their opinion on this? Else what db engine is suitable to be ran on AWS EFS as aforementioned use case?


texts: Switching the OS from WSE2012 to WSE2019 on new drive, but Mysql database still on same data drive - Can I just install MySql on new OS and connect?

texts:
Current setup - Server Essentials 2012 on Drive 0 partitioned into OS and DB partitions. Mysql innnodb database on the DB partition.
New clean install of ServerEssentials 2019 on Drive 1 (the New C: drive). I plan to leave the MySql innodb on the DB partition of Drive 0.
Can I just install MySqlServer (same version as on original Drive) in Server Essentials 2019 and then set the Drive 0 DB partition as the working directory for this new installation? Will the 2019 SE install of MySql then just pick up where the old one left off?
This would save tons of work - wince it would only involve setting a junction point at C:\ProgramData\MySql to the Db partition.


texts: How do I speed up a string manipulation query where I want to replace characters, extract certain values and update a table with the results?

texts:
I am trying to extract information from strings that are presented in a key-value format, with the keys and values being separated by commas. I want to extract the values associated with certain keys and add them to dedicated columns in my table.
Some notes on the data:

The keys I am interested in are connected, as in keyB relates to keyA;
in some cases keyB or keyA may not exist
If keyB doesn't exist, but keyA is something specific, then I can set the value for keyB anyway.

I have a solution that does what I want ([db-fiddle]), but it is painfully slow (9.6 hours) and I can't help think that there must be a better way as I've not been in this DB game long.
For info,
The table has ~8.2M rows and is hosted on AWS RDS on a t3.large DB (virtual CPUs = 2, Memory = 8.0GB).
Some pointers on where I can improve this are much appreciated.


texts: CU22 installation on Distributed Always ON - SQL Server 2017

texts:
We're planniing to install SQL 2017 - CU22 on Distributed Always On environment without taking any downtime. Can you please review below steps and confirm if need to follow any additional steps to avoid downtime and impact on business.
DC1:
dc1-db1 (primary)
dc1-db2 (secondary)
WSFC Name: wsfcdc1
DC2:
dc2-db1 (secondary)
dc2-db2 (secondary)
WSFC name: wsfcdc2
dc2:

change failover mode to manual on dc2-db1
install CU22 on dc2-db1
reboot dc2-db1
install CU22 on dc2-db2
reboot dc2-db2
Change failover mode to automatic

dc1:

chnage failover mode to manual on dc1-db1
install CU22 on dc1-db1
reboot dc1-db1
install CU22 on dc1-db2
reboot dc1-db2
perform manual failover from dc1-db2 to dc1-db1
Change failover mode to automatic

Thanks in advance.


texts: Lock priority for scripts run in maintenance windows

texts:
We run maintenance windows weekly and monthly. Some of our maintenance windows are online and the last thing we want is our scripts to block live queries from our applications.

Is there a way to set the priority of a query so that it cannot cause other queries to block for more than X seconds?

Example:
I run maintenance script A on tableA
Application is updating a row on tableA, waits on the maintenance script to complete.
SQL Server automatically terminates and rollback my maintenance script after X seconds of blocking.

I know there is a similar functionality with deadlocks (set deadlock victim to low).

Thanks!


texts: MySql | How to compute every row in same table

texts:
I have this table

Grade   Lec    Lab
1.50       3      0
1.50       3      0
1.50       2      1

The formula will be

1.50 * 3
1.50 * 3
1.50 * (2 + 1) = 13.5

The results will divide to total number of lec and lab it will be 13.5 / 9 = 1.5


texts: SQL 2005 Replication Create/Delete Tables, Views, and Procs

texts:
I have replication setup on a couple SQL2005 servers.

According to http://msdn.microsoft.com/en-us/library/ms151870(v=sql.105).aspx replication works when changing an item.

How do I have replication pick up new tables and proced


texts: MySQL : Why are there "test" entries in mysql.db?

texts:
Recently, I posted an answer to a question about mysql.db.

Then, I got to thinking I should ask everyone this question:

I have noticed for years that upon installation of MySQL 5.0+, mysql.db is populated with two entries that allow test databases to be accessed by anonymous users.

You can see it by running this query:

mysql> select * from mysql.db where SUBSTR(db,1,4) = 'test'\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
2 rows in set (0.00 sec)

Are these entries in the mysql.db a security risk, and if so, why are they added by default to a new install?

UPDATE 2013-06-14 10:13 EDT

This morning someone downvoted my question, which I truly don't understand. In light of this event, here is why I took the time to make a rebuttal:

I installed MySQL 5.6.12 for a client this week in their Staging Cluster. I decided to check to see if this was still an on-going problem:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)

mysql> select db,user,host from mysql.db where LEFT(db,4)='test';
+---------+------+------+
| db      | user | host |
+---------+------+------+
| test    |      | %    |
| test\_% |      | %    |
+---------+------+------+
2 rows in set (0.10 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-06-14 10:10:13 |
+---------------------+
1 row in set (0.00 sec)

mysql>

Guess what? This is still a problem even to this day !!!

MORAL OF THE STORY : Please check your mysql.db immediately after installation and remove anonymous logins and erase these test entries from mysql.db without delay.


texts:
I'm pretty sure this is a implicit conversion but I wasn't able to find information about it.

Suppose I have this SQL:

SELECT
CASE WHEN (SELECT 1 WHERE (1=1)) = 1 THEN 1 ELSE 0 END

My subquery is returning one row and hence compared to 1. This leads my CASE WHEN to produce the 1 output.

Even if I set the subquery to return no rows like the sample below, the CASE statement returns  the 0 result:

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) = 1 THEN 1 ELSE 0 END

Does the SQL interpreter make a conversion between "empty set" to 0 or 1 ?
I don't feel comfortable using this type of comparison. In my humble opinion, it's better to use EXISTS like this:

SELECT
CASE WHEN EXISTS(SELECT 1 WHERE (1=1)) THEN 1 ELSE 0 END

Is there any advantage or disadvantage if I don't use EXISTS ?


texts: Using MVIEWS to take in time data snapshot of tables

texts:
I have a requirement where I have to Load data periodically from more than 100 tables of an OLTP oracle database into MS SQL database. In my solution I want to use MVIEWs of these tables with Fast Refresh as the point of data extraction in oracle.

Every time before triggering the data load into MS SQL database I will refresh the MVIEWs to get the data. However have below mentioned doubts:

1) Is there a way through which i can refresh all the MVIEWs at once?

2) How can I ensure that data changes happening during the MVIEW refresh will not be reflected in the refreshed MVIEW. e.g. I started a refresh of all the MVIEWs at 10:00 AM then how can I ensure that no changes happening after 10:00 AM reflects in the refreshed MVIEWs?

3) Is there a better way of achieving this? I don't have the option of using mirror database.


texts: Automating DB Restore from network share

texts:
We have a network share that uses different credentials from the currently logged in user. Inside this share we have full, differential and log backups that come through from a third party database that are updated daily.

I've been tasked with automating the restoration of these backups to a local database.

What's the best way to go about designing and implementing a solution for this?

Folder Structure

Log File structure

As can be seen above, there are multiple files in each folder with different file names depending on the date, etc.

Update:

Following on from the solution posted below, I had to modify the suggested PowerShell code slightly in order to make it work:

$pass ="xxx"|ConvertTo-SecureString -AsPlainText -Force
$cred = New-Object System.Management.Automation.PsCredential('Domain\Account',$pass)

New-PSDrive -Name K -PSProvider "FileSystem" -Credential $cred -Root '\\UNC PATH TO FOLDER WHERE BACKUP IS STORED';
Get-PsDrive;

Copy-Item K:\ -Recurse M:\DESTINATION;

Restore-DbaDatabase -server MRVAPPSQL01 -path M:\DESTINATION -MaintenanceSolutionBackup -WithReplace;

Remove-PSDrive -Name K;

Remove-Item M:\DESTINATION\*.*;


texts: Hide table structure to users in firebird

texts:
I have been searching for a way to hide tables structures for some user but without success. I need that some users can only browse two views, but they don't be able to browse tables structures.
Is there any way?


texts:
On a big server with many user and website from 11AM - 00AM our MySQL throughput realised in max (60 - 80M)
this timestamp our customer website load slowing .

We haven't any Disk IO , and server HDD : 6 * 600 G SAS 15K + 64 G RAM

MySQL config :

innodb_buffer_pool_size=8192M (2 G above mysqltuner recommendation)

Screenshot:

Any idea to solve this problem?

This comment add after first answer :

Hello Dear Damiano :
Thank you so much your answer . about your comment :
I will attach the complete screen from server monitor . (end of this comment)

about slow query :
I have done this , but every 24 hrs we have under 500 slow query . and most them not occur repeatedly .

an other thing , this server big and we have High traffic web site on it , so I can not ask customer don't use server resource .

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 11573    |
| Qcache_free_memory      | 27291504 |
| Qcache_hits             | 83817933 |
| Qcache_inserts          | 36512628 |
| Qcache_lowmem_prunes    | 15633028 |
| Qcache_not_cached       | 4338965  |
| Qcache_queries_in_cache | 29930    |
| Qcache_total_blocks     | 106130   |
+-------------------------+----------+

Here You are all thing need :
http://www.4shared.com/zip/jX54_Dprba/data.html

1 screen shot + 1txt file from output server


texts: MySQL unclearness about DataType TIMESTAMP with DefaultValue CURRENT_TIMESTAMP

texts:
In MySQL InnDB engine I created TableColumn with data type TIMESTAMP and put there as default value CURRENT_TIMESTAMP, but this value are always changed to CURRENT_TIMESTAMP when I update that by using JDBC or MySQL Administrator GUI,

my questions are

1) is this feature by default and I have to remove CURRENT_TIMESTAMP from Table settings and update this column standard way(s)

2) is there some option / setting that allows me to switch on/off this feature

Sql engine is MySQL 5.1.29-rc community


texts: Optimize table on large table online DDL Feature

texts:
I am running optimize table on MySQL table size 340GB in size and storage engine is InnoDB.

mysql> show variables like '%tmp%';
+----------------------------+------------------+
| Variable_name              | Value            |
+----------------------------+------------------+
| default_tmp_storage_engine | InnoDB           |
| innodb_tmpdir              |                  |
| max_tmp_tables             | 32               |
| slave_load_tmpdir          | /dbtmp/mysql_tmp |
| tmp_table_size             | 33554432         |
| tmpdir                     | /dbtmp/mysql_tmp |

tmpdir directory is filling up space .

/dev/mapper/vgdbtmp-lvdbtmp
                      296G  231G   51G  83% /dbtmp

What will be the alternate solution to optimize table without creating copy of table.

MySQL 5.6 version

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.46-log |
+------------+
1 row in set (0.01 sec)

I have read below MySQL link.

https://mysqlserverteam.com/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-partitioned-innodb-tables/

https://dev.mysql.com/doc/refman/5.6/en/optimize-table.html

Prior to Mysql 5.6.17, OPTIMIZE TABLE does not use online DDL. Consequently, concurrent DML (INSERT, UPDATE, DELETE) is not permitted on a table while OPTIMIZE TABLE is running, and secondary indexes are not created as efficiently.

But here no one is firing DDL/DML/select on table currently.

It should run online without creating copy of table right?


texts: Ways to stop access to a SQL express database

texts:
I have a SQL express database and was wondering what options I have for securing it?

More specifically it is a replicated database using merge replication. I want to stop a user with a replicated copy of the database opening the database, modifying it and running queries against it.

UPDATE:

Just wondering though, what happens if I have a database which is secured, but I then take it offline, copy the database, and attach it to a different database server. Does the security go with it, and can I not break into it like that?


texts: Why can I select multiple columns from dual?

texts:
When I execute this query:

select 2+2, 2+2, 2+2 from dual;

I get multiple columns but, as per definition, dual only has one row and one column. So what is the reason behind it?


texts:
I am using Oracle 11g database server.

I checked the SYSAUX used space using Object Browser, and it shows 99%.

What will happen if it reaches 100%?


texts: SQL Server FullText search on non-persisted columns

texts:
Let's say I have a table with 3 columns: Id, ColA and ColB. Then I created a FullText index:

CREATE FULLTEXT INDEX ON [Table]
(
    ColA LANGUAGE 1033,
    ColB LANGUAGE 1033
) KEY INDEX [PK__table_id]

However, I figured that if I do a query like this: ... WHERE CONTAINS(*, '"this" AND "that"') it will return only results that contain the words "this" and "that" in the same column (ie, either ColA contains both words or ColB contains both words, but "this" in ColA and "that" in ColB won't match).

As a solution I found I can do this:

ALTER TABLE [Table] ADD
    Combo AS [ColA] + CHAR(32) + [ColB]

CREATE FULLTEXT INDEX ON [Table]
(
    Combo LANGUAGE 1033
)

Notice that the computed column is NOT persisted.

The question is: is there any drawback with having a full-text index on a non-persisted computed column? I couldn't find any information related to this and don't have the resources to do performance tests other than in production. But it seems to work completely fine in my tests.

The only reference I found to this is in MSDN and but it doesn't say much.


texts: Standard naming for a credential systems

texts:
Is there a standard naming convention in regards to credential oriented systems? (Logins) The only one I could really come up with (in a shorthand form) is using the word "Gateway". Representing a gateway to further functionality behind a log in form.

Please note. I am NOT asking for suggestions on what I should name my schema objects. I am asking if there is an established standard for naming such things. If there is not then an adequate answer would outline this fact. Please do not post opinions on what you believe is the right way


texts: Can't update trigger because of activity

texts:
Trying to add a line to a trigger that is attached to a table that is constantly in use

ALTER TRIGGER [dbo].[name_of_trigger] ON [dbo].[name_of_table] AFTER UPDATE
BEGIN

   IF ORIGINAL_LOGIN() in ('username')   RETURN

....somecode

END

Getting deadlocked and the only solution appears to be retrying every couple of seconds, which so far hasn't worked. Is there any other way to get my if condition in other than hitting F5 and hoping it works?

Thanks,

Craig


texts:
MS SQL Server apparently can tell you what indexes would be useful for your query. Is there anything similar in MySQL?

This article (scroll to "Find missing indexes") says that in Microsoft SQL Server you can use sys.dm_db_missing_index_details etc. to see what indexes would be useful for a given query, which largely renders deciding what indexes to make a trivial task.

In contrast, with MySQL I only know of EXPLAIN as a way to see whether an index that you already have is used for your query, but not what index would be used it you had it. This renders choosing what indexes to have a trial and error procedure.

Or, you may be tempted to create all possible indexes (millions!) and then use EXPLAIN to see which ones are actually used. But this seems to be too much work?

The question is whether we have a similar functionality in MySQL: to make EXPLAIN assume you have any index it might wish, and tell you which one it would use if it existed?


texts: Sort products by image first and by name ASC

texts: I have a table with products, some products have images, some of them don’t. I have to sort these products by name ASC but first products should be these with an image (field isn’t empty string), and after that it should list the products without image, also name ASC. Thanks for help in advance!


texts: Why Clustered Index property is enabled on few columns and not on others?

texts:
I heard that there can be only one Clustered Index? I may be wrong, but I am sure I read somewhere.

In one of my SQL Server 2005 table, I have following columns:

SerialNum ------- BigInt -------- Primary Key

District -------- nvarchar(50)

SchemeType ------ nvarchar(50)

When I open the table in design mode using SQL Server Management Studio, I notice that Clustered Index property is enabled for District, whereas it is disabled for SchemeType.

If there can be only one Clustered Index, then why District has this property enabled when SerialNum is already defined as Primary Key?

If there can be more Clustered Indexes, then how to enable this property for SchemeType column?


texts:
Here's part of my checkpoint log:

2014-03-26 11:51:29.341 CDT,,,18682,,532854fc.48fa,4985,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 15047 buffers (1.4%); 0 transaction log file(s) added, 0 removed, 30 recycled; write=68.980 s, sync=1.542 s, total=70.548 s; sync files=925, longest=0.216 s, average=0.001 s",,,,,,,,,""
2014-03-26 11:56:05.430 CDT,,,18682,,532854fc.48fa,4987,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 16774 buffers (1.6%); 0 transaction log file(s) added, 0 removed, 31 recycled; write=72.542 s, sync=17.164 s, total=89.733 s; sync files=885, longest=3.812 s, average=0.019 s",,,,,,,,,""
2014-03-26 12:01:21.650 CDT,,,18682,,532854fc.48fa,4989,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 14436 buffers (1.4%); 0 transaction log file(s) added, 0 removed, 33 recycled; write=122.350 s, sync=5.212 s, total=127.676 s; sync files=924, longest=3.740 s, average=0.005 s",,,,,,,,,""
2014-03-26 12:06:25.028 CDT,,,18682,,532854fc.48fa,4991,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 13277 buffers (1.3%); 0 transaction log file(s) added, 0 removed, 29 recycled; write=126.217 s, sync=5.733 s, total=131.991 s; sync files=894, longest=1.859 s, average=0.006 s",,,,,,,,,""
2014-03-26 12:10:41.958 CDT,,,18682,,532854fc.48fa,4993,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 20765 buffers (2.0%); 0 transaction log file(s) added, 0 removed, 28 recycled; write=88.015 s, sync=10.818 s, total=98.872 s; sync files=881, longest=2.690 s, average=0.012 s",,,,,,,,,""

I noticed that sometimes our database is very slow - you can see a very large number of normally short queries stuck for much longer than now. It happens regularly without a clear culprit.

Question: Could checkpoint cause this? What happens in the "sync" phase of checkpoint?


texts:
I have a question with an oracle query that takes almost 20 minutes when it is executed from a schema and yet when it runs as sys users it runs in 1 second maximum ... how is this possible? ... I do not know much about tuning plsql and I find it difficult to find the cause of this tremendous delay, I would like to know if there is any tool to autotuning the queries. The sentence in question is:

SELECT FC.ID_SER
     FROM MIGER.FONT_COUNTER FC
     WHERE TO_CHAR(FC.ID_SER) NOT IN (SELECT
     ID_SER FROM INAC.SERV)
     -- AND (FC.STATE <>'R' OR FC.STATE <> NULL)
     AND FC.T_ACT='M'

Thank you


texts:
We have a sybase database running an application our users use which contains client info.

The situation is that address information for maybe 1000 clients or so is out of date. We were given a .csv file with updated address info, and are being asked to update the addresses in the database.

I've filtered the csv to compare the 2 address columns and only show those that are not the same. So I have a .csv containing only the clients who need their address updated.

So imagine this client information lives on a table. We also have unique client ID's to go by along with other personal info found on the client table.

I'm no SQL expert, so my query syntax for sql is probably way off, but does anybody have any suggestions for how I might write a query for this?

Something like,

INPUT FOR EACH [client_id in .csv column A] [address_info in .csv column B] TO clientAddress.table

That's how I imagine it would work in my head anyway. I tried searching around but can't find anybody with a situation similar enough to mine.


texts: Is outputting to both a result set and a temporary table possible?

texts:
I have a stored procedure that I need to both output the result set to the user and use a piece of information in the result set to possibly output other result sets.

Here is a basic schema of what I have

create table FooQueue(FooId int, IsQueued bit)
create table Foo(FooId int primary key, BarId int, FooData varchar(50))

create table BarQueue(BarId int, IsQueued bit)
create table Bar(BarId int primary key, BarData varchar(50)

Here is a very simplified version of the stored procedures.

create procedure GetFoos as
begin
    select FooId, FooData
    from Foo
    where FooId in (select FooId from FooQueue where IsQueued = 1)

    update BarQueue
    set IsQueued = 1
    where BarId in (select BarId
                    from Foo
                    where FooId in (select FooId from FooQueue where  IsQueued = 1))

    exec GetBars

    delete FooQueue where IsQueued = 1
end
GO

create procedure GetBars as
begin
    select BarId, BarData
    from Bar
    where BarId in (select BarId from BarQueue where IsQueued = 1)

    delete Bar where IsQueued = 1
end
GO

And the first procedure is called like the following

update top 10000 Foo set IsQueued = 1
exec GetFoos

I am about to modify a large number of the stored procedures so it does not hit the Foo table twice to something like the following

alter procedure GetFoos as
begin
    select FooId, BarId, FooData
    from Foo
    into #t
    where FooId in (select FooId from FooQueue where IsQueued = 1)

    select FooId, FooData from #t

    update BarQueue
    set IsQueued = 1
    where BarId in (select BarId from #t)

    exec GetBars

    delete FooQueue where IsQueued = 1
end
GO

Would this be the correct approach to doing this or is there a better way to both return the resultset (without BarId included) and update the 2nd table based on the returned rows?


texts: Point PostgreSQL on Ubuntu to a new datadir

texts:
I have a new Ubuntu 14.04 install on a new drive, with the old main drive containing a PostgreSQL install from the previous Ubuntu 14.04 install.

I've been trying to move the PG dbs from the old location (/olddrive/var/lib/postgresql/9.3/main) to a new one on the new drive (/newdrive/postgresdbs), but haven't succeeded.

This is what I've done:

sudo service postgresql stop  # stop PostgreSQL

sudo pg_dropcluster 9.3 main  # remove the default cluster created during the new Ubuntu install

sudo pg_createcluster -d /newdrive/postgresdbs/ 9.3 main  # recreate the default cluster in the new location (pg_createcluster is a wrapper for initdb on Debian/Ubuntu)

sudo cp -R /olddrive/var/lib/postgresql/9.3/main/base/nnnnn /newdrive/postgresdbs/base/  # copy across each of the data directories from the base dir in the old location to the base dir in the new location, where nnnnn is a number

sudo chown -R postgres:postgres /newdrive/postgresdbs/  # set postgres to be the owner of the new location

sudo service postgresql start  # start PostgreSQL

This seems as if it should work, and PostgreSQL starts fine, but I have to recreate my superuser, and none of the data tables show up - there's just the postgres table there.

I'm now wondering if this is even possible.  If not, is there any way to access the data tables from the old location?  I could initialise everything from a backup, but that may not be entirely up-to-date (and in any case, where's the fun in that?).

I did look at tablespaces, but would they work here, and would I have to give the tablespace name against every query I ran on tables in the old location?

EDIT after Craig's answer:

For reference, the commands I used to implement Craig's answer were:

sudo service postgresql stop
sudo pg_dropcluster 9.3 main
sudo pg_createcluster -d /newdrive/postgresdbs/ 9.3 main
sudo cp -a /olddrive/var/lib/postgresql/9.3/main/. /newdrive/postgresdbs/
sudo chown -R postgres:postgres /newdrive/postgresdbs/
sudo service postgresql start


texts: Occasional error: Could not obtain information about Windows NT group/user 'domain\user', error code 0x54b

texts:
I have a query that will impersonate as a specific domain user to send email with attachment. This impersonation is required as it needs to read a file at some other server to be able to attach the file to the email.

The query is as follows

EXECUTE AS login='domain\user' -- To impersonate
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profile_name',
    @recipients = @recipient,
    @copy_recipients = @cc,
    @blind_copy_recipients = @bcc,
    @subject = @subj,
    @body = @mail_body,
    @body_format = 'HTML',
    @from_address = 'email@address.com',
    @file_attachments = @files;
REVERT

Sometimes I get error when running the query with error message as:

Could not obtain information about Windows NT group/user 'domain\user', error code 0x54b.

The weird thing is that, if I execute this query multiple times, it will work and it will send out the email.

What could possibly be wrong?

SQL Server version: 11.0.2100


texts: contradiction of 3rd normal form on address table

texts: I have an Address table and its columns are address_id, address_1, address_2, city, state, country, pin code .Its primary key is address_id . According to 3rd normal form no non-key attribute should depend on other non key attribute. But in this table the non key attribute state is depending on other non key attribute country that means if we know state we don't really require address_id(primary key) to find which country it belongs. So, how should i design the Address table so that it follows 3rd normal form. Also, is 3rd normal form really needed to be applied on database tables? I have attached the image of Address table schema.


texts: Insert new row with possible minimum PRIMARY ID key

texts:
For example, I have table like this:

ID    |    name
===============
1     |   BMW
4     |   MERCEDES
12    |   FERRARI

for example, i add a new row, and i think that ID will be 17, right?

questions:
1) i want the newly inserted ROW's id be not 17, but i.e. 2 (because  it is available). How to achieve that, without making query too complex or  performance-heavy?
2) I ask that, because i my table have millions of records, with much gaps and wanted to fill that gaps, instead of increasing the ID to billions. If that is not a performance problem, then ok, i wont need a solution.


texts:
I'm trying to make moodle work on my system, so i need to change bin_log format to MIXED or ROW.

But, i'm having a lot of problems doing that. Any changes i try to do don't work;

MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format'
    -> ;
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format'
    -> ;
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

And i've also set binlog_format=row on my.cnf.
One weird thing i noticied is that it seems that the basedir is wrong. the mariadb basedir is /etc/mysql, but it shows /usr as basedir:

MariaDB [(none)]> SHOW VARIABLES LIKE 'basedir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir       | /usr  |
+---------------+-------+

Anyone knows what the problem can be?


texts:
I am hacking around trying to write a script to restore a backup from a known folder location.  The location will only ever contain one backup file.  I have come up with this so far, but I don't know how to get the value from the temp table into the restore database command.

CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER)

INSERT INTO #tempFileTable
EXEC xp_dirtree 'S:\', 1, 1

SELECT TOP 1 FName FROM #tempFileTable

DROP TABLE #tempFileTable

DECLARE @DBName varchar(255) = 'DatabaseName'

RESTORE DATABASE @DBName FROM  DISK = ??? WITH  FILE = 1

GO


texts: MySQL Cluster -- Storing table data on data nodes

texts:
I have setup MySQL Cluster with the MySQL server node and the Management node on a single server, and 2 data nodes (each on a different server for 3 servers in all). After installing, I run ndb_mgm and run SHOW at the prompt and everything seems to look OK, with the following headers.

[ndbd(NDB)] 2 node(s)
[ndb_mgmd(MGM)] 1 node(s)
[mysqld(API)]   1 node(s)

When I run mysql -uroot on the mysql server and create a database and populate a table with data I notice that the database directory and table files are being created in the local datadir, not the datadir on my Cluster's data nodes.

I have set /var/lib/mysql-cluster/config.ini to point at the data nodes and their datadir. The /etc/my.cnf file looks like this on all 3 servers. I have hidden the DNS, both are the same

[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine
ndb-connectstring=domU-xx-xx-xx-xx-xx-xx #location of management server

[mysql_cluster]
# Options for ndbd process:
ndb-connectstring=domU-xx-xx-xx-xx-xx-xx #location of management server

Why is the mysql table data files being stored locally? Shouldn't the data files (.ndb and .frm files) be on the data nodes only?

Thanks!


texts: Oracle Datapump between different database releases

texts:
I need to transfer schemas from a Oracle 12.1.0.2 Database to a Oracle 11.2 XE Database. Accodring to

Oracle Docs:

  On a Data Pump export, if you specify a database version that is older
  than the current database version, then a dump file set is created
  that you can import into that older version of the database. For
  example, if you are running Oracle Database 12c Release 1 (12.1.0.2)
  and specify VERSION=11.2 on an export, then the dump file set that is
  created can be imported into an Oracle 11.2 database

.

I used the following expdp command:

expdp system/XXXX directory=name dumpfile=dump_24.8.2018.dp consistent=y version=11.2 logfile=dump_24.8.2018.log schemas=name1,name2

The impdp failes with errors that the dump has version 12. Any ideas whats wrong?


texts: I want to implement a zip based search in mysql database, I am populating the location column in my database based on a drop-down where in I have predefined zip codes, now if a user wants to search for a commodity which is in the radius of 75 miles of his present location, I want ideas as how and which is the best way to accomplish this. I am new to mysql so a detailed answer would be much appreciated. I would be much glad if I am given some example too.


texts:
Can some one help me to figure out the equivalent function to decode in SQL Server.

Here is the statement which I used for Oracle and I want it for SQL Server. I tried but not able to get it.

UPDATE [SCHEMA].TLS_FAC_PRC_ONG
SET FPO_RID_OWNER = decode(FPO_RID_OUTSTANDNG,'NONE',FPO_PID_FACILITY,NULL,FPO_PID_FACILITY,FPO_RID_OUTSTANDNG),
FPO_CDE_OWNER = decode(FPO_RID_OUTSTANDNG,'NONE','FAC',NULL,'FAC','OST');


texts: Mysql - unknown variable 'table_cache=64'

texts:
I use Mamp Pro 3. I upgraded Mamp's MySql to 5.6.24 using this script https://gist.github.com/tobi-pb/b9426db51f262d88515c.

After that I ran the Mamp Pro and MySql cannot be started. So I looked the mysql_error.log and found the following error:

2015-06-15 01:24:55 13139 [ERROR] /Applications/MAMP/Library/bin/mysqld: unknown variable 'table_cache=64'
2015-06-15 01:24:55 13139 [ERROR] Aborting

After I google about this error, I get to know that I have to delete 'table_cache=64' variable from my.cnf (MySql Configuration) file. So I deleted this variable from /Applications/MAMP/tmp/mysql/my.cnf file.

But the problem is that the same error still occurs when I start the MySql again. So I looked at the my.cnf file, 'table_cache=64' re-appears automatically again. I deleted it again but it automatically appeared again when I start the MySql.

How should I solve this 'table_cache=64' problem?


texts: Large Identifier Uniqueness Across Table

texts:
We use NEWSEQUENTIALID to generate uniqueidentifier as table ID.
Issue is raised after server reset as the seed of it is modified and sequentiality breaks.
Is there another suggested solution to achieve large identifier uniqueness for a table?
Thank you


texts: What does Oracle impdp do when a name clashes with a materialized view or synonym?

texts:
I have a production Oracle database, and I want to export tables, data, views, materialized views, synonyms, etc, to import into a development Oracle database. Actually, this is part of my job, and we have a script in place for doing this, which calls expdp and impdp. However, I'm experiencing some issues now, because there have been a lot of recent structural changes to the production database that seem to be causing problems in conjunction with the parfile being passed to impdp. I should say that I'm quite new to expdp and impdp.
I understand about TABLE_EXISTS_ACTION and the 4 options SKIP | APPEND | TRUNCATE | REPLACE, but how can I deal with a situation where a table name exported from the source database clashes with a materialized view name in the destination database, or vice versa? What about when the name belongs to a table in one database and a synonym in the other - what does impdp do in that case, or how can/should I deal with that? Really I don't care about any existing tables, views, materialized views or synonyms in the development database; if impdp tries to import some object that was exported from the production database, I just want it to be created, and any object whose name clashes can just be dropped basically.
Also, I don't fully understand what actually happens when you export/import a materialized view from one database to another. A materialized view has a definition - so, is the actual data in the source materialized view exported, or just the definition, and the destination materialized view is refreshed according to the definition?


texts: Why MongoDB collections are dropped automatically?

texts:
I have installed mongodb 3.6 standalone instance and created some collections through insert query in mongo shell. My app points to the db server, the app creates some new collections, it reads, writes and so on. This is running for more than two months. If we restart the instance we can see in mongodb collections which are created through insert query are lost. Now we can see the same behavior in other environments. Also, while analyzing this problem, we found that during mongodb server start there was a error saying "mongodb failed to create WiredTiger bulk cursor: Device or resource busy". Any idea what that error line implies?

The below messges are found in mongodb log file during server shutdown

**2018-08-28T12:44:46.138+0530 I CONTROL [signalProcessingThread] got signal 1 (Hangup), will terminate after current cmd ends**

**2018-08-28T12:44:46.138+0530 I NETWORK [signalProcessingThread] shutdown: going to close listening sockets...**

**2018-08-28T12:44:46.138+0530 I NETWORK [signalProcessingThread] removing socket file: /tmp/mongodb-27017.sock**

**2018-08-28T12:44:46.152+0530 I FTDC [signalProcessingThread] Shutting down full-time diagnostic data capture**

**2018-08-28T12:44:46.158+0530 I STORAGE [signalProcessingThread] WiredTigerKVEngine shutting down**

**2018-08-28T12:44:46.265+0530 I STORAGE [signalProcessingThread] shutdown: removing fs lock...**

**2018-08-28T12:44:46.265+0530 I CONTROL [signalProcessingThread] now exiting**

**2018-08-28T12:44:46.266+0530 I CONTROL [signalProcessingThread] shutting down with code:0**

And during server startup the below messages are found in the log file,

**2018-08-28T14:19:39.256+0530 I STORAGE [thread2] createCollection: config.system.sessions with generated UUID: e981a966-4f87-45a9-9adb-6b7e2fdb8803**

**2018-08-28T14:19:39.277+0530 I INDEX [thread2] build index on: config.system.sessions properties: { v: 2, key: { lastUse: 1 }, name: "lsidTTLIndex", ns: "config.system.sessions", expireAfterSeconds: 1800 }**

**2018-08-28T14:19:39.277+0530 I INDEX [thread2] building index using bulk method; build may temporarily use up to 500 megabytes of RAM**

**2018-08-28T14:19:39.278+0530 W STORAGE [thread2] failed to create WiredTiger bulk cursor: Device or resource busy**

**2018-08-28T14:19:39.278+0530 W STORAGE [thread2] falling back to non-bulk cursor for index table:index-12--7471022903774062594**

**2018-08-28T14:19:39.278+0530 I INDEX [thread2] build index done. scanned 0 total records. 0 secs**


texts:
Every second day, I run into an OOM problem.

  Mar 21 17:31:33 iZ23h7fvujwZ kernel: Out of memory: Kill process 60346 (mysqld) score 943 or sacrifice child
  Mar 21 17:31:33 iZ23h7fvujwZ kernel: Killed process 60346, UID 498, (mysqld) total-vm:406039136kB, anon-rss:64936040kB, file-rss:5180kB

Below is my.cnf:

#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
prompt="\\u@\\h \\D \\R:\\m:\\s [\\d]>
#pager="less -i -n -S"
no-auto-rehash

#[mysqld_multi]
#mysqld = /usr/local/mysql/bin/mysqld_safe
#mysqladmin = /usr/local/mysql/bin/mysqladmin
#log = /opt/mysql/mysqld_multi.log

[mysqld]
#misc
user = mysql
basedir = /opt/mysql_3306
datadir = /data/mysql_3306
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 1
max_allowed_packet = 1073741824
group_concat_max_len = 102400
#thread pool
thread_handling = pool-of-threads

#timeout
interactive_timeout = 3600
wait_timeout = 3600

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 40000
max_connect_errors = 10000

skip-name-resolve = 1
#logs
innodb_print_all_deadlocks = 1
log-output=file
slow_query_log = 1
slow_query_log_file = /data/mysql_3306/log/slow.log
log-error = /data/mysql_3306/error.log
log_warnings = 2
pid-file = /data/mysql_3306/mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
#binlog-do-db=db_use
#binlog-ignore-db = mysql
replicate_wild_do_table=db_use.%
replicate_wild_do_table=BBS.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
slave-skip-errors = all
#binlog
binlog_format = mixed
server-id = 1
log-bin = mybinlog
binlog_cache_size = 3072M
max_binlog_size = 2048M
max_binlog_cache_size = 3072M
sync_binlog = 0
expire_logs_days = 10

#relay log
#skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

#buffers & cache
table_open_cache = 4096
table_definition_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 1073741824
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_repair_threads = 1
log_bin_trust_function_creators = 1

#innodb
innodb_buffer_pool_size = 35G
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
innodb_additional_mem_pool_size=20M

Server details

CPU : 16
MEM : 62G

Server version:  Ver 14.14 Distrib 5.6.20-68.0, for Linux (x86_64) using  EditLine wrapper

Buffer pool and memory

Total memory allocated 38461767680; in additional pool allocated 0
Total memory allocated by read views 5120

We find memory allocated is 38461767680, but MySQL use memory is more and more ,never free. and when MySQL OOMs it uses total-vm:406039136kB, anon-rss:64936040kB, is more than 38461767680. Is there a memory leak? We can use flush tables free 6 g.

Any idea to fix this? Is my buffer_pool_size is too big in my.cnf?


texts: Large cost change in execution-plan why?

texts:
I had a query change execution plans for an unknown reason.  The stats were gathered about 24 hours before the change and the rate of data increase has not changed over the period in question. Here is the original GOOD execution plan:
GOOD   return about 428 row per run

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9)
SORT (ORDER BY) (Cost=9 Cardinality=1 Bytes=82 Time=1)
TABLE ACCESS (BY INDEX ROWID) OF DE.MY_TABLE (TABLE) (Cost=8 Cardinality=1 Bytes=82 Time=1)
INDEX (RANGE SCAN) OF DE.MY_TABLE_MY_COL_01_DATE (INDEX) (Cost=3 Cardinality=6 Time=1)

Here is the UGLY plan, FTS on 73 million rows spinning the CPU near 100% still returns about 400 rows

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4911)
SORT (ORDER BY) (Cost=4911 Cardinality=32894 Bytes=2697308 Time=59)
TABLE ACCESS (FULL) OF DE.MY_TABLE (TABLE) (Cost=4278 Cardinality=32894 Bytes=2697308 Time=52)

After rebuilding the index, creating a covering index and that not working we finally added a hint to use the original index and that worked well. This restored previous timings.  Working well the query runs in 22ms.  The Ugly plan ran at about 14 sec.

After the query hint this is the query plan:

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=56571 Cardinality=13697 Bytes=1164245)
SORT (ORDER BY) (Cost=56571 Cardinality=13697 Bytes=1164245)
TABLE ACCESS (BY INDEX ROWID) OF MY_TABLE (TABLE) (Cost=56297 Cardinality=13697 Bytes=1164245)
INDEX (RANGE SCAN) OF MY_TABLE_MY_COL_01_DATE (INDEX) (Cost=521 Cardinality=62447)

Notice the Cost and Cardinality of the 1st and the last plans both using the same index.  In the 1st the Cardinality is wrong (1).  The query returns a pretty consistent 400 rows. In the last plan the Cardinality is 13697.  That's wrong too.  But look at the Cost of the last plan 56571, no wonder it took a hint to force the use of the working index.

My research is pointing to a Statistics problem. I verified that the GATHER_STATS_JOB is running. I've used the dba_tab_stats_history table to verify the stats are actually updated on the table I'm interested in.

So my questions are: Why would the Cardinality and Cost of the query change so dramatically?  If it is how the statistics are gathered (I am using gather_database_stats(auto)) where should I start reading what Stats gathering options to use?

If it is unlikely the statistics where else should I look?

I'm running Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

EDIT NEW PLAN 03/21/2018
The latest status is the system no longer considers the hint index expensive.
Here is the latest plan detail:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |    41 |  3444 |   998   (1)| 00:00:12 |
|   1 |  SORT ORDER BY               |                        |    41 |  3444 |   998   (1)| 00:00:12 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| MY_TABLE               |    41 |  3444 |   997   (1)| 00:00:12 |
|*  3 |    INDEX RANGE SCAN          | MY_TABLE_MY_COL_01_DATE|  1128 |       |    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FO"."STATUSID"<6800 AND "FO"."PARENTID" IS NULL AND "FO"."DELETED"<>1)
   3 - access("FO"."MY_COL_01"=:AGENCY AND "FO"."EVENTDATE">SYSDATE@!-:TIMENUMBER AND
          "FO"."EVENTDATE" IS NOT NULL)


texts: TEXT data migrate from Sybase to SQL Server

texts:
I'm working on migrating Sybase ASE 15.7 SP134 database to SQL Server 2008R2, using SSMA for Sybase 2014.

I'm facing an issue where a table in SybaseDB has 2 columns with the data type TEXT. The SQL Server table with datatype "text" is mismatching on Japanese data characters.

Questions are:

How can we keep same text as Sybase while migrating data?
How to keep Non-ASCII characters as it is during migration?

Please comment if more information can be provided.


texts: DB Structure: one table to hold all domain values?

texts:
One database I recently start working with was designed with one main table containing all possible values, and foreign keys in other tables are specifying the nature of each of those items.

CREATE TABLE entity
(
     indexid INTEGER PRIMARY KEY NOT NULL,
     entitytype INTEGER,
     entityname VARCHAR,
);

For example, this main table contain a field named 'entityname' possibly containing records such as:

the upc code of the item
the short name of the item
the long name of the item
the location of the item
the vendor for this item
the quantity in stock
(and many others)

Each record in this table got a entitytype number as a foreign key referring in another table what each records mean, such as

entitytype=10 mean that this entity is a UPC code type, and then

entitytype=12 mean that this entity is a vendor name type, etc...

Obviously there is dozen of such types for entities.

I feel like it is a bit unintuitive. There is this post here (See One table to hold all domain values section) where the author suggest the approach as I expressed it in my example may not be the best way to do this, and I would tend to agree with his opinion on the matter.

I also think it is difficult to make efficient queries, and I feel it is not how this should be done. I would rather have more tables and keep the whole more intuitive. I have a time window where I could redesign this and was thinking about long term and best practices.

So can this approach be considered a good practice in the long term and I just need to deal with it?


texts: Replicating on-premise SQL Server instance with hundreds of databases to an Azure SQL Managed Instance

texts:
I am wanting to replicate an entire SQL Server 2016 on-premise instance of databases (300+) to an Azure SQL Managed Instance for redundancy of a read-replica in case of on-premise downtime. It appears as though I can use Azure Database Migration Service in order to deploy multiple databases, but this seems to imply that this is a one-time migration.

This will not be a one-time migration, and I am wanting to perform transaction replication with a Publish (on-premise SQL) and Subscriber (Azure SQL). Microsoft has outlined this method, but Publications only allow for a single database to be selected. I would like to explore publishing the full instance of databases, and shipping the logs at a particular interval.

Each of these databases share the same schema, so merging the data into a single database poses problems surrounding application configurations in the event we need to fail-over for read purposes. This is also a temporary solution until we are able to replicate our VMware cluster for Disaster Recovery, and our transactional throughput is on the lower side and is constrained to set working hours.

Is my only option to create publications for each and every database, or can the Azure Data Migration Service be extended for replicating at regular intervals?

Edit: Another option I've been toying with is creating a single database with each individual tenant as a security schema and file group, and developing an ETL pipeline to pass the tenants to these matching security schemas. In essence I would mimic the database-per-tenant through tenant-per-schema, allowing me to only need to push a single database to the Azure SQL. This might cause more headaches than it would solve, though. Does anyone have experience with this at scale?


texts: How does SQL Server control which users get automatically added to a new database?

texts:
I re-create my Dev Database with a Visual Studio DB Project twice a week.

Today I fixed my permissions to be Active Directory group based rather than giving individuals permissions.

After I re-ran my build I noticed that one domain/windows use was still in there.  I searched all through the DB project and could not find this user in there anywhere.

So to test, I created a different database (ie MyDummyDb).  Sure enough the user was added to that db too.

This user does not even have a valid login (I changed the logins to be AD Group based too).

So I have to assume there is a server level setting that controls which users get automatically added to a new database.

Is this right?  Does anyone know where this setting is?


texts: Spool query to file without output on screen

texts:
Using spool on Oracle sqlplus correctly
sends the results of my queries to a file.

But I also see output on the screen.

Is it possible to see only the query without output on screen, and result on file? (This feature is the default on PostgreSQL psql client program).


texts: Exporting to excel from OPENROWSET with cell formatting

texts:
I am trying to generate a report from SSMS to Excel, using OpenRowSet.

All is working fine, still I have to export a specific column with a barcode font.
I already have the column calculated from SSMS, and being exported to Excel. I also have installed the right font and I can easily use it in Excel.

I am using a template that I have designed and the designated column in Excel is already set to be using the needed barcode font, but when the export is done, the column is showing the barcode value, but not the barcode itself.

Any idea?


texts:
There are two servers on co-location. First is Windows Server Standard 2008 R2 SP1. Second is Windows Server 2012 + Sql Server Standard 2012 SP1.

First server has a task with a report against tables in Server B. Report runs via sqlcmd11. Normally, it takes 3-4 seconds. But sometimes it hangs in the middle of data receiving (even, If I redirect output to console) and around at 20 seconds after start connection is killed with these errors:

HResult 0x2746, Level 0, State 10
TCP Provider: An existing connection was forcibly closed by the remote host.

HResult 0x2746, Level 0, State 0
Communication link failure

Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total     21107  avg   21107.00 (0.05 xacts per sec.)

Sqlcmd runs script from file. Command line is:

"%ProgramFiles%\Microsoft SQL Server\110\Tools\binn\SQLCMD.EXE" -S "ip,port"
    -U username -P password -u -i script.sql -o output.html -m-1 -p

Query has header:

declare @today datetime = cast(cast(getdate() as date) as datetime)
declare @yesterday datetime = dateadd(day, -1, @today)
declare @days15 datetime = dateadd(day, -15, @today)
declare @days10 datetime = dateadd(day, -10, @today)

Then is contains 5 pairs:

print N'Report header'
select <report body>

Queries doesn't use table variables or any temp table. I read about tcp chimney and other tcp options. Disabling them on both sides didn't help.

Sql Server logs don't have messages about killing my process.


texts: Database Replication/Mirroring for Reporting Purposes

texts:
I am pretty green to the administration end of databases, but I was hoping you guys could direct me on the right path.

I have Server 1, 2, and 3. They both identical versions of SQL Server, with an application. Within each instance of MS SQL Server, I have roughly 10 databases in each server. My goal is to replicate/mirror the data to a Server 4 so that reporting and alerts can be run off of the 'live' data without effective performance of the application running on Servers 1,2,3.

To be honest, I don't even know where to start:

Is this at all possible?

Is this a common practice?

Is there any type of recommended reading material?


texts:
Mysql Version: 5.5.62 and 5.6.10
lib version: 2.18.1
CREATE TABLE IF NOT EXISTS temp_user (
  email         varchar(255),
  name          varchar(255)  NOT NULL,
  surname   varchar(255)  NOT NULL,
  PRIMARY KEY (email)
) ENGINE=InnoDB;

When I insert a new user with a wrong query:
insert into temp_user (email) VALUES ('email@example.com');

If I execute in Mysql IDE (DBeaver) it throw an error: Errore SQL [1364] [HY000]: Field 'name' doesn't have a default value
But the node library insert the record with null in name and surname.
await pool.query(`insert into temp_user (email) VALUES ('email@example.com')`);


texts: issues with encoding and pg_dump/restore between windows and linux

texts:
I have a dmp file I have created using pg_dump encoded in WIN1252.  What I would like to do is import it using pg_restore to a database on a Linux machine.  The problem is that on the windows machine postgreSQL recognizes the encoding WIN1252 but not the CP1252 encoding (they both are identical just different names for the same standard).  On the Linux machine, postgreSQL recognizes CP1252 but not WIN1252.  So when I try and do a pg_restore I get an error because WIN1252 is not recognized as a proper encoding.  Also, if I try to do a pg_dump on windows and specify the encoding as CP1252, I get a similar error.  I would like to stick with this encoding format so doing a pg_dump in utf8 or something is not something I would like to do.  Any help would be appreciated!

EDIT:
Actually, upon further investigation, postgreSQL does not understand cp1252.  The iconv linux command used to convert a text file from one encoding to another understands cp1252 and not win1252 and thats where I got it from.  On the Linux machine, I still cannot set the client encoding to WIN1252.  This is the error message:

FATAL:  conversion between WIN1252 and LATIN1 is not supported

The variable client_encoding was originally set to UTF8 on the Linux machines so I dont know where LATIN1 is coming from.  Unless WIN1252 is not supported so postgres tries to convert it to latin1 because that is the closest thing to it and fails at achieving that due to some character miss-match?


texts: SSMS 2014 does not launch Graphical Execution Plan from clicking hyper-linked query plan - ShowPlanXML

texts:
I am working with an older version of Microsoft SQL Server Management Studio 2014. I run the following query:

SELECT TOP 10 Q.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan( qs.plan_handle ) Q
WHERE Q.dbid = DB_ID( 'Northwind' )

When I click on one of the hyperlinked query_plans (ShowPlanXML=...), SSMS 2014 opens the link in a new text window in XML format. As I understand it, SSMS is supposed to open the link as a graphical execution plan.

Is there any way to configure SSMS to automatically open the ShowPlanXML link as a graphical execution plan without third party software and without updating the version I have of SSMS 2014?


texts: Where does Oracle store message properties for AQ messages?

texts:
We create messages with various properties, like this:

    message :=  sys.aq$_jms_message.construct(0);
                  message.set_string_property('a', v_a));
                  message.set_int_property('b', v_b);
                  message.set_int_property('c', v_c);
                  message.set_string_property('d', v_d);
    DBMS_AQ.ENQUEUE(queue_name         => SOME_QUEUE,
                    enqueue_options    => queue_options,
                    message_properties => message_properties,
                    payload            => message,
                    msgid              => message_id);

And now I'm wondering where the message properties are actually stored.


texts: How can I store least used table files to a slower drive?

texts: Is it possible to separate the least used and most frequently used data files on to separate disks when using InnoDB?


texts: How to check the health of a SSAS database? And of its backups?

texts:
regarding backups of the SSAS databses, based on the following behaviour:

Backup on an empty SSAS database hangs

Is there a way to check whether or not the SSAS backup is healthy?

I would accept powershell, AMO, anything, any tool to get this done.

Plus after backing it up, how would I know that the backup file is not corrupted as well?

If yes, then is there a way to fix it, without rebuilding the cubes from scratch?

Our cubes here take a whole lot of processing and time to get rebuild, I am trying to figure it out a way to avoid that, in case the of a disaster recovery scenario.

I found the same question below, but I could not make it work. If at least there would be an example.

How to verify a SSAS Backup and how to perform Integrity Checks in SSAS?


texts:
The Dba of our company has created views for the tables for all the users.
We have 230 users, he has created 230 views for each tables!
I want to replace this by public synonym and grant select to the users.
Example:
Create public synonym emp to scott.emp
grant select on scott.emp to public;
When I query synony after droping view it works.
Without dropping the view If I query emp oracle does the select on the view.
When we have a view and synony in the same name, what is the hirarchy between this?
Is there a workaround for this problem.
Because when I modify a table I have to correct 230 view scripts!


texts:
I am not able to understand how to achieve a hierarchy accessing 3 tables. That is, I have Table1, Table2 and Table3 where:

Table1 (ID_table1, name_c, size)

Table2 (ID_table2, ID_table1, name_l, size)

Table3 (ID_table3, ID_table1, ID_table2, name_d, size)

How do I make a hierarchy with all this information? I've looked for it but only found solutions with information into a single table.

*EDIT: I edited the information in the tables because they do not express myself


texts: SSIS 2012 Ftp parallel/asynchronous multiple file download

texts:
Any help would be appreciated with either scenario.
if
Are there any settings available to download multiple files in parallel from an FTP server?
else
If not, I guess I'll just roll my own via C# script source component using: List Directory Contents with FTP, FtpWebRequest Class, and C# Multiple Download from FTP using parallel task - Duplicate Download issue to get going.
other details

SSIS 2012 saving to a local Windows Server 2012 directory
100 + ascii text files
6 sibling folders
Filename convention: AAAA_YYYYMMDD.txt
File size ranges from 5KB - 15MB
Currently only able to download one file at a time
Run once per day manually at this stage via SSDT (still prototyping/developing, so not on a production system, yet, but I plan to have Sql Agent running the packages in the future)
Downstream packages to load text file data into data warehouse staging tables (if script task is used then perhaps the response stream buffer can go into a multicast with 1 buffer stream writing to text files, while the other buffer stream writes to staging tables?)


texts: SQL Server 2008 R2 Remote Connection issue

texts:
I have installed SQL Server 2008 R2 in mixed mode, but currently issue is I can't use SQL through ip nor ip with port, even I have enabled TCP/IP and open port in firewall too.

I tried to telnet 127.0.0.1 1433 to check whether its working or not but its not connecting
I tried almost every possible things but failed in each.

How do I get this to work?


texts: What are the best practices of creating new SQL Server instance on SQL Server Clustering 2008 R2 and migrating a database with high transactions?


texts: SQL Server - Stopping a server side trace

texts:
I want to setup a server side replay trace and scripted out setting up the trace. However I want the trace to end either after 12 hours or once 10 1GB files have been created. Normally with a max file count, the system deletes the oldest file and then creates the new one. In my situation this would end with me having Files 2-11 instead of the desired 1-10.
I have space limitations and need to replay all the steps from start to finish. I could just set the file size limit to 10GB. I think there is a 1GB limit on file size, but this could just be a limit in the GUI.

Any ideas on how I can stop the script once it reaches 10 files?

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
declare @maxfilecount int

set @DateTime = DATEADD(HOUR,12,GETDATE())
set @maxfilesize = 1024
set @maxfilecount = 10

exec @rc = sp_trace_create @TraceID output, 2, N'D:\Output\Trace', @maxfilesize, @Datetime, @maxfilecount


texts: Restore database in workbench from a Linux file

texts: We have a database on a Linux server. Instead of restoring using command line, can I restore it from Workbench? My backup file is in a Linux data directory.


texts: Mysql FLUSH LOGS in live server with replication

texts:
I need to run the slow query and unindexed query logging for some time on a live production db with replication. Changing GLOBAL const value sounds like a good solution. This article also provides good read on that..

The problem is that I need to run FLUSH LOGS; on the master / slave. And I'm not sure how that will effect the replication. Can someone tell me the impact of running FLUSH LOGS; on live master and on live slave.

Thanks


texts: T-SQL performance tuning - Query running slower

texts:
I've been tasked by the dev manager as a DBA to look into performance tuning the code as attached in the link provided below.

Basically, this is a stored proc that uses a CTE and then joins the CTE with various tables. However, the stored proc execution is surpassing way more than 9 minutes and I need to basically bring the execution time down as much as possible either by rewriting the code or adding indexes.

Looking at the actual execution plan it seems that the most expensive operation seems to be "sort" operation incurring 97% of the overall cost. We have a non clustered index on the sorted column but the sort order is asc and the code does a desc.

I am thinking of changing the sort order in the index to desc but again it uses a row_number() function which requires an order by to be explicit.

Is there a way I can reduce the cost or rewrite the query or maybe add an index or two to reduce the execution time?

;WITH cte_MaxStatus as
(
       SELECT
              QueuedTxUpdateID, ObjectID, QueuedAt, ErrorMessage,fkSfUpdateStatus, fkSfUpdateAction, ObjectType, ProcessedAt,
              rn = ROW_NUMBER() OVER(PARTITION BY ObjectType, ObjectID ORDER BY QueuedAt DESC)
       FROM dbo.SfQueuedTxUpdates
)

SELECT   MOT.ObjectName,/*mot.Description, */ ObjectID, QueuedAt, ErrorMessage, ua.SfUpdateAction, us.SfUpdateStatus, cte_MaxStatus.ProcessedAt
FROM cte_MaxStatus
       LEFT JOIN dbo.SfUpdateAction UA ON cte_MaxStatus.fkSfUpdateAction = ua.SfUpdateActionID
       LEFT JOIN dbo.SfUpdateStatus US ON cte_MaxStatus.fkSfUpdateStatus = US.SfUpdateStatusID
       LEFT JOIN DBZ.dbo.ModuleObjectTypes MOT ON cte_MaxStatus.ObjectType = MOT.ObjectTypeID
WHERE fkSfUpdateStatus <> 3
              AND cte_MaxStatus.rn = 1

Full SQL Script

SQL PLAN


texts: How can I see what called a stored procedure?

texts:
Is there any way we can find out from where a stored procedure was executed?

I am calling a specific stored procedure from the web, but when I check the SQL Server profiler it showing  that entry 4 times; I am suspecting some thing is in the background is getting that method. So if possible what is the source of execution of that stored procedure will be really help to narrow down the issue?

I am using SQL Server 2012


texts: How would I run DELETE queries on a Master Without Executing Same DELETEs on a Slave?

texts:
If I want to run a query that will only execute on the master mysql server and not the slave. assuming we have an environment with replication through query logs. Could I and how?

The idea is to have some records that are archived on the slave. Lets say the master is a production website and only requires data that has been entered in the past month to operate correctly but the slave is where data from years will be saved and later analyzed to change variable on the main (production/master) server.


texts: delete rows in 3 tables with on delete cascade

texts:
For a relation 0..n between two tables (t1,t2), the mpd generates an intermediate table (t3) with two fk.
Let two constraints on the t3 table, each one with "on delete cascade", if I do:

delete from t1 where id1=$id1;

I want to delete all rows of the t3 which contain the fk id1 and all rows of the t2 which contain the fk id2.
Is it possible in one query ?
Edit: here are my tables:

create T1 (
id1 ...
...
CONSTRAINT pk_id1 PRIMARY KEY (id1) );

create T2 (
id2 ...
 ...
CONSTRAINT pk_id2 PRIMARY KEY (id2) );

create T3 (
id1 ...,
id2 ...,
CONSTRAINT pk_T3 PRIMARY KEY (id1, id2),
CONSTRAINT fk_T3_1 FOREIGN KEY (id2)
REFERENCES T2 (id2) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_T3_2 FOREIGN KEY (id1)
REFERENCES T1(id1) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE )


texts: I have a database should store sales, I created a table for products and one more table for stock I felt the relationship between them is many-to-many because a product in one stock may have different price from another stock, and one stock can have many products, so I created another table for create a relationship between these two tables by name of Stock_Product, but Now I fell it is wrong, because every time when I want to query from Sales table which contains the sales records I need to join 4 tables for selecting the details of product and stock.


texts: How to change default mongo port on a bitnami instance

texts:
I am running a bitnami instance of mongodb for our pre-production. The default port for mongo is 27017. But for security reasons i want to change it but it is not possible.

I have tried these things:

changes the port in mongodb.conf and restarted the server. It shows the below error:

connecting to: mongodb:///opt/bitnami/mongodb/tmp/mongodb-27017.sock/
2018-04-20T12:55:04.373+0530 W NETWORK  [thread1]
Failed to connect to /opt/bitnami/mongodb/tmp/mongodb-27017.sock:0, in(connect), reason: Connection refused
2018-04-20T12:55:04.373+0530 E QUERY    [thread1] Error: couldn't connect to server /opt/bitnami/mongodb/tmp/mongodb-27017.sock:27017, connection attempt failed :
connect@src/mongo/shell/mongo.js:237:13
@(connect):1:6
exception: connect failed

I restarted mongod with different port. Still i am not able to do it.

when i switch back to 27017. It comes back to normal.

Any suggestions on how to do it the right way?


texts: We have a SQL Server 2008 R2 instance that is running on a Windows Server 2008 R2 Enterprise server that has been allocated 128GB of memory. However, it maxes out at 70GB. Can anyone tell me how I can allow the SQL server to utilize the majority of its memory?? Thanks in advance.


texts: Is there a way to detect if there is an uncommitted transaction in H2?

texts:
I am using an H2 database with JDBC to create a connection with setAutoCommit(false).

I am currently programatically checking whether or not there is an uncommitted transaction, but I was wondering if there was a way to directly query H2 for information about this state.

I have found some information for other databases on this topic, but nothing so far on H2.


texts: Select historical prices for an exact datetime - MySQL

texts:
I have a price_list table where I store historical prices for each item.

columns are -  id, name, price, start_date, item_id

When a price is changed, a one new row is inserted with a current start_date. (Start_date is a datetime format).

Now I need to select prices of all items for an exact datetime. Problem is that this query is slow (1.058 sec):

SELECT * FROM (SELECT * FROM price_list h WHERE start_date <= '2013-12-15 12:14:18' ORDER BY start_date DESC) a GROUP BY item_id

Very slow is a GROUP BY. Without it it takes 0.075 sec, but each item (item_id) is there a multiple times.

bad: SELECT * FROM price_list h WHERE start_date <= '2013-12-15 12:14:18' ORDER BY item_id, start_date DESC

Please, can this query be rewritten to be quicker?


texts:
Willing to solve this problem related to a wrong value for is_media_read_only database property I did some research and tests, but in the end I couldn't sort out what exactly triggers the UPDATE of the column is_media_read_only on sys.database_files.

According to sys.database_files documentation, the column is_media_read_only should have one of two possible values:

  1 = File is on read-only media.

  0 = File is on read-write media.

With this information I did the following experiment with two different versions of SQL Server:

Microsoft SQL Server 2014 (SP3-GDR) (KB4505218) - 12.0.6108.1 (X64)
Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)

Plugged a pen-drive in my notebook (Drive E:) and created a database as follows:

CREATE DATABASE [MyDB]
 ON  PRIMARY
( NAME = N'MyDB_01', FILENAME = N'D:\DataBases\MyDB_01.mdf'),
 FILEGROUP [SECONDARY]
( NAME = N'MyDB_02', FILENAME = N'E:\DataBasesPendrive\MyDB_02.ndf')
 LOG ON
( NAME = N'MyDB_log', FILENAME = N'D:\DataBases\MyDB_log.ldf')
GO

Queried sys.database_files:

USE MyDB;
GO
SELECT file_id, name, physical_name, is_media_read_only, is_read_only, state_desc
FROM sys.database_files;
GO

This was the result:

Once the database was created I opened a CMD prompt and ran diskpart. On the utility I issued the following commands:

list disk
select disk 2
attributes disk set readonly
attributes disk

Disk 2 (my pen-drive) is now a read only media, so I expected the is_media_read_only value to change, but when I queried sys.database_files again there was no change: is_media_read_only was still 0. So I started doing random procedures with the database to see if anything would make SQL Server notice that the database was now sitting on a read only media and update the value to 1. I could achieve this in two situations:

1st: changing the database to READ_ONLY mode and back to READ_WRITE.    (it must be both actions, just one of them won't do the trick):

USE master;
GO
ALTER DATABASE MyDB SET READ_ONLY;
ALTER DATABASE MyDB SET READ_WRITE;
GO

2nd: detach and attach the database back again.

These were the two situations in which I could have is_media_read_only updated to 1 as you can see on the picture:

Now it was time to revert the pen-drive to read_write again, so I went back to diskpart and ran:

select disk 2
attributes disk clear readonly

When I changed Disk 2 to read write mode back again the is_media_read_only would only update to 0 upon a detach and attach procedure.
I even tried to restart the SQL Server instance hoping it would update the value, but no luck.

Until a detach and attach of the database SQL Server would leave me with value 1 for is_media_read_only even though the disk was no longer READ_ONLY.

Thus my questions: What exactly triggers the UPDATE of the column is_media_read_only on sys.database_files? Could the behavior shown with this experiment be a bug of SQL Server?


texts: How to improve query count execution with mySql replicate?

texts:
For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table).

And its really time consuming when it comes to million records data.
So I decided to use mySql Replicate to create a slave db for my reporting purpose. But I'm still struggling with these things:

Are you willing to duplicate a part of your db in exchange for query speed ?
Is there any way to add data to user_type column automatically in 'user_activity' table (slave db) when they are syncing?
And how to optimize database for reading purpose? (reporting data, analytics,..)

update:
this is my create sql

Table user

CREATE TABLE `library_th`.`user` (
`id` INT UNSIGNED NOT NULL ,
`username` VARCHAR(100) NOT NULL ,
`password` VARCHAR(32) NOT NULL ,
`user_type` TINYINT NOT NULL DEFAULT '0' COMMENT 'this user is created automatically by system or by them himself?' ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;

Table user_activity:

CREATE TABLE IF NOT EXISTS `user_activity` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `action` int(11) NOT NULL DEFAULT '0' COMMENT 'what activity did user do?',
  `datetime_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `user_activity`
  ADD KEY `user_id` (`user_id`);

and my count query that I'm wanna use is:

select count(ua.id) as `total`, u.user_type, ua.action from user_activity ua
inner join user u on ua.user_id = u.id
group by ua.action, u.user_type


texts:
I'm trying to change primary key on a table in Azure storage.
The plan is to create a new table, move data over and drop the old table.
The problem is that I need all the constraints names to be the same as the old ones.

So I can't create a new table until I rename old table and all the constraints on that table.
I have tried sp_rename on the constraint, but no luck:

sp_rename 'PK_dbo.ContactSessions', 'PK_dbo.ContactSessions_old', 'OBJECT'

gives me that:

Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

If I run sp_rename like that:

sp_rename 'PK_dbo.ContactSessions', 'PK_dbo.ContactSessions_old'

I get this error:

No item by the name of 'PK_dbo.ContactSessions' could be found in the current database 'testing_only', given that @itemtype was input as '(null)'.

I have seen this thread: How can I alter an existing Primary Key on SQL Azure?
and tried

create clustered index [NEW_INDEX_NAME] on [ContactSessions] ([ContactSessionId])
    with (drop_existing=on);

and got the error:

Could not find any index named 'NEW_INDEX_NAME' for table 'ContactSessions'.

Are there other options to change the index name??

I know that is possible because I can right click on the key name in SSMS and rename the key, but I need it in the script.


texts: Which primary keys must be specified when using slony PostgreSQL replication?

texts:
I'm extremely green when it comes to databases, so forgive my ignorance. I've been researching ways to replicate our production Postgres DB, and I've decided to try out Slony. Since the server is running Ubuntu 10.04, I'm stuck with Postgres version 8.4 and Slony 1.2. I've been using this guide, which has proven extremely helpful, but I don't fully understand all of what I'm doing.

My issue is that I'm not sure what I need to put in the slon_tools.conf file in the section "pkeyedtables". I listed all of the tables in our current database, and there are a total of 205. Do I need to specify each one in the "pkeyedtables" section? And if so, what format do I need to write them in? Some of the table names are like "AO_60DB71_SUBQUERY", while others are like "userassociation".


texts:
I'm trying to pass a new ID onto another procedure but keep getting a "too many parameters" error.

CREATE OR ALTER PROCEDURE dbo.Process_Insert
  @Name        VARCHAR(50),
  @IntOnly     INT,
  @enabled     BIT,
  @CustomerID  INT,
  @AllowAttr   BIT,
  @IsKit       BIT = 0,
  @Body        NVARCHAR(MAX)=NULL,
  @new_ident   INT NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Process
(Name,
 IntOnly,
 enabled,
 CustomerID,
 AllowAttr,
 IsKit,
 Body)
VALUES
(
  @Name, @IntOnly, @enabled, @CustomerID, @AllowAttr, @IsKit, @Body);
SET @new_ident=SCOPE_IDENTITY();
END;

Then in the below procedure I get "Procedure or function has too many arguments specified". I'm guessing it has something to do with the output parameter, but can't work it out.

CREATE OR ALTER PROCEDURE dbo.Customer_Init
  @CustID INT
AS
DECLARE
  @vendID INT;
EXEC dbo.Process_Insert
     @Name="NameOfEntry",
     @IntOnly=0,
     @enabled=False,
     @CustomerID=@CustID,
     @AllowAttr=False,
     @Body="test",
     @new_ident=@vendID OUTPUT;
SELECT @vendID;


texts: What to do with those invalid JAVA CLASS object type objects in SYS prior to the upgrade the oracle db from 10g to 11g?

texts:
I am ready to upgrade the oracle 10g prod db to 11g, but we have 130 invalid objects in SYS (all are belong to JAVA CLASS object_type). And from the best practice of oracle upgrade from 10g to 11g, it said should not have any invalid objects in sys or system schema. But I already ran the utlrp.sql to recompile all invalid objects script multiple times, those invalid JAVA CLASS object_type objects are persistent in SYS user. And I tried to manually recompile each of those object as doing following,

  alter java class "java class object name" compile;
  but got error:
  ORA-29521: referenced name balabala could not be found

You have any advice for either how to fix this error or can we go ahead to upgrade the db with those 130 invalid objects in the sys schema safely? Thanks!


texts:
I am very new to system and db admin stuffs. Infact, I need to have my skills more improved in those areas. Anyways, I was trying to connect to a remote oracle database and use php oci8 library to talk with that db.

I installed the oracle basic package, sdk and sql plus. For testing the connectivity I ran sqlplus command from /usr/bin which prompts for username and password, If I provide the remote db username and pass it throws an error like this: ORA-12162: TNS:net service name is incorrectly specified.

Also, once I installed oci8. But it seems like oci8 functions are not recognized since the library is not properly installed? How can I check if the library is properly installed?

Update: My OS is Mountain Lion 10.8.4,
PHP version is 5.4.10,
SQL*Plus: Release 11.2.0.3.0 Production


texts:
I'm running Postgresql 9.6 on Ubuntu 16.04. The collation order is en_ZA.UTF-8. I'm puzzled by how Postgres compares strings:

test=> select 'b' > 'B';
 ?column?
----------
  f

test=> select 'ba' > 'B';
?column?
----------
  t

test=> select 'b' = 'B';
?column?
----------
  f

test=> select 'ba' > 'C';
 ?column?
----------
 f

The response to the second query doesn't make sense to me.


texts: Associate BAK file with SQL Server database

texts:
One of our users deleted a lot of data from one of our database applications, and I need to find a way to restore it.  I have a bak file and full recovery mode, but there is a catch.

This bak file is not recognized as belonging to this database, so I am not given the option of a timeline restore.  I can restore the bak as is, but it does not contain all of the data I need.  My only other option seems to be somewhat expensive (~$2000) recovery tools which can recover the data from the transaction logs.

Is there a way to convince SQL Server that this bak file really does belong to this database?

Do I have any other options?


texts:
Why is this simple query granted so much memory?

-- Demo table
CREATE TABLE dbo.Test
(
    TID integer IDENTITY NOT NULL,
    FilterMe integer NOT NULL,
    SortMe integer NOT NULL,
    Unused nvarchar(max) NULL,

    CONSTRAINT PK_dbo_Test_TID
    PRIMARY KEY CLUSTERED (TID)
);
GO
-- 100,000 example rows
INSERT dbo.Test WITH (TABLOCKX)
    (FilterMe, SortMe)
SELECT TOP (100 * 1000)
    CHECKSUM(NEWID()) % 1000,
    CHECKSUM(NEWID())
FROM sys.all_columns AS AC1
CROSS JOIN sys.all_columns AS AC2;
GO
-- Query
SELECT
    T.TID,
    T.FilterMe,
    T.SortMe,
    T.Unused
FROM dbo.Test AS T
WHERE
    T.FilterMe = 567
ORDER BY
    T.SortMe;

For an estimated 50 rows, the optimizer reserves almost 500 MB for the sort:


texts: Spool Unicode data in sqlplus to ISO-client without character set conversion

texts:
I'm currently facing the following problem:

We have an Oracle 11g database with character set AL32UTF8, and an sqlplus-client with NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1.
Stored in the database are various non-ISO8859-1 characters.

Now, when the client connects to the database and tries to spool these special characters, a character set conversion takes place according to the client's NLS_LANG setting and the special characters are lost.

Unfortunately, changing the client-NLS_LANG isn't easily possible (legacy application - it's complicated...).

Is there an easy, or standard way to spool this data without losing these special characters?

I've thought about encoding the data in base64, spooling it, and then decoding it client-side.

But is there a better way?


texts:
I'm building a database for a local charity. The database will be filled with entries coming from different sources and updated daily.
One of the element (e.g. a car) has a relationship with many elements (e.g. car colors).
One of my friends who is helping with the project suggested to use a JSON field. I'm not a DBA and maybe I don't fully understand the benefit of using JSON over a many to many table.
I could build the cars table as follow:

Where an entry would be:
Cars
+----+--------+-------------------------------+
| id |  name  |             colors            |
+----+--------+-------------------------------+
|  1 | Fiesta | { "15": "red", "22": "blue" } |
+----+--------+-------------------------------+

Or I could build a many to many table:

Where an entry would be:
Cars
+----+--------+
| id |  name  |
+----+--------+
|  1 | Fiesta |
+----+--------+

Colors
+----+------+
| id | name |
+----+------+
| 15 |  red |
+----+------+
| 22 | blue |
+----+------+

CarsColors
+----+-------+---------+
| id | carID | colorID |
+----+-------+---------+
|  1 |   1   |    15   |
+----+-------+---------+
|  2 |   1   |    22   |
+----+-------+---------+

Personally I find the second approach would be cleaner, especially since in future we'll need to perform operations like "find all cars with color red". I know that the answer probably is "it depends" but, please, could you point me in the right direction?


texts: Filling in Dates - Postgres/Redshift SQL

texts:
I have a dates table with a "calendar_date" field and I have been trying to utilize it for when I need to fill in missing dates when an id does not have a record for a particular day AND have the daily count = 0. I cannot seem to find the proper solution for it.
Result example:

        SELECT
        id,
        date_trunc('day',created_at) as activity_date,
        COALESCE(COUNT(DISTINCT user_id),0) AS daily_count
        FROM events e
        WHERE date(date_trunc('day',created_at)) <= CURRENT_DATE - INTERVAL '1 day' AND date(date_trunc('day',created_at)) >= CURRENT_DATE - INTERVAL '90 day'
        AND name = 'visited site'
        AND user_id != 0
        GROUP BY date_trunc('day',created_at), id

Any ideas/help would be appreciated!


texts: AlwaysOn - Problem with adding secondary replicas

texts:
I installed AlwaysOn on my laptop (testlab) with three VMs, of which one is a domain controller and two are hosting SQL Server instances (planned to be one primary and one secondary replica). Everything went well until I wanted to add the secondary replica to the Availability group.

Please find a screenshot attached. I have already changed the port to 1433 in the TCP/IP settings and have also added an exception for SQL Server in the Windows Firewall. Both of the servers are in the same cluster network. Not sure what else I am missing here.


texts:
Database: Oracle 12c

I have a process that selects data from Fact Table, summarizes it and pushes it to Summary Table.

Summary table is Range Partitioned (Trade Date) and List Partitioned (File Id).

the process picks up data from Fact table (where file_id=<> for all Trade Dates), summarizes it in a temp table and use Partition Exchange to move data from Temp table to one of the SubPartitions in Summary Table (as the process works on a File Id level).

Summary table is completely refreshed everyday (100% data will be exchanged).

Before the data is exchanged at the subpartition level, statistics are gathered and exchanged along with the data.

After the process is completed, we run dbms_gather_table_stats at partition level (in a for loop - for each partition) with granularity set as "approx_global and partition".

Even though we collect stats at the global level, user_tab_statistics for the summary table has "STALE_STATS" = YES for this table, however, partition & Subpartition stats are available.

when we run a query against the summary table (for a date range of 3 years), the query spins for a long time - spiking the CPU to 90%, but never returns any data.

I checked the explain plan on the query, Cardinality is showing as 1.

I read about incremental stats, but it seems increment will work if a few partitions change - it may not be the best option in my case, where data across all the partitions change completely.

I m looking for a strategy to gather statistics on the summary table - don't want to run a full gather stats.