texts: When the master db fails, the slave db will enable the write mode. How do I synchronize the master db with the information written to the slave db?

texts:
have two database. They are db1 and db2. db1 master, slave at db2.

d1 have write/read mode. d2 have read mode.

I have a problem like this: When the master db fails, The slave db will enable the write mode. How do I synchronize the master db with the information written to the slave db after activating master db again.

I understand how master db gives error, but I have some questions:

How do I enable slave mode after master db fail?
How do I activate the master db again?
Most important: How can I synchronize the information written in slave databases with master databases.
Finally, how do I cancel the slave databases's write mode?

You can see the my.cnf file as below:

<CLASSHOST:db1 db2>
# MySQL config file (for very large systems 1G memory and up)

# The following options will be passed to all MySQL clients
[client]
port    = 3306
socket  = /u1/database/mysql/mysql.sock

# The MySQL server
[mysqld]
skip-innodb
user    = mysql
port    = 3306
socket  = /u1/database/mysql/mysql.sock

## Each processor MUST have its own server_id !!
#  Concat SITE with local SERVERID
server_id=<CFGTAG:SERVICE_SITE_ID><CFGTAG:MYSQLSERVERID>

## Performance tuning variables
key_buffer=384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache = 8

# DISABLE query_cache : it can hang tue MySQL server for 10-50 seconds.
query_cache_type = 0
query_cache_size = 0
# Try number of CPUs*2 for thread_concurrency
thread_concurrency = 8

# this holds about 500000 SpamList rows - the only x HEAP table
set-variable = max_heap_table_size=30M

max_connections=200
back_log=50
# Prevent host-cache from locking out host.
max_connect_errors=999999999

# Log options: need BINARY log, log all updates on slave processors,
#     log slow queries

log_bin=<CFGTAG:MYHOSTNAME>-bin
log_slave_updates
log_slow_queries=slow.log

# ------------------------------------------------------------------
# Replication variables BLOCK...
#           COMMON VARIABLES
# These variables control which DBs are snared by a slave processor
# db1 may not be a slave in HA mode. It MUST be a slave in DR mode
# db2 will always be a slave in both HA and DR mode.

replicate-do-db=x
replicate-do-db=xx
replicate-do-db=xxx
replicate-do-db=xxxx
replicate-do-db=xxxxx
replicate-do-db=xxxxxx
replicate-do-db=xxxxxxx
replicate-do-db=xxxxxxxx
replicate-do-db=xxxxxxxxx

# replicate-same-server-id prevents loops. FALSE is default NEVER change it.
replicate-same-server-id=FALSE

## We choose not to set these via this method
# master-host=my master host
# master-user=our replication user
# master-password=

# -----  HOST SPECIFIC replication Variables

</CLASSHOST>
<CLASSHOST:db1 db2 >
# Dispense auto-increment keys ...
#    The "skip" value is the greater of 2 or the total no of GEO sites.
auto_increment_increment=<CFGTAG:SITE_MYSQL_AUTO_INCR_INCR>
#    Each site's slice is determined by its SITE_ID.
auto_increment_offset=<CFGTAG:SERVICE_SITE_ID>

## -------- End of replication variables block
</CLASSHOST>
<CLASSHOST:db2 >
# HA only, and only for the SLAVE. We should use it on the 3rd copy, too.
# users with the SUPER privilege (repl and root) are not blocked.
read_only

## -------- End of replication variables block
</CLASSHOST>
<CLASSHOST:admin1 db1 db2>

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

</CLASSHOST>

How can I implement these steps? Or I need it in another file.

Can you inform me about this topic.

Thanks,


texts: Mysql federated engine table creation error

texts:
I am trying to create a federated table on my local machine which suppose to connect to my another machine which also in my local network. I have enabled federated engine on both machines and is shown when I run this command show engines.

Below is my syntax.

CREATE TABLE fedTransfer( `fedID` INT NOT NULL AUTO_INCREMENT, `transferID` INT, `outletFromID` INT, `employeeToID` INT, `transferStatus` ENUM('y'), `transferToDate` DATE, `transferToTime` TIME, PRIMARY KEY (`fedID`) )
ENGINE=FEDERATED;
COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer';

Below is the error I get when I run this on sqlyog.

Error Code: 1
Can't create/write to file 'server name: '' doesn't exist!' (Errcode: 1408685900)

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.047 sec
---------------------------------------------------

Query: COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer'

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer'' at line 1

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec
---------------------------------------------------


texts: transaction log growing out of control during maintenance

texts:
I have been asked to clean up preceding and appending spaces in several columns of a table.  the table is an import from flat file and has many mistyped rows.

The table has over 150 million rows.

So far I have attempted performing an update statement that updates the columns using ltrim(rtrim(columnname)) and I have also tried to create a temp table (heap) and insert into ... select from using the same ltrim(rtrim(columnname)) syntax.

In every case the transaction log grows out of control to the point of running out of disk space.

I understand how transaction logs work fairly well.  However in the context of performing some kind of bulk maintenance like this, I am stumped.

Currently the database is in bulk-logged recovery model, I am running transaction log backups every 30 seconds and the transaction log continues to grow faster than I can back it up.  I have researched how I might break this job up into batches, but cannot seem to formulate a query that could do this effectively.  I have an identity field I could key off of, all the other columns are varchar columns.  Right now I'm thinking the answer is to simply get more disk space for the server, however I was hoping there was a better solution.

I am using Microsoft SQL Server 2014 Enterprise Edition.

The flat file is long gone; this is a table that has grown in size over the course of 10 years and many versions of SQL Server.  only smaller incremental batch updates are performed and I do believe the new inserts are already cleansed before inserting.  So I am fixing old stuff :(


texts: AWS Aurora RDS 2nd reader gets no queries

texts:
Running a Postgres Aurora RDS with one writer and two readers.

One reader is used heavily, but the other is barely used at all according to CloudWatch.

The Laravel application this is connected to points the read host to the reader cluster endpoint so that Aurora can do the balancing internally, but it does not appear to be doing that, instead favoring the 1st reader exclusively.

Is there some misconfiguration in the RDS or something else going on?


texts: phpMyAdmin - Cannot log in to the MySQL server

texts:
Setup a new instance of phpMyadmin and I am able to sign with root and password, but when I do the following I am unable to login. Also able to sign into mysql via command line.

 create user 'user'@'localhost' identified by 'password';
 grant all on user.* to 'user'@'localhost';

The phyMyAdmin config shows the following:

  <Directory /usr/share/phpMyAdmin/>
   AddDefaultCharset UTF-8

   <IfModule mod_authz_core.c>
     # Apache 2.4
     <RequireAny>
       Require ip 192.168.1.1/24
       Require ip 127.0.0.1
       Require ip ::1
     </RequireAny>
   </IfModule>
   <IfModule !mod_authz_core.c>
     # Apache 2.2
     Order Allow,Deny
     Allow from 192.168.1.1/24
     Allow from 127.0.0.1
     Allow from ::1
     Deny from All
   </IfModule>
</Directory>


texts: Setting up PostgreSQL Connection with DBVisualiser

texts:
Apologies for the rudimentary question, I'm new to setting up databases. Am aiming to create a new Postgres connection and load some CSV files into a table using DBVisualiser. Working on a Mac running El Capitan 10.11.3

I enter all the details in the Connection Wizard as follows:

But get the following 5432 error when I try to ping the server.

Where am I going wrong? I also tried using 127.0.0.1 as my local host but that didn't work either.


texts: How to recover data from a corrupted /base/ directory?

texts:
I have deleted all directory by mistake except from the given link
/var/lib/postgresql/9.5/main/base/18686 and so on.

I have 4 such directories, now I want to get data back from those folder as they are very important.

I am using ubuntu 16.04.

Can you please help us?


texts:
I have the following query:

SELECT master.master_id, master.showname,
MIN(CONCAT(shows.show_date, ' ', shows.show_time)) AS first_show_date,
MAX(CONCAT(shows.show_date, ' ', shows.show_time)) AS last_show_date, master.showinfo
       FROM master LEFT OUTER JOIN
       shows ON master.master_id = shows.master_id
LEFT OUTER JOIN
       realtimeticketing rtt ON master.realtimeticketing_id = rtt.realtimeticketing_id
LEFT OUTER JOIN
       event_types ON master.event_type = event_types.event_types_id
       WHERE   (master.domainlist LIKE '%INTERNET%')
       GROUP BY shows.master_id
       ORDER BY first_show_date, master.showname LIMIT 100;

This query returns the following output:

The requirement is that master_id 281 should be displayed as the first record since show dates lie within current date. And after 6th April 2014, the id 282 should start showing up.

I have tried various WHERE clauses but if I use WHERE shows.show_date >= Now(), it starts showing show dates from current date. I however require the first_show_date i.e. 01-03-2014 for master_id 281


texts: Are we a good fit for SQL Server Enterprise and table partitioning?

texts:
We currently have a production database that contains millions of records (14 million in the largest table).

One function of the database is to provide time sheet information so a single table could have 2 million time sheets but be joined across 5 other "detail" tables and each of those detail tables have between 500k and 14m records.

Also, we are running some legacy software that cannot be changed or replaced anytime soon.  The problem we are having is that there are some queries (also legacy and cannot be changed) that unfortunately are performing massive table scans.  So looking up 100 time sheets is causing millions of rows to be scanned.

I'm not a DBA by trade but I believe I have indexed everything I can possibly index with as much logic and research as I know how.  Plus I believe I have setup proper re-indexing jobs, etc.

We are running SQL Server 2008 (10.0.5500.0) on a machine with 32GB RAM (16GB devoted to SQL Server).  There isn't much else on the machine so most of the resources are devoted to SQL Server.

Our next plan is to possibly add more RAM to the machine and maybe bump the RAM used by SQL Server to 32GB.  Our database size is around 56GB on disk.

My question is, do you think we should look into upgrading to SQL Server 2014 Enterprise so that we can partition our time sheet tables?  I'm new to table partitioning but from what I understand it could be used to perhaps partition our tables by site and year?  So that 14m rows would be split out by 10-20 sites and then further split by year so that queries would scan 10k-20k rows instead of 14m.  Or am I just totally missing the mark here?

Any other advise would be welcomed.

Thank you.


texts: How to restore MySQL databases from database files?

texts:
Our MySQL on Ubuntu 15.04 stopped working after we moved the computer to a new location and kept giving the missing socket file error. We couldn't really fix it after trying sudo apt-get install -f and there was an error about mysql-server-5.6 being corrupted.

So after a lot of trail and error I was able to remove mysql-server-5.6 following commands here : https://askubuntu.com/questions/172514/how-do-i-uninstall-mysqli

sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/mysql

Afterwards, I reinstalled mysql server by:

sudo apt-get install mysql-client mysql-server

Then when I tried mysql -u root -p  and inside mysql, I couldn't see any previous databases..

I now realized that I've removed the /etc/mysql/my.cnf file from the system and my understanding is that mysql doesn't know where the databases files are and they haven't been loaded into the databases yet.

I should also mention that, the /etc/mysql/my.cnf file is basically empty with these two lines:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

I have .sql files for each database from a previous logical backup in February. The issue is that I don't know if there were any updates between the February backup and now (as I'm new to manage this database). I also have the path to a directory (let's call it a B directory) that contains what I believe the database files as many directories names match the database names. And in this B directory, many directories seem to be modified after February so it's very likely there were updates.

I'm reading other questions and the B directory contains the following files among others:

ibdata1
ib_logfile0
ib_logfile1

tokudb.directory
tokudb.environment
a bunch of *.tokudb

I understand I could use the following command to restore the February backup.

mysql -u username -p database_name < file.sql

But I'm wondering if there is any possibility to bring those updated database files back to life to MySQL. If so, how to do this? I'm guessing I need to somehow restore the previous /etc/mysql/my.cnf file.. but not sure what are required lines to put from this list I found: http://www.fromdual.com/mysql-configuration-file-sample

I'm not very familiar with MySQL backup and I'm still reading about mysqldump and what it does. I'm wondering if I can use the February .sql files and somehow apply it to the latest mysql database files??? Hopefully this and the title are clear..

Thanks so much for the help!


texts:
I'm trying to retrieve data from a table using multi-column subquery but it unions the result from both tables. I'm unable to modify it to get actual results.
Here is my query

select *
from   Recipe, user_plan
where  Recipe.id in (select meal_number
                     from user_plan
                      where week_id=(select max(week_id)
                                     from user_plan
                                     where user_id=:user_id))

Recipe Structure

user_plan structure

It should return 2 rows as per my expectations and data available in tables but it is returning 10 rows.Please help me figure this out. Thanks.
I'm using mysql-server on ubuntu


texts: MySQL - Optimize subquery in Select, where and order by

texts:
Im running a query like below.

SELECT DISTINCT c.`id`
    , c.`job-cat`
    , (
        SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())
        ) AS jobCount
FROM `jobscat` c
WHERE (
        SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())
        ) > '0'
ORDER BY (
        SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())
        ) DESC

from the above query this

SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())

repeated thrice, so my query planner also needs to execute this query 3times.

Since alias names will not work in where, so how can I optimize this query instead of running thrice I want to run only one time.

In order by I can use alias to reduce one step.


texts:
I have three tables:

forms which contains basic information about a form

form_parts which contains information about a certain input field and has a FK to forms (each form parts must have one form_id and each form may have one or more form_parts)

part_answers which contains the answer that the user submits for each field, has a FK to form_parts (each form_part may have multiple answers by different users and each part answer must have one form_part)

Now the problem is that when an editor whishes to edit the form, or a part of the form, if a user already answered one or more of those edited form_parts, his or her answer won't necessarily match the question anymore.

So how should I proceed?

I have thought of only one option so far, but it feels like a hack:

Set the form in question to inactive, or updated on a certain column (like a status column), and re-insert the updated version with it's corresponding answers.

This way, the data that has already been submitted will always continue to match the form it was submitted for, but I'm wondering if this is truly an optimal solution or if I forgot about a better method.

forms - sql :

CREATE TABLE `forms` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `modified_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `type` varchar(255) DEFAULT NULL,
 `product_id` int(11) NOT NULL,
 `updated` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `formulieren_id_uindex` (`id`),
 UNIQUE KEY `formulieren_name_uindex` (`name`),
 KEY `forms_products_id_fk` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1

form_parts - sql:

CREATE TABLE `form_fields` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `pdf_placeholder` varchar(255) NOT NULL,
 `datatype` varchar(255) NOT NULL,
 `regular_expression` varchar(255) DEFAULT NULL,
 `form_id` int(11) DEFAULT NULL,
 `input_placeholder` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `formulier_onderdelen_id_uindex` (`id`),
 KEY `formulier_onderdelen_formulieren_id_fk` (`form_id`),
 CONSTRAINT `form_fields_forms_id_fk` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=latin1

part_answers - sql:

CREATE TABLE `part_answers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `form_part_id` int(11) NOT NULL,
 `order_product_id` int(11) NOT NULL,
 `answer` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `part_answers_id_uindex` (`id`),
 KEY `part_answers_form_parts_id_fk` (`form_part_id`),
 KEY `part_answers_order_products_id_fk` (`order_product_id`),
 CONSTRAINT `part_answers_form_parts_id_fk` FOREIGN KEY (`form_part_id`) REFERENCES `form_fields` (`id`),
 CONSTRAINT `part_answers_order_products_id_fk` FOREIGN KEY (`order_product_id`) REFERENCES `order_products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=latin1


texts: Connecting to sql azure from ssms throw exception 'sys.configurations'

texts:
Connecting to Sql Server Azure from MSSQL Server MGMT Studio, following exception occures :

Cannot connect to xxxxxxxxx.database.windows.net

An exception occured while executing a Transact-SQL Statement or batch.
   (Microsoft.SqlServer.ConnectionInfo)

sql management studio connection to windows azure invalid exception object name
'sys.configurations'

Any clue ?

And here's the whole message : Cannot connect to ndkkyxvogj.database.windows.net.

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.CheckDacAndSmo(SqlConnection sqlConnection) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

Invalid object name 'sys.configurations'. (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.00.9164&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476

Server Name: ndkkyxvogj.database.windows.net Error Number: 208 Severity: 16 State: 1 Line Number: 17

Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)


texts: Subquery - return new row and only fill one column

texts:
I'm trying to fetch information about a database and I have the following query that prints most of it out:

SELECT Serverproperty('ServerName') AS [Server],
       (SELECT Server_type = CASE
                               WHEN virtual_machine_type = 1 THEN 'Virtual'
                               ELSE 'Physical'
                             END
        FROM sys.dm_os_sys_info)
       AS [Virtual/Physical],
       (SELECT ( cpu_count / hyperthread_ratio )
        FROM   sys.dm_os_sys_info)
       AS [CPU(s)],
       (SELECT CASE
                 WHEN hyperthread_ratio = cpu_count THEN cpu_count
                 WHEN ( ( cpu_count - hyperthread_ratio ) / (
                        cpu_count / hyperthread_ratio )
                      ) = 0 THEN 1
               END
        FROM   sys.dm_os_sys_info)
       AS [Core(s)],
       Substring ((SELECT @@VERSION), 1, Charindex('-', (SELECT @@VERSION)) - 1)
       AS [Product Name],
       Serverproperty('ProductVersion') AS [Product Version],
       Serverproperty('ProductLevel') AS [Product Level],
       Serverproperty('Edition') AS [Edition]

Result:

This is where my issue comes in. I found a script that prints out all your instances on the server, too. Like this:

DECLARE @GetInstances TABLE
  (
     value         NVARCHAR(100),
     instancenames NVARCHAR(100),
     data          NVARCHAR(100)
  )

INSERT INTO @GetInstances
EXECUTE Xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

SELECT instancenames
FROM   @GetInstances

My issue is that I cannot seem to forge the first query with the second by including it in the SELECT-statement of the first script. I get the error:

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

How can I fix this?
I would like the result to be printed out in the form of the image below, is that possible?


texts: Oracle drop database - Can images stored in RMAN catalog be retained?

texts:
It seems the default behavior when an Oracle database is dropped / deleted (entire server retired) that any RMAN entries for that system are also dropped.

Is it possible for RMAN to retain records for the dropped system until the "retention" is reached (removing backup pieces as they expire when stored on separate media) until all images are deleted (both from the RMAN catalog and media)?

Or does the original database have to be alive for this to happen?

The goal being to act like a backup product (NetBackup, CommVault, NetWorker, etc) where a catalog is retain independent of the state of the database.  Though doing this all natively with Oracle tools.


texts: What are the risks of building a database outside IT control?

texts:
I've been looking for the best StackExchange community to ask this. I hope it fits well here.

As a risk analyst in a trading business, I'm often confronted with large volumes of data, way beyond the excel-vba/MS Access scale capabilities.

However, when I requested my IT department to have a (any) SQL Database installed in my machine (or in whatever place) so I could 'play with', I was informed that it's an IT policy not to allow a mortal user like me to build a database because somehow I could threaten the infrastructure.

Assuming this reply is not due to my poor coding skills or my dubious character, can anyone put some perspective in this policy? Does anyone knows if this is a regular IT policy? Is there a way I could implement a SQL database that would eliminate such fears?

tks in advance!


texts: Select Most Recently Updated Row Based on Sequence ID with Three Tables

texts:
I've previously asked how to use a sequence ID to select the most recently updated row, but I'm not quite sure how to apply it to a more advanced query involving multiple joins. Here is my current query:

select
  p.Person_ID,
  p.Person_Name,
  p.Person_Age,
  d.Department_Name,
  c.Company_Name,
  c.Company_City,
  c.Company_State
from Person p
left join Department d
  on p.Person_Department_ID=d.Department_ID
left join (
  select Company_ID, Company_Name, Company_City, Company_State
  from Company
  group by Company_ID, Company_Name, Company_City, Company_State
) as c on c.Company_ID=d.Department_Company_ID
where c.Company_ID=1

I have made an example on SQLFiddle for this question. I want to only select the most recent Person (max(Person_Seq_ID)), and only show the most up-to-date Department name next to that Person (max(Department_Seq_ID)). There should only be seven records returned in my uploaded example. How do I do this?

Note that only Person and Department have sequence IDs—not Company.


texts: Does it make sense to create an index on foreign key (uuid) column in Postgres?

texts:
Assuming 2 sample tables in Postgres 10.X:

CREATE TABLE public.order (
id       VARCHAR(36) NOT NULL,
...
)

CREATE TABLE public.suborder (
id       VARCHAR(36) NOT NULL,
order_id VARCHAR(36) NOT NULL,
...
CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES public.order(id)
)

All IDs are simple UUIDs. suborder is being queried by order_id quite often. Does it make sense to create a separate index on order_id even though it references unique (UUID) value?

Something like:

CREATE INDEX suborder_order_idx ON public.suborder(order_id)


texts: MySQL fulltext query is very slow | What I'm doing wrong?

texts:
I have a very simple table with 100,000 rows.
CREATE TABLE test_database.products (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `city_id` int  NOT NULL,
  `market_id` int NOT NULL,
  `product_id` int NOT NULL,
  `search` varchar(450) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` int NOT NULL,
  `price` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
   PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

I added fulltext:
ALTER TABLE products ADD fulltext (search);

And when I search:
    SELECT
        id, market_id, product_id, quantity, price,
        MATCH(pr.search) AGAINST('dolor* qui*' IN BOOLEAN MODE) as srv
    from
        products pr
    where
        pr.city_id = 1 and
        MATCH(pr.search) AGAINST('dolor* qui*' IN BOOLEAN MODE)
    ORDER by
        srv DESC;

It takes ~ 2500 ms.
Explain select ...
id|select_type|table|partitions|type    |possible_keys|key   |key_len|ref  |rows|filtered|Extra                        |
--|-----------|-----|----------|--------|-------------|------|-------|-----|----|--------|-----------------------------|
 1|SIMPLE     |pr   |          |fulltext|search       |search|0      |const|   1|    10.0|Using where; Ft_hints: sorted|

MySQL version:
 Ver 8.0.21 for Win64 on x86_64

What I'm doing wrong?


texts: Find out which client IP and Query using highest network Bandwidth from SQL Server

texts:
I want to know which clients (IP or Hostname) are taking highest bandwidth from server and by what query they are taking high bandwidth.

Field Names:

Client Name/IP, Query, KB transferred, Date&Time of query executed, Time took to execute

Is there anyway to get it done in SQL Server?

Thanks Sanket


texts: How to INSERT all entries to a map table

texts:
I have a map table as

CREATE TABLE keymap
(
col1 int(11) unsigned NOT NULL,
col2 int(11) unsigned NOT NULL,
col3 int(11) unsigned NOT NULL,
FOREIGN KEY(col1) REFERENCES table1(col1) ON DELETE CASCADE,
FOREIGN KEY(col2) REFERENCES table2(col2) ON DELETE CASCADE,
FOREIGN KEY(col3) REFERENCES table3(col3) ON DELETE CASCADE,
PRIMARY KEY (col1,col2,col3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

What is the most efficient way to INSERT all possible combination of col1, col2, and col3?

The reason I am asking this simple question is that the table is big (tens of millions of rows) and I need to do INSERT regularly to fill the missing combinations (table1, table2, and table3 are regularly being enlarged).


texts: Same query plan, different data set, very different query duration SQL Server 2012

texts:
This may be a terrible question, because I'm not sure how information I can include to help.

We have data segregated by customer. One customer apparently has higher volume of data. The same query ran for a small customer returns in 2 seconds, and the result is 11 rows. The larger customer takes 47 seconds, and the result is 6600 rows. This is a complicated query with 11 joins. This is just for a report, but the report is timing out and the operator is complaining. It's possible the difference is just the volume of data, but I want to investigate.

When I look at the query plan, the percentages for the query cost are exactly the same between the two. There are no suggestions for indexes. In both query plans, I can see the highest cost is from a join to a table that has 3.8 million rows. However, both sets of data are joining with that same table. In both cases the resulting "actual number of rows" is 3.8 million. In both cases this join has a clustered index scan that is 39% of the query "cost".

So my question is this: Do the percentages and query cost even mean anything? are they referring to the estimated data from the query plan, and do not reflect the real cost? Otherwise how could the same join make up 39% of the "cost" for both, but one takes 2 seconds and one takes 47 seconds? Is SQL server lying to me and the actual difference in cost is the "nested loop" for the inner join that produces the final row count even though it lists that as 0% of the cost?

These images show the two elements with the highest cost. They have identical properties between both the query plans. The difference in the final row totals is the result of joins with other tables. But those joins all have a low relative cost.


texts: Using Foreign Key and References for ON DELETE CASCADE in mysql

texts:
I was thinking that these two ways for creating foreign keys are the same

CREATE TABLE child1 (
id int(11) not null auto_increment,
parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE CASCADE,
PRIMARY KEY(id)
);

and

CREATE TABLE child2 (
id int(11) not null auto_increment,
parent_id int(11),
PRIMARY KEY(id),
FOREIGN KEY(parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE
);

but when deleting a record from parent table, the corresponding record in table child2 will be deleted but NOT that of table child1.

Where am I wrong? REFERENCES is not enough and we necessary need to write FOREIGN KEY to use ON DELETE CASCADE?


texts: How to solve replication in MongoDB only with two machines?

texts:
I have two machines connected in a ring network, each one running Wildfly in HA mode using JGroups ant MongoDB in HA Mode using Replica Set. The machine 1 is the primary and machine 2 is secondary.

Whenever Wildfly 1 drops, Wildfly 2 raises by reconfiguring MongoDB 2 to be primary by changing the settings of the ReplicaSet.

We know that the Replica Set does not work by itself with two machines, but we have this limitation.

If a split occurs on the network, Wildfly 1 continues to work writing in MongoDB 1, Wildfly 2 is also active, changes the MongoDB 2 configuration to primary, and begins writing to it.

We are using version 3.6.5 of MongoDB.

Questions:

What happens to MongoDB when reestablishing the network and Wildfly 1 take over as principal?
MongoDB 2 will continue as a primary but Wildfly 2 will be stopped?
MongoDB 1 will be able to merge with MongoDB 2?
Considering that MongoDB resolves by doing the merge, what happens if the MongoDB 2 OPLOG passes the size specified in the configuration? Does he go back to the beginning (like a car odometer)?


texts: How to select random groups of similar records?

texts:
I have following table:

create table users (
  id serial primary key,
  gender text not null,
  age integer not null
);

insert into users (gender, age)
select (case when i % 2 = 0 then 'male' else 'female' end), (i)
from generate_series(20, 45) i;

I need function(s) that will be able to fetch n random groups of k users. One user may appear in more than one group (so there might be two identical groups in the result group list). I need four variants (or four functions) of such function:

users within one group are picked randomly
users within one group are picked randomly but they should be of same gender (gender for a single group should be picked randomly)
users within one group are picked randomly but they should be within same age range (difference between their ages should not be > 5 years)
Combination of 2) and 3) (same gender + same age range).

I've used word "should" because if there are no users that match required conditions then random users should be picked within group.

Also k (size of each group) does not have to be dynamic (in fact it will be 2 or 3). Performance here is very important. I know that order by random() might be a bottleneck and I am considering adding column rnd integer to users table that will be updated periodically.

As for point (2) I have something like this - it works but I don't know yet how to "fallback" when there are not enough users with specific gender in database.

with genders as (
  select (case when random() > 0.5 then 'male' else 'female' end) as gender
  from generate_series(1, 10) i
)
select (
  select json_agg(row_to_json(three_users.*))
  from (
    select *
    from users u
    where u.gender = g.gender
    order by random()
    limit 3
  ) as three_users
) user_group
from genders g

And here is what I have for point (3) (assuming user ages in database between 18-50):

with ages as (
  select
  floor((random() * (50 - 18) + 18)) age
  from generate_series(1, 10) i
)
select (
  select json_agg(row_to_json(three_users.*))
  from (
    select *
    from users u
    where u.age >= (a.age - 5) and u.age <= (a.age + 5)
    order by random()
    limit 3
  ) as three_users
) user_group
from ages a

And here is what figured out for "fallback" users so far:

with ages as (
  select
  floor((random() * (50 - 18) + 18)) age
  from generate_series(1, 10) i
)
select (
  select json_agg(row_to_json(three_users.*))
  from (
    select *
    from (
      (select u.*, true as primary
      from users u
      where u.age >= (a.age - 5) and u.age <= (a.age + 5)
      order by random()
      limit 3)
      union
      (select u.*, false as primary
      from users u
      order by random()
      limit 3)
    ) six_rows
    order by six_rows.primary desc
    limit 3
  ) as three_users
) user_group
from ages a


texts: What is the proper way of designing database with dynamic fields in MySQL + PHP?

texts:
I need help on designing a proper structure for a database table with dynamic fields. I am using MySql with PHP and the system I'm currently working on (inventory system) requires a table that accepts user-generated data categories. I'm really not good with this so any help will be greatly appreciated.

This is what I have right now (I know it's in really bad design please help)

Inventories can have different types (e.g Food, Clothes, etc) and details are dynamic based on what will be supplied by user (e.g Size, Brand, etc).

I have inventories_tbl:

+-------------------+
| inventories_tbl   |
+-------------------+
| id                |
| item_name         |
| inventory_type_id |
| description       |
| cost              |
| price             |
| quantity          |
| created_at        |
| updated_at        |
+-------------------+

then I have the inventory_types_tbl:

+--------------------+
|inventory_types_tbl |
+--------------------+
| id                 |
| name               | (e.g Food, Beverages, Clothing,etc)
| display_name       |
| created_at         |
| updated_at         |
+--------------------+

here comes the problematic part, I need to store dynamic inventory details according to what type of inventory type the item is. (e.g Food = 'expiration date', etc then if in Beverages = 'net volume', 'alcohol content', etc)

So I created a inventory_attributes_tbl:

    +-------------------+
 | inventory_attributes_tbl |
    +-------------------+
    | id                |
    | name              | (e.g size, weight, model number, serial number etc)
    | inventory_type_id |
    | field_name        |
    | created_at        |
    | updated_at        |
    +-------------------+

Then I have the inventory_attributes_values_tbl where I store the value of the attributes of a given inventory:

    +-------------------------+
  inventory_attributes_values_tbl
    +-------------------------+
    | id                      |
    | inventory_attributes_id |
    | inventory_id            |
    | value                   |
    | created_at              |
    | updated_at              |
    +-------------------------+

This setup makes the item delete/update so complicated and I also need to create a reports later on about based on the dynamic details of an item like graphs and this setup makes the query so slow and actually made the server to hang on querying just 10k+ of results. Any opinions are appreciated. Thanks in advance.


texts: How to concatenate old string and new string in mysql procedure

texts:
I want to get string from concatenate in while loop by step

1.get value(TmpAttrTH)

2.concatenate string(AttrTH) and value(TmpAttrTH)

My code like this

SET AttrTH = ''; -- First set AttrTH to blank
while i < CountAttr DO -- CountAttr is 2  and i set start at 0
    SET TmpAttrTH = (select thainame from attribute  where code = (select SPLIT_STR(atrMapping,'|',i) from attrcontrol where atrcode = AtrSet));
 -- TmpAttrTH  it getdata of loop example "stringloop1" , "stringloop2"
    SET i = i + 1;
 -- AttrTH in Concat function is old AttrTH string
    SET AttrTH = CONCAT(AttrTH,TmpAttrTH);
 -- and SET AttrTH is new string will CONCAT(AttrTH,TmpAttrTH);
END WHILE;
    select AttrTH  as str;

My result Expect "stringloop1stringloop2" but i don't know why i get null result.

I try to explain this problem too hard for me. pls help.


texts: Table statistic vs estimated rows query plan

texts:
Can anyone explain why despite having inaccurate table statistics, the actual query plan "estimated number of rows" is correct? E.g table has 30 rows but all table statistics show 50 rows. However a query on the table produces a plan that apparently correctly estimates 30 rows. How did it know there are actually 30 rows?

CREATE TABLE dbo.TestPerf
  (
     ID   INT IDENTITY(1, 1),
     Col1 VARCHAR(100)
  )
GO

INSERT TestPerf
SELECT 'badger'

GO 1000

CREATE STATISTICS stat_TestPerf_Col1 ON dbo.TestPerf (Col1) WITH FULLSCAN;

--lose some rows
DELETE dbo.TestPerf
WHERE  ID > 750 --Check the stat not updated - still shows 1000 rows
DBCC SHOW_STATISTICS (N'dbo.TestPerf', stat_TestPerf_Col1) --now run a query and see the estimatedRows
SET statistics profile ON

SELECT *
FROM   dbo.TestPerf

SET statistics profile OFF --estimatedRows = 750


texts: Create a mysql database with scheduled queries

texts:
I want to create a system where a database only returns the most recent select query every 20ms,and otherwise returns the most recent one in memory.

I am not sure how this can be achieved.
I am using php to access it and i can use cron jobs to automate things.

Previously i thought about updating a text file every 20ms using a php script that selects from the database. but i am sure there is a more ascetically pleasing solution to show information of a query that doesnt require opening a stream to a text file (feels wonky).

The REASON i want to do this is to eliminate the need for over hundered select queries all the time to the database. and to keep everyone in sync so if 2 people send a query in a 10ms interval no different results will be sent. (if these queries arent in between the 20ms refresh timer)

I am not completely sure this is the right place to ask, but since this isnt a programming question is decided this might be the most correct sections.


texts: Modify all array entries in all records of a table at once

texts: I have a schema that involves an array of timestamps. Due to an import problem all the timestamps are off by one hour (time zone issue). I'd like to update all the timestamps in all the records with one UPDATE statement.


texts:
My database has 16MB of space left.

I used to just truncate as I was taught but I found these links that advise against truncating:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/

Is there anything else I can do on my database to reduce the size other than deleting table records? I am new to the DBA forum and I probably should have looked around for other questions before posting but I am desperate as I am worried about my database going down.


texts: Postgresql installation, avoid creating “postgres” user

texts:
I have a constraint, that I can use only one user on a SUSE Linux Enterprise Server 11.

Now, I need to install PostgreSQL 9.4. Is it possible to somehow avoid the automatic creation of "postgres" linux user by PostgreSQL's installation wizard, and use current user as a database admin?

Additional explanation about the weird  constraints:

We have to host our software on the server of the IT company, which is subsidiary of the big bank. They have quite strict rules, due to storing and processing of the sensitive user data. So, one of the rules, is that we can have only one user there and everything should be installed under this user (with sudo rights). Initially, the server has only the most basic software (and PostgreSQL is not included there).


texts: psql: id SERIAL PRIMARY KEY not starting with 1?

texts:
This is something I don't understand, can somebody explain?

I create a table

CREATE TABLE cali (id SERIAL PRIMARY KEY,
    ALK_from char,
    ALK_to char,
    AND_from char,
    AND_to char);

Then I simply try to insert some data from a csv file like so

\COPY cali FROM '/home/.../data/output/id_cali.csv' (FORMAT CSV);

Which gives me this Error:

ERROR:  value "4372840000" is out of range for type integer
CONTEXT:  COPY cali, line 1, column id: "4372840000"

I know that 4372840000 results in an integer overflow. That is why I'm trying to insert the values as chars in the first place.

But my understanding is that id SERIAL PRIMARY KEY is some sort of auto increment why is the value 4372840000 used for the column id?

The file looks like this

4372840012,4372840013,13,14,
4372840014,4372840014,15,15,


texts: Generating sequences partitioned or grouped by field(s)

texts:
Given a table like:

Id     Grouping    SequenceId
1         A            1
2         B            1
3         A            2
4         A            3
5         C            1
6         B            2

Where SequenceId increments based on Grouping, like the result of DENSE_RANK()

I would like to efficiently generate the next SequenceId on INSERT.  SQL Server has SEQUENCE, but I would need a sequence for each Grouping.  I could also create a table like the following (perhaps with a few more columns for flexibility):

Grouping     NextValue
   A             4
   B             3
   C             2

and a procedure to atomically fetch the next value and update the table, but this wouldn't be as effecient as a SEQUENCE and feels an awful lot like reinventing the wheel.  I just haven't found the wheel I'm looking for yet.

What is the efficient and scale-able way to generate sequences per grouping, when the number of sequences can be huge and I want to avoid the performance hit at read time of an aggregate function like DENSE_RANK()?


texts: #1036 - Table 'pma__recent' is read only in phpMyAdmin / xampp / Windows

texts:
I have about 40 databases on localhost powered by xampp 5.6.15.
Some of them use MyISAM and others InnoDB.

The database has been corrupt and phpMyAdmin was permanently outputing error messages. I tried many things suggested on stackexchange's websites and I almost managed to get back to normal.

However, there is still this message in phpMyAdmin when clicking on a table name:

#1036 - Table 'pma__recent' is read only

From my readings, this seems caused by innodb_force_recovery = 1 in xampp\mysql\bin\my.ini file.

Unfortunately, if I comment out innodb_force_recovery, MySQL cannot be started, probably because there are still errors in the database.

I called mysqlcheck -c -u root -p --all-databases from a terminal.
The log tells that some tables are not existing in engine (whilst they should).

How should I proceed to get back to normal?


texts: When to periodically RESTART SQL SERVER 2008 R2?

texts:
So I've read that it's really a bad practice to restart SQL Server on a production environment..

Does restarting SQL Server speed it up?

Automatically restart SQL Server 2008

Why are periodic restarts required to keep my instance performing well?

Then tell me this,

How many of you have maintained an SQL Server on a production environment without having to restart it for at least ONE year or more? If so, can you share some general guidelines on how to do this? namely with regards to the following:

Minimizing/eliminating deadlocks
Cost efficient CPU and Memory usage
Minimizing/eliminating memory leaks
Monitoring the servers' processes to identify increase resource consumption

Please pardon me if anyone would find this question as offensive, I would just like to be more enlightened with the subject matter.


texts:
There's a CAP Theorem: R + W > N. Let's say I have W=1 (master), R = 6 (slaves), N = 6, so one slave can write and read. As the theorem says we could be sure in strong consistency (we always will get the newest value).
I read that data storage could check the version and return the newest one. But is it true in fact of using MySQL? Or maybe we need to use any kind of middleman? How does CAP theorem apply to MySQL?


texts: Data usage increases initially when reorganize partition mysql cluster

texts:
In my MySQL cluster, the data usage of 4 data nodes is approximately 77%. I added a new data node online, and attempted to reorganize partition to redistribute data among all data nodes.

But before the query alter table customer_tbl reorganize partition; is successfully executed, the data usage of existing data nodes started increasing, and reached up to 95% before ERROR customer_tbl is FULL is encountered.

How can I bring a data node online and reorganize table successfully? My table has ~100 million rows.

Why the data usage of existing data nodes increased in the first place and what is the internal working of mysql cluster when it reorganizes partitions?


texts: Is a 5+ column primary key bad for large (100 million+) table?

texts:
I was reading about some real life DB issues, and one project had a 100 million row plus table that had 5 columns as its primary. I'm thinking this is bad, but can anyone tell me exactly why?

The table was kind of a micro rollup/aggregation table, so the 5 columns were like (day, market_id, product_id...). At first I thought that a 5 column primary key wasn't ideal, but the more I thought, I couldn't really come up with a good reason why it was bad.

This was in a late night discussion with half the company engineers. Someone just mentioned this was a bad design, one senior engineer agreed, but no one really jumped in as to why. Thus trying to research the matter for myself!


texts: What happen when changing column type for a table with clustered index?

texts:
I have a very large table (200M+ row), and it is clustered indexed. I would like to change one of the foreign key column from a bigint to smallint.

My questions are:

Does SQL need to rebuild the clustered index?
What if this table is a heap and not a clustered index?


texts: change analyzer for an elasticsearch index?

texts:
I'd like to change the analyzer for an existing index in ElasticSearch, but I can't figure out the syntax (and I'm not grokking the error message).

GET /ccc_test/_mapping

{
  "ccc_test": {
    "mappings": {
      "test_article": {
        "properties": {
          "id": {
            "type": "text"
          },
          "language": {
            "type": "text"
          },
          "text": {
            "type": "text"
          },
          "title": {
            "type": "text"
          },
          "url": {
            "type": "keyword"
          }
        }
      }
    }
  }
}

Then I close the index with:

POST /ccc_test/_close

And then I'm trying to update the analyzer for the "title" field to Portuguese:

PUT /ccc_test/_mapping/test_article
{
  "properties" : {
    "title" : {
      "type" : "text",
      "analyzer" : "portuguese"
    }
  }
}

But I get this error:

{
  "error": {
    "root_cause": [
      {
        "type": "remote_transport_exception",
        "reason": "[instance-0000000004][172.17.0.8:19760][indices:admin/mapping/put]"
      }
    ],
    "type": "illegal_argument_exception",
    "reason": "Mapper for [title] conflicts with existing mapping in other types:\n[mapper [title] has different [analyzer]]"
  },
  "status": 400
}

So I'm not clear what has a different analyzer.  The ccc_test index only contains one type (test_article). I'm on ElasticSearch 5.6


texts: How can I sync databases to a secondary server for read-only usage?

texts:
I want to sync databases to a secondary server that will be used read-only for SQL Server Reporting Services.  These syncs can be done once or twice nightly and mid morning;
synced database sizes would be 400 Gb, 50 GB, and 60 GB.

What are my options to approach this scenario?

I planned to implement log shipping using RedGate's product so my backup would be compressed and easier to implement, however I am not sure how to maintain the logfile size for maintenance purposes.  For instance, when do I shrink the logfiles since they may grow enormous until I take a log backup during the night?

Are there any other options like mirroring or replication?


texts: How do I calculate a field based on multiple non-numeric records

texts:
I have a several tables that I have joined together to get raw data.

Some information about my query in the most basic form:

Table1 has a column [FacilityID] that is a unique identifier in that table (as well as several other columns, that are unimportant to the question).
Table2 has a column [FacilityID] that matches Table1.[FacilityID], but not all of the records from Table1.[FacilityID] are found in Table2.[FacilityID]
Because of this, Table1.[FacilityID] is Leftjoined to Table2.[FacilityID].
Table2 has additional columns [ActivityCode], [ActivityDate], [ActivityEval?], and [ActivityOutcome]
Table2.[ActivityOutcome] is only populated if Table2.[ActivityEval?] = "Yes"
Table2.[ActivityCode] contains several string values, all nonnumeric.
For simplicity and purposes of this example, Table2.[ActivityCode] contains values "EVALA", "EVALB", "EVALC", "EVALD", "EVALE", "EVALF", "EVALG", "EVALH", "EVALI", "EVALJ", "NOEVALA", "NOEVALB, "NOEVALC", and "NOEVALD" (not in that order, nor with the same prefixes or suffixes)

Right now my query is pulling: Table1.[FacilityID], Table2.[ActivityCode], Table2.[ActivityOutcome], Where (Table2.[ActivityDate] is between a begin date and end date) And Table2.[ActivityEval?] = "Yes"

My problem lies within the codes listed in Table2.[ActivityCode].

Codes: EVALA, EVALB, EVALF, EVALG, EVALH, EVALI, and EVALJ can all stand on their own and are counted as a single event
Codes: EVALC, EVALD, and EVALE cannot be counted as a single event, as they would only happen with at least 1 code from above.
If EVALA, EVALB, and EVALC are all entered within the timeframe set by the query, then an EVAL3 event has taken place.
If EVALA, EVALB, EVALC, EVALD, and EVALE are all entered within the timeframe set by the query, then an EVAL5 event has taken place.

I need a calculated field in my query to list out either an EVAL3 or EVAL5 event, or list out the individual codes from Table2.[ActivityCode].  This is to help our data entry folks quickly check if all their codes are entered into the system for credit.  (We've been having a hard time getting consistent data entry with thousands of data points entered every year, by hundreds of people.)

I cannot, for the life of me, find how to complete this task out on the google.

Can anyone help me?  Will I just end up with a Crosstab query that I have to filter in Excel?  I really want this to happen all in one fell swoop, so I don't spend a "billion" hours each time the data is pulled refiltering in Excel.

I'm not the best with SQL or VBA, but I definitely try my damnedest to understand. I've tried using the Expression Builder and Concatrelated, but I am not getting the results that I want. That's why I'm finally turning here.

It's an access db, so yes, it runs on SQL. I don't necessarily need a single row, unless the 3 or 5 criteria are true and there are no other codes. Say for 1 FacilityID EVAL3 is TRUE, but they also have an EVALD, but not an EVALE, I want one row for EVAL3 and another for EVALD. Once an EVALE is added for the facility, I only want one row for the facility to show EVAL5. Any other EVAL codes should be listed out on separate rows.

Also, the ActivityCode may not be unique for the FacilityID in the query. There may be times that an EVALA occurs as part of an EVAL3 or EVAL5 event, but later in the timespan pulled for the query (usually a year) another EVALA occurs because of new information about or a change at the Facility. In this case the new EVALA is acceptable and should be counted in the activities for that facility, but not counted as part of the EVAL3 or EVAL5 event.


texts: What is a good way to clone a database with only certain tables?

texts:
My situation:

I have a MySQL database, DB1, which is fed new data every hour via a PERL script. I want to create a second database, DB2, which is a copy of DB1 except it only has one of the tables that DB1 has. DB2 must be updated either every hour or whenever DB1 is updated (whichever is the most simple to implement).

What would be a good method of doing this? I'm mostly looking for simple ideas that won't take too long to implement, but I'm open to any and all ideas you might have.

Additional information:
- DB2 will be used to connect to a Tableau dashboard.
- DB1 has terrible performance and will be scrapped in the near future.


texts: Restore a large number of transaction logs

texts:
I'm using SQL Server 2008 R2, and I need to restore 36 .trn logs to a database. I have restored the first 4 using the restore transaction log functionality in SSMS (right-click database > tasks > restore > transaction log).

Is there a way to restore them all in order using this tool or with a script? The .trn files are in a folder on a remote server. I do not not need to restore all the files in the folder, only ones between specific dates.

I'm looking for a way to automate this so I don't have to manually restore each individual log.

I access the files using an address like the one below.

\\remote server IP\SQL_Backup\LMSQL02\TLog\file.trn


texts: Confused about Primary Keys, and Foreign Keys

texts:
Im brand new to database design and, I'm slightly confused about Primary Keys, and Foreign Keys and my database design.

Context:
I'm working on a database of museums and Categories will be one way the user will be able to search. e.g click on "Music" and a list of music related museums turn up. Pretty simple.

Problem:
I'm stuck and confused on the Normalisation process of the tables and IF I've got my keys in the right order.

This is what I've got...

I'm not sure, is this overly complicated? OR would it be easier, to add the contents of the "Location" and "Admission" table into the "Details" table and just relate the "Details" to "Categories" table. So, from my understanding going from 1-to-many (at present?) to then becoming 1-to-1???

—

I apologise if this makes no sense, I am new, obviously VERY confused. It's tricky teaching yourself!

Thanks everyone, I'm really grateful. If you need to see a larger image, it's here...

www.graphicsdesigned.co.uk/Museumscema3.jpg

Cheers!


texts:
We have a well designed and normalized DB, but, there is a requisit that could cloud this design.
This image shows the design of part of the DB.

The main functionality of our application is to create some stats of the quantity of notifications each user recieves. So we query the DB by groups of users (e.g. SELECT COUNT(*) FROM Notifications INNER JOIN Users ON User.UserID = Notifications.UserID WHERE User.att = ...).

The requisit that is causing me troubles is that our application allows a user (which is different of any user stored in the table Users, since this is a monitoring application) to customize some configuration parameters, such as miliseconds to wait before looking again in the DB in order to obtain some data for charts, which number of notifications makes a user selectable (e.g. if the user has more than X notifications then it should be displayed in the chart) etc.

This configuration must be persistant, we do not want to make the user set the parameters each time he opens the application.

The problem I see is, how do I make the configuration persistant without clouding the design. Because, if you set, lets say, the X number of notifications needed, you could see that as an attribute of Notifications, but is kind of an Entity attribute. Since, is (conceptually) an attribute of Notifications but what we store there are notifications, which are oblivious of configuration attributes.

Moreover, we have more entities, with exactly the same problem. An entity that stores certain kind of data, and from the application you can configure in which ways this data is going to be treated. And that configuration must be persistant.

Therefore, my question is, what is the appropiate way to represent this configuration persistance? Should it be represented in the DB or in other file? I think that it should be stored in the DB, but maybe I am being stubborn...


texts: Can I use Default Accounts for setting up AOAGs 2014\2016

texts:
Can anyone help me on below :

I would like change the SQL Service \ SQL Agent Service to Active Directory Accounts . But before that I would like to understand below :

Can I use the default accounts which SQL Service provides by default

Local Service
Local System
Network Service

If I cannot why I cannot use them  ?

If I can then why should I need Windows Domain Level Service Accounts ?

Why SQL Server Agent Account needs to be added as SysAdmin , is it by default nature of SQL Server Agent Account ?

Can anyone please help me with the background of the above points in brief .


texts: Create unique index oracle with where clause

texts:
I m trying to create a unique index with where clause like this:

CREATE UNIQUE INDEX cmt_unique_sid_yid_ct
          ON COMMENTARY (source_id, year_id)
       WHERE comment_type_id = (
         select comment_type_name
         from comment_type
         where comment_type_name = 'Final'
       );

Getting this error :

02158. 00000 -  "invalid CREATE INDEX option"
*Cause:    An option other than COMPRESS, NOCOMPRESS, PCTFREE, INITRANS,
           MAXTRANS, STORAGE, TABLESPACE, PARALLEL, NOPARALLEL, RECOVERABLE,
           UNRECOVERABLE, LOGGING, NOLOGGING, LOCAL, or GLOBAL was specified.
*Action:   Choose one of the valid CREATE INDEX options.

I m basically trying restrict comments with type Final from comment_type table, to only one row per source/year. So scope to :

comment_type_name | source_id | year_id

How can I do this with SQL, specifically oracle?


texts: How would you design a table for a booking system?

texts:
I have a design database for a booking system. The booking system requirement are as follows:

The booking system allows user to book a time slot (1 hour) between 8 am to 1 pm for Monday to Friday.
The user can book a venue for that 1 hour time slot.
The booking system is open for 13 weeks, then rest for 13 weeks and open for another 13 weeks and rest for 13 weeks for each year.

The problem that I face when I design the table is that there are 3 important controlling variable - timeslot, venue and 13 weeks.

I have draft the design by having a table that contain a fixed amount of rows. (So, for a 8 am to 1 pm, there will be 5 slots, and thus each week will have 25 slots. Suppose there are 3 venue, then I will have 25 slots x 3 venue, which give me 75 rows. Then I multiply 75 to 26 weeks, because there are two 13 weeks that the system will be open. Thus, this will give me 1950 fixed rows.

But, the problem will be that if I increase the number of hours by ending it at 10pm, then I will have 14 slots per day, which mean that each week will have 70 slots. What if the venue increase to 10 venue, then, I will have 70 slots x 10 venue, which give me 700 rows. Of course, multiply it by 26 weeks, I will need to have a table that contain a 18,200 fixed rows. The design will be hard to manage since there are so many rows.

The table would look something like the following:

+----------------------------------------------------------------------+
|  ID      |   Week    |   Time   |   Venue   |   User   |    Status   |
+----------------------------------------------------------------------+
|  000001  |   1       |  8 am    |  Room 1   |          |  Available  |
|  000002  |   1       |  9 am    |  Room 1   |          |  Available  |
|  000003  |   1       |  10 am   |  Room 1   |          |  Available  |
|                                                                      |
|  018200  |   26      |  10 pm   |  Room 10  |          |  Booked     |
+----------------------------------------------------------------------+

The ID will be the PK for this table.

Is there a way that I can keep track of each booking slot but without creating a fixed amount of rows for each booking slot? (Each booking slot will contain 3 important data - the week, time, venue)


texts:
In continuation of my previous question:
Get week/month data

I have a MySQL table with day column of type DATE and position column of type INT.

I need to fetch average position for every week in consideration. (A week is in consideration if there is a row with day value belonging to this week.)

The fetched data should be an array of two columns:

date
position

Here "date" is the first day (Sunday) of the week and "position" is the average position for this week.

The trouble is how to get the first day for a week.


texts: Reorganize index maintenance job is more than 20 times slower

texts:
I have a reorganize maintenance job which runs daily (I know, this have been altered to weekly) which for the majority of executions it takes around 2 hours however over the weekend has failed with execution times of 22 and 35 hours.

The server instance is quite small with the largest database size being 65 gb.

I have checked auto growth and there haven't been any auto growth processes, also I'm currently running the script below to show index fragmentation however this has been running for over 10 minutes and pulled through nothing.

Please can someone provide possible reasons for the elongated duration of my reorganize job?

    SELECT  TOP 10
    OBJECT_NAME(ips.OBJECT_ID)
     ,i.NAME
     ,ips.index_id
     ,index_type_desc
     ,avg_fragmentation_in_percent
     ,avg_page_space_used_in_percent
     ,page_count
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
    INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
     AND (ips.index_id = i.index_id)
    ORDER BY avg_fragmentation_in_percent DESC

Additional information

After running for 10 minutes + and filtering to only show user tables, I managed to return results for fragmentation.

The top 237 were >= 80% fragmentation and top 1091 were >= 50% fragmentation.


texts: MS SQL : Use Computed value to Compute other values

texts:
I'm having problem with generating results which are computed from data that exists in database. This is the case

I have Coupons that can be amount or percent, if percent is 0 then amount is used

CASE
  WHEN c.CouponPercent > 0 THEN p.Price - (c.CouponPercent/100)
  ELSE c.CouponValue
END as Coupon

now I have to calculate percent for one content provider that is

(p.Price-Coupon)*(ProviderPercent/100)

How can I use calculate coupon value from above to calculate Provider percent, if I use it as in code above error is reported "Invalid column name 'Coupon'".

Please help me, i Know how to resolve this on difficult way which is calculate Coupon value each time when calculating ProviderPercent but I have 8 percents like this in one report for different providers.

So basically question is how to use computed Coupon value to use it in another calculations for same record. Coupon is changed regarding product, so i can be different for every record selected from database.


texts: How to write a comma-separated list of a single scalar variable in PL/pgSQL?

texts:
This is related to:

Postgres function assign query results to multiple variables
https://stackoverflow.com/q/15949337/5419599

I want to loop over values returned in a single column.

In other words:

FOR my_var IN select distinct some_column from some_table LOOP
  -- do stuff with my_var
END LOOP;

(The type of some_column is text, by the way.)

However, I get an error (line break added for readability):

ERROR:  loop variable of loop over rows must be a record
or row variable or list of scalar variables

I believe I can work around this with something like:

DECLARE
  somerow RECORD;
  my_var TEXT;
FOR somerow IN select distinct some_column from some_table LOOP
  my_var := somerow.somecolumn;
  -- do stuff with my_var
END LOOP;

Or perhaps even:

DECLARE
  idontcare INT;
  my_var TEXT;
FOR my_var, idontcare IN select distinct some_column, 1 from some_table LOOP
  -- do stuff with my_var
END LOOP;

But, isn't there any way I can just have a single variable in a comma-separated list?  Or is that impossible?

(If it's impossible, are the other workarounds I gave above even correct?  Any gotchas in them?)


texts:
Ok So here is the scenario.

Web based application running on mysql database A.  All users able to add/edit/delete.

The aspplication runs well.  But now the Company face a new problem which is accessing the web app where no internet is readily available.

As a result they need an offline version which runs on their tablet.

Im thinking that I could simply set up WAMP on their tablet and have the app run there, however clearly I have to sycronise the data at some point.

So my questions are this.

Is this a master/master scenario?

Can someone explain some of the major pitfalls/uplifts I may get from doing this?

Is there a more practical approach that Im missing?

Many Thanks in advance


texts: Auditing user queries of specific rows in a table

texts:
Is it possible in SQL Server 2017 and upwards to audit the access to certain rows in a table? Imagine a customer table, and we wanted to track when users selected the data for premium customers identified by their primary key; or maybe employees querying the salary information of their fellow employees (but not too worried about them querying their own salary information).

With the wealth of auditing and security technologies that Microsoft are throwing at SQL Server these days, is there anything among e.g. extended events, row level security, auditing etc that would help me?


texts: Modeling a system with optional components

texts:
I am working on an MS Access database that is intended to help keep track of a large scale implementation of many component parts connected together. I have created a working implementation based on my self-taught database design skills, but it lacks features that I'd like to have. I'm wondering if there would be a way to more effectively model the actual implementation in a database than I have created.
The design consists of a collection of varied implementations of similar component parts. There are 7 types of components, A through G. As you can see in the image below, there are two complexities that make modelling it difficult. The image does not show every possible connection, but essentially any component D through F can connect to any other component D through F.

I've substituted some clumsy made-up example components for clarity.

First, Component B may be connected to component C directly, or it may be connected through any number of components D through F. Components D through F may also be connected to other instances of components D through F. For example, it may simply be B->C, or it could be B->D->F->F->D->E->C or any other configuration. Finally, each instance of components D through F might be connected to more than one other component D through F, creating branching paths.
Second, instances of component C and component G are inherently related, usually (but not necessarily) existing one for one in a pair. However, instances of component G is actually physically connected to a component B. This creates a circular relationship.
As far as I have been able to tell this system is not possible to implement fully in a relational database. Instead I have implemented the below design.

I grouped components D through F together as they have similar attributes, and the user will simply register their existence, but must reference other materials to determine the configuration. I also removed the link from component B and G, as a component G cannot exist without a component C.
My main issue with this design is that when, for example, two component C instances and a number of components D through F are related to a component B, there is no way to tell which components D through F are associated with a specific component C. In an ideal world, I'd like to be able to match the primary key of any component and pull up the information each item in the specific chain of components that the matching component exists within.
Sorry for the vagueness, but I can't speak on the specifics. Thank you.


texts:
I am currently interning and one of my tasks is to design a relational database model for a wedding firm that plans and handles weddings.

There is a user log in, and the users will be bride, groom, vendors and guests. I am not exactly sure how I should architect the database as it is the first time I am making one.

USER (user_id, username, password, bride_id, vendor_id, guest_id)

BRIDE (bride_id, bride_fname, bride_lname)

VENDOR (vendor_id, vendor_fname_lname)

GUEST (guest_id, guest_fname, guest_lname)

This is a somewhat version of the schema I was designing, but I am unsure how I can have everything on the USER table instead of repeating fname, and other redundancies. Also, the reason I have not put everything in the USER table is because I am not sure how it would work out. Can someone please explain this to me?


texts: Trying to insert only unique items into one table and then create a many to many relationship in a join table only if that join does not exist

texts:
Probably best to explain this with an example.

I have 3 tables two of which are 'regular' tables and the third which is a many to many relationship table joining the first two.

I have a stored procedure that creates a temp table, Then inserts the values from the temp table into an existing table B only if they dont exist, then creates a many to many relationship between table B and the other regular table. This works fine until I hit a value that has been inserted into B previously and has an existing relationship in the many to many table as well, then I get a constraint violation.
Where I'm having issues is trying to get the insert into the many to many join table to only occur if there is not existing entry.

CREATE TABLE A
    (ID INT PRIMARY KEY,
     Number INT)

CREATE TABLE B
    (ID INT IDENTITY(1,1) PRIMARY KEY,
     Name nvarchar(100))

CREATE TABLE AtoB(
[AId] [int] NOT NULL,
[BId] [int] NOT NULL,
 CONSTRAINT [AtoB_PK] PRIMARY KEY
(
[AId] ASC,
[BId] ASC
),
CONSTRAINT FKtoA FOREIGN KEY (AId) REFERENCES A(ID),
CONSTRAINT FKtoB FOREIGN KEY (BId) REFERENCES B(ID),
)

some initial values

insert into A ( ID, Number) Values (1,1000),(2,1001),(3,1234);

A Working (first time) insert

DECLARE @temptable TABLE (firstname varchar(200))
DECLARE @tableAid INT
DECLARE @tableBid INT

SET @tableAid = (SELECT ID FROM A WHERE  A.Number = 1001)

INSERT INTO @temptable (firstname) VALUES ('John'),('James'),('Bill')

INSERT INTO B (Name) SELECT TT.firstname FROM @temptable TT WHERE NOT EXISTS
( SELECT 1 FROM B WHERE B.Name = TT.firstname )

INSERT INTO AtoB (AId, BId ) SELECT B.Id,  @tableAid FROM B JOIN @temptable
T ON B.Name = T.firstname

The last part of this statement will fail ( as expected ) if you run it again but with some different values

INSERT INTO @temptable (firstname) VALUES ('John'),('Ted'),('Alice')

What I want to happen would be that on a second run Ted and Alice would be inserted with many to many relationships.  I've tried

INSERT INTO AtoB (AId, BId ) SELECT B.Id,  @tableAid FROM B JOIN @temptable
T ON B.Name = T.firstname WHERE NOT EXISTS
SELECT ( * FROM AtoB JOIN B ON AtoB.BId = B.Id JOIN @temptable ON B.Name =
@temptable.firstname WHERE AtoB.Aid = @tableAid And AtoB.BId = B.Id

and several variations but I usually end up with no values inserted into the many to many table at all.

Ideas on whats wrong?

Thanks

Long reply to answer below
That means that only if none of the items exist you need to insert.
Obviously, that's not what you want" - This is exactly what I want. In table B I want one one entry for each name. and this portion of the stored procedure works correctly
    INSERT INTO B (Name) SELECT TT.firstname FROM @temptable TT WHERE NOT
    EXISTS
    ( SELECT 1 FROM B WHERE B.Name = TT.firstname )

The problem starts when I get to the next portion where I want to create the many to many relationship but only if its a new name value in table B. If the values passed in that fill the temptable contain an item what was previously associated between A and B the statement is terminated and the new ( and previously uninserted into B ) values do not get entered.


texts: upgrading SQL Server 2017 Standard to 2019 Enterprise

texts: We are about to upgrade our SQL Servers from 2017 standard to 2019 enterprise.  Does anyone have any real world experience on the best way to do this?  We do not use HA, so our one prod server needs to be upgraded.  We have a "Staging" server that is a day old restore of the prod database so my first thought is to try to upgrade staging to 2019 and make sure everything works, then move Production to 2019 if there are no concerns.  Is the only "Backout" plan if we find something does not work that is a show stopper completely remove SQL on the prod server, reinstall 2017 and restore all system databases and user databases?  Is there a "Rollback" if there is an issue?  How have you done this?  upgrading to standard -> enterprise should not break anything since it just adds features, correct?


texts: SQL Server 2016 Availability Groups Named Pipes?

texts:
We have a SQL Server 2016 cluster configured (two VM's) with our databases spread across multiple Availability Groups (AG).

Our Sharepoint admin is reporting to me that some functionality is failing as it requires Named Pipes. Now, named pipes are enabled but from some very old articles (pre-2013) I can see that Named Pipes were not supported on multiple IP addresses, which would cause issues for the AG listeners.

Can anyone confirm if this is the case? Or point me in the right direction?


texts:
We have a few jobs that run nightly that the status has to be reported every hour.  I am trying to find in MSDB where I can capture the information below hourly and send out an excel file.

Server          Job Order - Name        NormalStartTime NormalEndTime   ActualStartTime ActualEndTime
Server A        1. JobA     11:00:00 PM     11:05:00 PM     11:00:00 PM     11:06:15 PM
Server A        2. JobB     11:05:00 PM     11:15:00 PM     11:06:16 PM     11:31:09 PM
Server A        3. JobC     11:15:00 PM     11:45:00 PM     11:31:09 PM     12:52:04 AM
Server A        4. JobD     11:45:00 PM     11:50:00 PM     12:52:04 AM     12:53:08 AM
Server A        5. JobE     11:50:00 PM     12:00:00 AM     12:53:09 AM     12:56:59 AM

Server = Server Name
Job Order - Name = This is the list of Jobs I want to search for.  The 1-5 is not in the job name.
NormalStartTime = Earliest Start time
NormalEndTime = Latest End Time
ActualStartTime = The time the job started on today's run
ActualEndTime = The time the job finished today's run


texts: Determine the source of user permissions in a database

texts:
I have a developer that just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment.

How can I track down what security group allowed such a behavior? I have the AD login used, as well as the target database.

I could go the long way around, looking into every single AD group and sub-group this user is a member of, and check for each one if it grants some kind of security in my SQL server. But it is going to take hours. And that would only cover one user out of a dozen.

I would like to track permissions (other than READ or VIEW DEFINITION), and their AD members. Either selecting an AD user and look through what they have access to, or the other way around, selecting a database and getting everyone that has access to to it (and how).

I can handle a PowerShell solution too if someone has some code to share (PS newbie).


texts:
I have a problem with left join query. It connects 3 tables and calculates sales value for categories.

Query:

SELECT
    categories.name,
    categories.id,
    SUM((orders_products.product_price_gross + orders_products.option_price)*(1 - (orders_products.rebate/100))  * orders_products.product_qty) AS suma
FROM product_categories
LEFT JOIN orders_products
    ON product_categories.product_id = orders_products.product_id
LEFT JOIN categories
    ON product_categories.category_id = categories.id
GROUP BY categories.name, categories.id
ORDER BY suma DESC

    categories
id  int(11) NO  PRI     NULL    auto_increment
parent_categories   int(11) NO      0
name    varchar(250)    NO

orders_products
id  int(11) NO  PRI     NULL    auto_increment
order_id    int(11) NO  PRI     0
product_id  int(11) NO      0
product_name    varchar(255)    NO
product_price_gross     double(8,2) NO      0.00
product_qty     int(3)  NO      0
option_price    decimal(8,2)    NO      NULL
discount    decimal(8,2)    NO      NULL
rebate  int(11) NO      NULL

product_categories
product_id  int(11) NO  MUL     0
category_id     int(11) NO      0
ord     int(11) NO      0
id  int(11) NO  PRI     NULL    auto_increment

Tables are big - categories - 2808 rows, product_categories - 84 000, orders_products - 47500

It should display list of categories and sales values for each one. Instead of that it hangs the server totally and restart is needed. I used many different queries from StackOverflow but any of them hangs server. I have no idea why.


texts: Inconsistencies with ADD CONSTRAINT index_name in MySQL

texts:
I created this example table

CREATE DATABASE foobar;
USE foobar;
CREATE TABLE foos (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  bar_id INT(11) DEFAULT NULL
);
CREATE TABLE bars (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL
);

Then ran this query

ALTER TABLE foos ADD CONSTRAINT constraint_name FOREIGN KEY foreign_key_name(bar_id) REFERENCES bars(id);

Some mysql server will return this on show create table foos

CREATE TABLE `foos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `bar_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `constraint_name` (`bar_id`),
  CONSTRAINT `constraint_name` FOREIGN KEY (`bar_id`) REFERENCES `bars` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then some other ones will return this on show create table foos

CREATE TABLE `foos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `bar_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `constraint_name` (`bar_id`),
  CONSTRAINT `foreign_key_name` FOREIGN KEY (`bar_id`) REFERENCES `bars` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Notice the differences in the index_name, it should be foreign_key_name.

At first I thought it was a problem between MySQL 5.5 vs 5.6, but that does not seem to be the case.

Does anyone know why sometimes the index_name is ignored?


texts: Is it possible to convert mysql binlog from statement format to row format?

texts: The mysql server online is of version 4.1, which doesn't support row-based binary log. Nevertheless, I need the row-based binlog. Can I use the binlog generated by old mysql and import it into another mysql of higher version that supports row-base binlog to get the row-based binlog?


texts: Create a result for a line chart with dates

texts:
I have this table in SQLite which I use to record the risk level identified in vulnerability assesments according to the day the scan was performed.
This table looks like this:

Scan Date  | Risk
2014-04-20 | 26
2014-04-24 |  60
2014-04-26 | 156
2014-04-28 |  26

What I want to do is  to build a table like the following in order to use it to display the results on a line graph day by day (including those days without scanning, where the risk level is the same as the last scanning):

  Date    | Risk
2014-04-20|  26
2014-04-21|  26
2014-04-22|  26
2014-04-23|  26
2014-04-24|  60
2014-04-25|  60
2014-04-26| 156
2014-04-27| 156
2014-04-28|  26

How can I do it using SQL?


texts: Centralized Backup report for SQL server databases

texts:
Even though, I've searched couple of them online but still looking if there is a better way to manage the SQL server backups, almost across 250 servers.

Issue is we are not allowed sysadmin login to be created on one server to pull this report from.

Please suggest a link or way to view the health report for sql server backups.

Also SSRS works as we are not supposed to use database mail to sen emails


texts: MySQL illegal mix of collations, ASCII to UTF-8

texts:
I have a MySQL 8.0 table like this, which is UTF-8 apart from one field, which holds an ASCII UUID that doesn't need UTF-8 overhead:
CREATE TABLE `things` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `things_uuid_unique` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and I'm getting query failures like this:
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations
(ascii_bin,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='
(SQL: select * from `things` where (`uuid` = 1abb9e11-4f00-4904-988e-233a3c0ce411) limit 1)

My connection is using utf8mb4 and utf8mb4_unicode_ci, the other fields, table, and database are using the same collation, and my scripts themselves are also UTF-8, though that makes no practical difference here since it's just ASCII. This particular query uses a string literal containing only 7-bit ASCII chars, not a field name or variable.
I don't understand why this fails, since (unlike ISO-8859 charsets), ASCII is UTF-8 compatible; there is no ASCII string that is not also a valid UTF-8 string, so why wouldn't this be coercible? Is it something to do with ci vs bin? I can't solve this by switching the connection to ASCII because the real queries use multiple fields, some of which are UTF-8.
I know that I can force the comparison using:
select * from `things` where (`uuid` = BINARY '1abb9e11-4f00-4904-988e-233a3c0ce411')

But that also seems unnecessary, and not something I've needed to do before, in the same circumstances. I guess worst case I could switch the UUID field to UTF-8, but that offends my developer sensibilities!
It's doubly frustrating as I know I've used this pattern successfully many times before!


texts: Database system checksumming to prevent network corruption not caught by TCP checksumming

texts:
Since TCP checksumming is not sufficient to detect/prevent all corruption, do modern database systems (e.g. SQL Server, MariaDB / MySQL, Oracle, etc.) use any additional checksumming of network traffic as part of their client/server protocol?

I did some searching but wasn't finding anything which made me wonder if perhaps they don't and just roll the dice.

Got a good answer on Oracle. Would like to hear about SQL Server and MariaDB  / MySQL as well.


texts: Postgresql not using GIN trigram index when performing non-ASCII LIKE query?

texts:
Steps to reproduce

Create database

CREATE DATABASE citiesdb
  WITH OWNER = citiesowner
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'C'
       LC_CTYPE = 'C'
       CONNECTION LIMIT = -1;

After creating database you can just run code from sql fiddle from the answer by Erwin https://dba.stackexchange.com/a/63202/37108 (http://sqlfiddle.com/#!12/270e2/1) or read additional info at the end of the question.

Run LIKE query with only ASCII characters

EXPLAIN ANALYZE SELECT * FROM city WHERE other_names_lower like '%ele%';

"Bitmap Heap Scan on city  (cost=16.10..64.02 rows=13 width=147) (actual time=0.642..3.303 rows=513 loops=1)"
"  Recheck Cond: (other_names_lower ~~ '%ele%'::text)"
"  ->  Bitmap Index Scan on other_names_lower_trgm_gin  (cost=0.00..16.10 rows=13 width=0) (actual time=0.486..0.486 rows=513 loops=1)"
"        Index Cond: (other_names_lower ~~ '%ele%'::text)"
"Total runtime: 3.439 ms"

Run LIKE query with non-ASCII characters

explain analyze SELECT * FROM city WHERE (other_names_lower like '%желез%')

"Seq Scan on city  (cost=0.00..1693.53 rows=5 width=134) (actual time=33.498..58.688 rows=9 loops=1)"
"  Filter: (other_names_lower ~~ '%желез%'::text)"
"  Rows Removed by Filter: 46673"
"Total runtime: 58.753 ms"

Question

When searching for non-ascii text the engine is using sequential scan instead of GIN trigram index. Why is it doing that and what are alternative ways to construct the index, query or database to speed up the lookup?

Additional info

PostgreSQL 9.2; Windows 8 64-bit.

Part of table definition ([...] are other columns).

CREATE TABLE city ([...] other_names_lower text [...]) WITH ( OIDS=FALSE );

Column other_names_lower contains different names for cities. Rows contain Chinese, Polish, Russian and other character ranges.

Index creation code

CREATE EXTENSION pg_trgm;

CREATE INDEX other_names_lower_trgm_gin
  ON city
  USING gin
  (other_names_lower gin_trgm_ops);

Other settings - query suggested by Daniel Vérité in the comment

select name, source, setting from pg_settings where source <> 'default' and source <> 'override';

"application_name";"client";"pgAdmin III - Narz??dzie Zapytania"
"bytea_output";"session";"escape"
"client_encoding";"session";"UNICODE"
"client_min_messages";"session";"notice"
"DateStyle";"session";"ISO, YMD"
"default_text_search_config";"configuration file";"pg_catalog.simple"
"enable_seqscan";"session";"on"
"lc_messages";"configuration file";"en_US.UTF-8"
"lc_monetary";"configuration file";"Polish_Poland.1250"
"lc_numeric";"configuration file";"Polish_Poland.1250"
"lc_time";"configuration file";"Polish_Poland.1250"
"listen_addresses";"configuration file";"*"
"log_destination";"configuration file";"stderr"
"log_line_prefix";"configuration file";"%t "
"log_statement";"configuration file";"all"
"log_timezone";"configuration file";"Europe/Sarajevo"
"logging_collector";"configuration file";"on"
"max_connections";"configuration file";"100"
"max_stack_depth";"environment variable";"2048"
"port";"configuration file";"5432"
"shared_buffers";"configuration file";"4096"
"TimeZone";"configuration file";"Europe/Sarajevo"


texts: Is it bad to assign specific processors to SQL Server 2008 R2 cluster instances?

texts:
I have a SQL Server 2008 R2 failover cluster with a passive node and an active node.  The servers are physically identical.  I have two instances of SQL Server, one set to use all the CPUs in the first 3 NUMA nodes, the other set to use all the CPUs in the 4th NUMA node.

This seems to be working okay, even when failover occurs, but is this a bad idea?

What would happen in failover if the passive server didn't have as many processors?


texts: Big values for Hash Match with this query ( Lots of sub selects )

texts:
I'm having problem to run this query.

SELECT
    JROS.ID_DATA_JMTM,
    JROS.ID_CEL_JMTM,
    JROS.TXM,
    JROS.DATA,
    JROS.NO_COL_JMTM,
    MDU.QT_PRAZODIASU,
    MDU.CD_RENDA_FIXA,
    MDU.VL_FUTURO,
    MDU.DT_REF_TRANS,
    MDU.FIXING,
    0.00 AS "VL_MTM",
    MDU.TIPO
    FROM
    (SELECT
    B.ID_DATA_JMTM,
    B.ID_CEL_JMTM,
    (B.VL_MTM*FTR1+C.VL_MTM*FTR2)/DENOM AS TXM,
    B.DATA,
    B.ID_COL_JMTM,
    B.FATOR + CONVERT(VARCHAR,A.DU) AS NO_COL_JMTM

FROM(   SELECT
        DU,SUBSTRING(NO_COL_JMTM,1,3)as NO_COL_JMTM,VERT1,PAR_FIM as VERT2,
            (PAR_FIM-DU) AS FTR1,(DU-VERT1) AS FTR2,PAR_FIM-VERT1 AS DENOM
        FROM PARAMETRO_MTM P
        ,(  SELECT DISTINCT CASE RISCO_JUROS
            WHEN 'N' THEN 'PRE'+ CAST( QT_PRAZODIASU AS varchar(17) )
            ELSE NO_COL_JMTM
            END AS NO_COL_JMTM ,QT_PRAZODIASU AS DU
            FROM RENDA_FIXA_MDU A      WITH (NOLOCK)
            WHERE RTRIM(LTRIM(NO_COL_JMTM)) <> ''
         ) VALOR
       WHERE DU>=VERT1 AND DU<PAR_FIM
    ) A,
    (
    SELECT
    DT_JMTM AS DATA,
    SUBSTRING(NO_COL_JMTM,1,3) AS FATOR,
    CAST(SUBSTRING(NO_COL_JMTM,4,4) AS INT) AS VERT,
    VL_MTM ,
    B.ID_DATA_JMTM,
    C.ID_CEL_JMTM ,
    A.ID_COL_JMTM
        FROM JUROS_MTM A
        ,JUROS_MTM_DATA B
        ,JUROS_MTM_CEL C
        WHERE A.ID_COL_JMTM = C.ID_COL_JMTM
        AND B.ID_DATA_JMTM = C.ID_DATA_JMTM
        AND DT_JMTM  between cast('2016-02-01' as date) and cast('2016-04-13' as date)
    ) B,
    (   SELECT DT_JMTM AS DATA,SUBSTRING(NO_COL_JMTM,1,3) AS FATOR,CAST(SUBSTRING(NO_COL_JMTM,4,4) AS INT) AS VERT,VL_MTM ,B.ID_DATA_JMTM,C.ID_CEL_JMTM ,A.ID_COL_JMTM
        FROM JUROS_MTM A
        ,JUROS_MTM_DATA B
        ,JUROS_MTM_CEL C
        WHERE A.ID_COL_JMTM = C.ID_COL_JMTM
        AND B.ID_DATA_JMTM = C.ID_DATA_JMTM
        AND DT_JMTM  between cast( '2016-02-01' as date) and cast('2016-04-13' as date)
    ) C

WHERE A.NO_COL_JMTM = B.FATOR
AND A.NO_COL_JMTM = C.FATOR
AND B.VERT = A.VERT1
AND C.VERT = CASE WHEN A.DU >= 2600 THEN 2600 ELSE A.VERT2 END
AND B.ID_DATA_JMTM = C.ID_DATA_JMTM
) JROS
INNER JOIN RENDA_FIXA_MDU MDU
ON MDU.NO_COL_JMTM = JROS.NO_COL_JMTM

I don't know what i'm doing wrong, because I want to select between 2017 / 2016' for exmaple, but it's taking 2 minutes to list 1 month ( After I add an index on JUROS_MTM_DATA ( DT_JMTM) include (ID_DATA_JMTM)

This is the query plan ( part of it ):

coudn't solve the problem with this information.

Probably, using SQL Sentry, the Merge Join ( table - renda_fixa_mdu) has EST ROWS is 50millions


texts: How come systemctl status postgresql.service and systemctl status postgresql@10-main show different results?

texts:
Not sure I really understand what's going on.

Originally, I'm trying to connect to a remote host (which I can ping, access via SSH) but cannot connect through psql

Error: psql: could not connect to server: Connection refused Is the server running on host <HOST> and accepting TCP/IP connections on port 5432?

After checking a couple of things, I stumbled upon different results from postgresql.service and postgresql@10-main :

postgresql.service -- active (exited)

  ~ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;
vendor preset: enabled)
   Active: active (exited) since Don 2018-01-04 15:24:14 CET; 2min 29s ago
  Process: 10954 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 10954 (code=exited, status=0/SUCCESS)

postgresql@10-main -- active (running)

  ~ systemctl status postgresql@10-main
● postgresql@10-main.service - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled)
   Active: active (running) since Don 2018-01-04 15:24:14 CET; 5min ago
  Process: 10920 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop (code=exited, status=0/SUCC
  Process: 10929 ExecStart=postgresql@%i --skip-systemctl-redirect %i start (code=exited, status=0/SUCCESS)
 Main PID: 10936 (postgres)
   CGroup: /system.slice/system-postgresql.slice/postgresql@10-main.service
           ├─10936 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/1
           ├─10938 postgres: 10/main: checkpointer process
           ├─10939 postgres: 10/main: writer process
           ├─10940 postgres: 10/main: wal writer process
           ├─10941 postgres: 10/main: autovacuum launcher process
           ├─10942 postgres: 10/main: stats collector process
           └─10943 postgres: 10/main: bgworker: logical replication launcher

Jan 04 15:24:12 floppy systemd[1]: Starting PostgreSQL Cluster 10-main...
Jan 04 15:24:14 floppy systemd[1]: Started PostgreSQL Cluster 10-main.

Is this related to my problem with connecting to the remote host ?
Why are two different services running (showing different results) ?

PS: I'm running on Ubuntu 16.04

Thanks a lot


texts: xp_cmdshell called from procedure WITH EXECUTE AS OWNER

texts:
I am trying to understand details of wrapping xp_cmdshell functionality in user defined stored procs, so that other users can just be given execute permission to the stored procs rather than xp_cmdshell.

The steps in this scenario are:

xp_cmdshell is already enabled and a xp_cmdshell proxy has been created
A user with db_owner membership creates a stored proc WITH EXECUTE AS
OWNER which calls xp_cmdshell
The user executes the stored proc and therefore executes arbitrary shell code

This is unexpected to me.  I would not expect a user with only db_owner to be able to achieve this.  (Obviously assuming xp_cmdshell has already been enabled by a sysadmin.)

When the database owner is changed from sa to another low privileged login, then the user stored proc is no longer able to call xp_cmdshell.

USE MASTER;
CREATE DATABASE testdb;
CREATE LOGIN testuser WITH PASSWORD = 'password', CHECK_POLICY=OFF;
CREATE LOGIN dummyuser WITH PASSWORD = 'password', CHECK_POLICY=OFF;
SELECT * from sys.credentials WHERE NAME LIKE '%cmdshell%';
-- returned: 101 ##xp_cmdshell_proxy_account## .....

USE testdb;

EXEC sp_changedbowner 'sa';
CREATE USER testuser FOR LOGIN testuser;
ALTER ROLE db_owner ADD MEMBER testuser;

EXECUTE AS LOGIN = 'testuser';
GO

CREATE PROCEDURE [dbo].[testproc]
WITH EXECUTE AS OWNER
AS
    SELECT SUSER_NAME() as [SUSER_NAME()], USER_NAME() as [USER_NAME()];
    exec xp_cmdshell 'echo %time%';
GO

SELECT SUSER_NAME() as [SUSER_NAME()], USER_NAME() as [USER_NAME()];

EXEC dbo.testproc;
-- returned: sa dbo  proving that the call to xp_cmdshell has succeeded

EXEC xp_cmdshell 'echo %time%';
-- returned: The EXECUTE permission was denied on the object 'xp_cmdshell'

REVERT

EXEC sp_changedbowner 'dummyuser';
EXECUTE AS LOGIN = 'testuser';
EXEC dbo.testproc;
-- returned: The EXECUTE permission was denied on the object 'xp_cmdshell'
-- proving that the sysadmin role of the database owner is relevent

REVERT

Note that I have not granted execute xp_cmdshell permission to any particular user.

I thought that enabling xp_cmdshell was ok if care was taken to only grant execute xp_cmdshell permission carefully, but my example seems to show otherwise.

Since a sysadmin is often a database owner, does this example show a serious security problem, or am I misunderstanding something?


texts: Why Wikipedia using blob type instead of text type?

texts:
As I experienced, Blob type is good choice when you want to save binary data such as images or videos etc and Text type is good choice when you want to save large string.

Today I saw Wikipedia database. The interesting thing was that wiki use medium blob type for storing wikitext of the page.

So I want to know why Wiki prefer to use Blob type instead of Text?

Thanks


texts: Select Into a table on server1 from server2 without using 'Linked Servers'

texts:
I am trying to get all the records from one table into another table but these tables (which are identical) are on separate servers.

Any help would be appreciated.

Thanks


texts: mysqldump locks all tables even when specifically grabbing a few?

texts: I'm using a mysqldump command pulling data from 3 tables of 7. Are the other 4 locked as well?


texts: Disconnect specific database from pgpool II

texts:
I'm using PostgreSQL 9.5 with pgpool II version 3.4.13 on Ubuntu 16.04.3
I want to drop a specific database so I must disconnect the database from pgpool.
How can I do this without disconnecting the whole PostgreSQL instance from pgpool?

I myself use below command:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'dbName'
AND pid <> pg_backend_pid();

...but after executing, I lose all the database connections in pgpool.

Thanks in advanced for any attention.


texts: Should I be alarmed by this NO JOIN PREDICATE warning?

texts:
I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning

select
    method =
        case methoddescription
            when 'blah' then 'Ethylene Oxide'
            when NULL then 'N/A'
            else methoddescription
        end,
    testmethod =
        case methoddescription
            when 'blah' then 'Biological Indicators'
            when NULL then 'N/A'
            else 'Dosimeter Reports'
        end,
    result =
        case when l.res is null or l.res <> 1 then 'Failed'
        else 'Passed'
        end,
    datecomplete = COALESCE(CONVERT(varchar(10), NULL, 101),'N/A')
from db2.dbo.view ls
    join db1.dbo.table l
        on ls.id = l.id
    where item = '19003'
        and l.id = '732820'

The view ([ls]) calls a remote server (the remote query %41 on the right of the plan).

Here is an image of the plan:

I only ask this question because of this blog post and I want to make sure this won't come back to bite me later.