texts: Wordpress query on MyISAM tables in MariaDB on ZFS takes too long to execute

texts:
I use a pretty common setup, some wordpress sites running on Debian 10 (Buster) with mariadb 10.3.27 on ZFS, on a decent server with SSDs and lots of RAM, pretty smooth operation with low CPU utilization. There is this query wordpress makes,
SELECT DISTINCT meta_key
FROM wp_postmeta
INNER JOIN  wp_posts ON post_id = ID
WHERE post_type <> 'product'
  AND post_type <> 'product_variation';

For some reason I cannot possibly imagine, on database A (InnoDB tables) it runs in about 3 seconds, and on database B (MyISAM tables) it takes over 2 minutes. wp_postmeta has 198938 records and wp_posts 25189 in database B, where on database A they both have many more records. I've tried dumping, deleting the database and recreating it to no avail. If I move the database to another similar server with EXT4, the query on MyISAM tables finishes in about 4 seconds. If I alter the tables to InnoDB, query execution finishes in about 4 seconds. Also, when running the query on MyISAM tables on ZFS I noticed the disk reads spike to about 50MB/s and up to 80MB/s while the query is running. 2 minutes of reads at 50MB/s is 6GB of data, in a database with a total size less than 500MB.
This difference in execution time between MyISAM and InnoDB was noticed on Debian systems with ZFS 0.6 and 0.7. The exact same databases on a FreeBSD/ZFS server display no such difference, with queries executed within a a few seconds for both MyISAM and InnoDB tables.
Does anybody know what could make such a huge difference between MyISAM and InnoDB on ZFS on Linux, and why ZFS on FreeBSD does not show this behavior?
The tables created as:
CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
  `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT 0,
  `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=MyISAM AUTO_INCREMENT=33318 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT 0,
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM AUTO_INCREMENT=279035 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts: Is this a step in the right direction for normalization? SQL 'inheritance' and Many-to-Many relationships

texts:
Context:

So, this is kind of a compound question, but they go together, and definitely wouldn't make sense split up into separate questions.

I've been working on a new database for what will end up being a programmer's blog/portfolio, with the occasional tutorial or demonstration, written in ASP.NET MVC 5 and Entity Framework 6. About halfway into designing the database schema, I noticed almost all of the tables contained the same 5 basic columns: ID, Title, Description, DatePosted, and URLStub. While not necessarily redundant, as most of the data would be unique, except maybe where an article is linked to a Demo, it looks wrong or sloppy.

Models:

This is the model I initially set out making:

I swear I auto-arranged/auto-sized those tables

And this is what I ended up shifting towards, but I still feel like I'm doing something stupid:

Questions:

Is the new model even plausible with entity framework?
Is it an improvement (more normal) over the first one?
Should I have even worried about the first one?
Can anyone see anything inherently stupid with the new model?

Thank you in advance for any input, and also sorry in advance if this is going to "create too much discussion" or is "primarily based on opinion".. I don't really know a better place to ask this, and get knowledgeable answers.


texts: PostgreSql 12 - Can't see table details \dt (table) but can see all tables \dt

texts:
I have a custom schema and one table in it. Following answers about searching through custom schemas I have modified by search_path to include new schema.
I can see now all objects in the new schema, so this one works:
xekata_dev=> \dt
                List of relations
 Schema |     Name     | Type  |      Owner
--------+--------------+-------+-----------------
 xekata | TestNodeBase | table | xekata_user_web
(1 row)

Now when I want to see table details I do:
xekata_dev=> \d TestNodeBase
Did not find any relation named "TestNodeBase".

which shows nothing... :(
I tried different ways to but nothing works:
xekata_dev-> \d xekata.TestNodeBase
Did not find any relation named "xekata.TestNodeBase".

I am logged as xekata_user_web and my search_path is:
xekata_dev=> show search_path;
  search_path
----------------
 xekata, public

What's going on? Why I can't see table details?


texts: How to move SSAS tabular instance databases?

texts: We have a SQL Server 2012 SSAS tabular instance in our environment that is sitting in domain1. We need to move these SSAS databases in a different SQL server 2012 that is on domain2 which has no trust established with domain1. This is the first time I am tasked to move SSAS in to another server. What would be a good approach in moving these SSAS tabular databases? Is a normal backup and restore would do the process? Or does it have to be attach detach process? Also, when I migrated our SQL Server databases, I have to adjust each account logins since there is not trusts between domains. Do I have to do the same thing with SSAS in this case?


texts: Restore database from script created by the "Generate and Publish Scripts" wizard

texts:
I have this database I need to move to a test server. The only way I can do this according to my searches is to create a script. The database is on SQL Server 2014, I am using 2012). I can get the script to generate (1.3 gigs), I am using sqlcmd to run it, but I have run into issues.

The first issue was that it was complaining about the database not existing, so I created a database with the same name and hoped I got the settings right. This solved that issue, but now it is complaining about the users not existing or me not having permission (I ran the script as the 'sa' user), and not all stored procedures and assemblies get created.

I just need a copy of the database to test against so I can find an issue without breaking the live database. Could someone help me with this, please?

I am unable to upgrade the database. The only thing I have permission to do is copy/backup the database, so long as it does not disrupt it. On the test-server side, it is a virtual machine that I restored from backup with Windows Server 2012 and SQL Server 2012 already installed on it.

I seem to have misunderstood part of what the script does. It does not re-create the database, but sets it up again. I was able to make it work when I create a new database, set up the database so that everything would be able to be recreated (set unsafe assemblies on and such). The users do not get recreated, and must be created, given permissions, etc. manually, as the script will try to log into the windows users with the old server name. All the script does is set the tables, stored procedures, assemblies, etc. and fill them with data. I wish they would have been more clear on that.


texts:
We have 3-rd party application. In one of the reports it gives us error message:
Microsoft OLE DB Provider for SQL Server: Subquery returned more than 1 value. This is not permitted...
I need to find which statement causes this error.
The report duration is quite long - about several hours.
I want to setup extended event session to catch this error.
Am I right?
Are there any other ways (except xEvents and trace) to grab this erroneous SQL statement ?


texts: Mysql hand machine due to high load average and CPU

texts:
Mysql is used by only one application which only fire query periodically.
When i restart server,It run smooth for period of time.
and then crash the whole server.

My my.cnf file

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table=1
set-variable = max_connections=200
log-slow-queries=/var/lib/mysql/slow.log
safe-show-database
query_cache_size=128M
join_buffer_size=50M
tmp_table_size=128M
max_heap_table_size=128M
table_cache=10K
long_query_time =10
open_files_limit=50000
table_open_cache=20000
table_cache=20000
query_cache_limit=134217728
innodb_buffer_pool_size=900M

[mysqld_safe]

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


texts: Update table with data from table in a separate database

texts:
TableA
ID Address Location Country
1   NULL     Region1  Austrailia
2   NULL     Region2  Antartica
3   NULL    Region3   Egypt

TableB
ID  Address        Name
1   test avenue    james
2   test avenue 2  jessica
3   test avenue 3  joshua

I am working on a query to migrate data Address data from Database 1, TableB to Database 2 TableA. So far I have tried to do the query below however that only inserts one record for the entire database, I want to insert each new sequential address into TableA as it appears in TableB.

UPDATE [dbo].[TableA]
SET [dbo].[TableA].[Address]= Table2.ID
FROM (SELECT [TableB].[ID]
        FROM [TableB]) AS Table2
WHERE [dbo].[TableA].[ID] <= 1449

This results in all records only having the first ID which is 1, so all the rows in TableA are Address = 1, instead of 1,2,3 etc. I think I am close but not quite sure what I am missing

Desired Result

TableA
    ID Address Location Country
    1   1        Region1  Austrailia
    2   2        Region2  Antartica
    3   3        Region3   Egypt


texts: Query optimization with conditional group by and where

texts:
I've a DB with events, workshops in each event (1:N), participants in each workshop/event(N:M), role of participant in each workshop, and a table (letture) with access control for each participant in workshop.

We create a unique qrcode for participant/workshop. With this qrcode we can identify who went to which workshop and we can calculate for how many hours he remained.

But not all workshops need this in/out (entrata=1/2) value, for some are sufficient check who are present (entrata=0).

To print the final certificate for every participant in every event/workshop I try to populate the table attestati_dati with this query :

TRUNCATE attestati_dati;
INSERT INTO attestati_dati
(id_participant,id_event,id_workshop,id_role,id_attestato,ore_totali)
SELECT * FROM
(
   SELECT
     p.id_participant AS id_participant,
     i1.id_event AS id_event,
     i1.id_sala AS id_workshop,
     r.id_role AS id_role,
     1 AS id_attestato,
     SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND,
             i1.datalettura,
             IFNULL(i2.datalettura, i1.datalettura)))) AS ore_totali
FROM
  letture AS i1
     LEFT JOIN
 letture AS i2 ON (i1.qrcode = i2.qrcode
     AND i2.datalettura > i1.datalettura
    AND i1.id_event = i2.id_event
    AND i1.id_sala = i2.id_sala
    AND i2.entrata = 2)
    LEFT JOIN
letture AS i3 ON (i3.qrcode = i2.qrcode
    AND i3.datalettura > i1.datalettura
    AND i1.id_event = i3.id_event
    AND i1.id_sala = i3.id_sala
    AND i3.entrata = 2
    AND i3.datalettura < i2.datalettura)
    CROSS JOIN
event_participants ep ON ep.qrcode = i1.qrcode
    AND ep.id_event = i1.id_event
    LEFT JOIN
participants p ON p.id_participant = ep.id_participant
    LEFT JOIN
roles r ON ep.id_role = r.id_role
    LEFT JOIN
events e ON i1.id_event = e.id_event
    LEFT JOIN
workshop_participant wp ON wp.id_participant = p.id_participant
    AND wp.id_workshop = i1.id_sala
WHERE
 i1.entrata = 1
     AND i3.datalettura IS NULL
     AND i1.id_Event = 52
GROUP BY i1.qrcode
UNION SELECT
     p.id_participant AS id_participant,
     i1.id_event AS id_event,
     i1.id_sala AS id_workshop,
     r.id_role AS id_role,
     1 AS id_attestato,
    SEC_TO_TIME(0) AS ore_totali
FROM
 letture AS i1
     CROSS JOIN
 event_participants ep ON ep.qrcode = i1.qrcode
     AND ep.id_event = i1.id_event
     LEFT JOIN
 participants p ON p.id_participant = ep.id_participant
     LEFT JOIN
 roles r ON ep.id_role = r.id_role
     LEFT JOIN
 events e ON i1.id_event = e.id_event
     LEFT JOIN
 workshop_participant wp ON wp.id_participant = p.id_participant
     AND wp.id_workshop = i1.id_sala
WHERE
    i1.entrata = 0 AND i1.id_Event = 52
GROUP BY i1.qrcode , DAYOFMONTH(i1.datalettura)
) A;

I'd like to improve this query because this is the best I can do to solve this problem, but I think that is optimizable, specially in the group by/where/joins part.

Show Create table:

  CREATE TABLE `attestati_dati` (
 `id_attestati_participants` int(11) NOT NULL AUTO_INCREMENT,
 `id_participant` int(11) DEFAULT NULL,
 `id_event` int(11) DEFAULT NULL,
 `id_workshop` int(11) DEFAULT NULL,
 `id_role` int(11) DEFAULT NULL,
 `id_attestato` int(11) DEFAULT NULL,
 `ore_totali` time DEFAULT NULL,
  PRIMARY KEY (`id_attestati_participants`)
 ) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;


texts:
Is it possible to determine what SPID was created to execute a SSRS report?

I am trying to prevent users from running the same report multiple times.  So what I was thinking of doing was retrieve the SPID that was created after a report gets executed.  Then if they click run again, it kills that SPID and runs the new request.

Any alternate ways to do this would be welcomed!


texts: How many partitions should I set for my MySQL table?

texts:
I have a table which contains 3.5M rows of sales data (~4 years).

Currently some of my queries (done from php) take 6-15 seconds to complete.  (indexes are useful, as per explain plans). My next step is to partition the table to see if I can squeeze more out of it, without upgrading hardware during development phase of the application.

When the table is queried, at the very most a whole, single year's worth of data is retrieved, sometimes finer like QTR or month.

When the application is 'released', it is anticipated that the data in the table will grow, at a rate of about 900k rows per year.

This being the case. Should I partition only the amount of years I hold in the db currently? assuming that number can be changed down the line. Or should I create more partitions?

The thought which led me to my question is:  If I create more partitions than I have years, then queries will need to get data from multiple partitions, will the performance of this case be worse than if my query is only needing to select data from 1 much larger partition?


texts:
I have a question related performance DB MySQL. I have querying like this with big data inside :

SELECT
       *
FROM
       tableA a
       INNER JOIN tableB b ON a.id = b.id AND a.code = b.code
       INNER JOIN tableC c ON a.uniq = c.uniq
WHERE
       a.date = '2020-01-01'
       AND a.type = 'X'
       AND b.merk =  'New';

My question is, what is better using single index or multi-column index?

Ex :

Index in tableA (Example1) :

idx1 (merk, code, date, type)
idx2 (date)
idx3 (merk)
idx4 (code)
idx5 (type)

Index in tableA (Example2) :

idx1 (merk, code, date, type)
idx2 (merk, code)
idx3 (date, type)

What is better? Example1 or Example2? And please explain it.

Notes : maybe tableA will got varian WHERE on another query for future

Thank you


texts: Segmentation fault (core dumped) with self-complied mysql-5.1.9-beta.tar.gz

texts:
I am compiling mysql-5.1.9-beta.tar.gz source code under Ubuntu 12.04. My steps are following:

tar -xzf mysql-5.1.9-beta.tar.gz
cd /home/gaoyu/mysql-5.1.9-beta
./configure --prefix=/home/gaoyu/mysql-5.1.9 --with-debug
sudo make
sudo make install
sudo cp /home/gaoyu/mysql-5.1.9-beta/support-files/my-medium.cnf /etc/my.cnf
cd /home/gaoyu/mysql-5.1.9
bin/mysql_install_db --user=mysql
sudo cp /home/gaoyu/mysql-5.1.9-beta/support-files/mysql.server /etc/init.d/mysql
sudo bin/mysqld_safe --user=mysql &

The bug is :

gaoyu@FlashGroup:~/mysql-5.1.9$ sudo bin/mysqld_safe --user=mysql &
[1] 26196
gaoyu@FlashGroup:~/mysql-5.1.9$ nohup: ignoring input and redirecting stderr to stdout
Starting mysqld daemon with databases from /home/gaoyu/mysql-5.1.9/var
^C

(here I wait for a very long time, so I must press Ctrl + C to stop it and continue)

gaoyu@FlashGroup:~/mysql-5.1.9$ bin/mysql
Segmentation fault (core dumped)

After I input sudo bin/mysqld_safe --user=mysql &, I must wait for a very long time. Why is that?
Why do I get a segfault?
Has anyone encountered a similar problem?


texts: in Neo4j my Cypher query pulls data from most recently loaded object, not the one specified

texts:
I believe this code demonstrates a bug in this software.  But given my lack of experience with Neo4j, maybe something is coded incorrectly.

I would like to know if the unexpected output is due to a bug.

Given 2 tiny .csv files, I load these data and when I query each loaded object the data seems ok.

Then when I query object T7 it shows the data from object T8.

Thanks!

Steps to reproduce the behaviour

File t7.csv

col1
t7 1 row

File t8.csv

col1
t8 2 rows
t8 2 rows

I then login with the following command:

$ bin/cypher-shell -u neo4j -p my password

I then run the following commands:

MATCH (T7)
OPTIONAL MATCH (T7)-[r]-()
WITH T7,r LIMIT 5000
DELETE T7,r
RETURN count(T7) as deletedNodesCount;

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:///t7.csv" as line
CREATE (T7 {col1:line.col1});

MATCH T7 = (t7)
RETURN t7.col1;

The above commands load the data and return the values found as follows:

+------------+
| a.col1     |
+------------+
| "t7 1 row" |
+------------+

I perform the same task for the T8 data:

MATCH (T8)
OPTIONAL MATCH (T8)-[r]-()
WITH T8,r LIMIT 5000
DELETE T8,r
RETURN count(T8) as deletedNodesCount;

LOAD CSV WITH HEADERS FROM "file:///t8.csv" as line
CREATE (T8 {col1:line.col1});

MATCH T8 = (t8)
RETURN t8.col1;

The above commands load the data and return the values found as follows:

+-------------+
| a.col1      |
+-------------+
| "t8 2 rows" |
| "t8 2 rows" |
+-------------+

Now if I run the following command, this gets data for most recent query, but not the correct node?

MATCH T7 = (t7)
RETURN t7.col1;

I expected this output:

+------------+
| a.col1     |
+------------+
| "t7 1 row" |
+------------+

Instead I received the following output:

+-------------+
| a.col1      |
+-------------+
| "t8 2 rows" |
| "t8 2 rows" |
+-------------+

Version information

Neo4j

call dbms.components() yield name, versions, edition unwind versions as version return name, version, edition;

+----------------------------------------+
| name           | version | edition     |
+----------------------------------------+
| "Neo4j Kernel" | "3.5.1" | "community" |
+----------------------------------------+

OS

Mac OS 10.13.6


texts: How to Prevent a Many to Many When Number of Rows Shared Is Unknown

texts:
I have two tables that look like the following:

Subjects
id|subject_name|..some other columns
1 |maths       |
2 |english     |
3 |science     |

Users
id|username|...some other columns
1 |Frank1  |
2 |Bob2    |
3 |Mike    |

A user should be able to have many subjects however the number of subjects they will have is unknown, is there some way to create a table that would prevent a many to many and if not what would be the best way of storing the subjects in the Users table?


texts: ORACLE 11g Know Insert Record Details which Failed to insert

texts:
I have started auditing insert records by user on failure to any table in my oracle 11g Database. I have used following command to do the same.

AUDIT INSERT ANY TABLE BY SHENA BY ACCESS WHENEVER NOT SUCCESSFUL;

I would like to know whenever the record insert will fail, Can i know what was the records which failed to insert into table.

Where we can see such information. Or if you know any other way of auditing of the same please suggest. One way which i know is to write a trigger on insert. In that trigger handle insert failure EXCEPTION and save those values to some table.


texts:
When I run the following SQL command

select payee_id, start_dt,
       row_number() over(partition by payee_id order by start_dt) as rn
from xxx
where rn = 1

I got an error :

  error Invalid identifier 'rn'

Please advise.


texts: How to reuse dataset in that stored procedure

texts:
First, I need to get a dataset then I want to use that dataset more then one time.

   SELECT @FullTimeEmpResult =
   ( select * from emp where type="FullTime"
   );

 SELECT * from @FullTimeEmpResult where salary >5000;

 SELECT * from @FullTimeEmpResult where YearOfExperience> 5;

how I can do this. Thanks in advance.


texts: Link DAX OpenQuery Output to SQL Server Temp Table

texts:
I want to record measures from a Tabular model, as KPIs, and store the values in a table in SQL Server.

I have created a linked server from my SQL Server instance to my SSAS instance.
I have written a stored procedure to execute the DAX code via OPENQUERY, with the intention of storing these in a temp table, before loading them in to the KPI table. I am using a temp table because I am querying multiple tabular models.

My problem occurs when I try to update my temp table with values from my OPENQUERY output. My OPENQUERY output is currently within a CTE, and
I was hoping to do a simple join to the temp table, but because the output from the DAX query returns each column name within [ ], when I try to join on one of the OPENQUERY columns I receive the error "Invalid column name...".

E.g.

UPDATE temp
SET temp.[Current Contract Count] = cte.[Contract Count]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[Copy of Service Zone Code] = temp.[ServiceZoneAlternateKey]

The error occurs because 'Copy of Service zone' does not exist in the OPENQEURY output; the output column name is [Copy of Service Zone].

I may well be missing a simple trick here?

How can I join an OPENQUERY output, returning tabular model data, to my T-SQL temp table?


texts: Random row selection but with distinct values for a subset of columns

texts:
I have a table that looks like this :

Key1 | Key2 | Key3 | Data  |
-----|------|------|-------|
   1 |   1  |   1  |   a   |
   1 |   1  |   2  |   b   |
   1 |   2  |   1  |   c   |
   1 |   2  |   2  |   d   |
   2 |   1  |   1  |   e   |
   2 |   1  |   2  |   f   |
   2 |   2  |   1  |   g   |
   2 |   2  |   2  |   h   |
---------------------------|

Here is some code to create that example table :

CREATE TABLE Example(
Key1 int,
Key2 int,
Key3 int,
Data varchar(1));

INSERT INTO Example(Key1,Key2,Key3, Data)
VALUES (1,1,1,'a'),
(1, 1,2,'b'),
(1, 2,1,'c'),
(1, 2,2,'d'),
(2, 1,1,'e'),
(2, 1,2,'f'),
(2, 2,1,'g'),
(2, 2,2,'h');

I want to randomly select one whole row for each distinct pair of Key1 and Key2 values (one for the 1 / 1 pair, one for the 1 / 2 pair etc.) One possible result would be :

Key1 | Key2 | Key3 | Data  |
-----|------|------|-------|
   1 |   1  |   1  |   a   |
   1 |   2  |   1  |   c   |
   2 |   1  |   2  |   f   |
   2 |   2  |   1  |   g   |
---------------------------|

I can do this doing multiple queries, by first selecting all the Key1 and Key2 distinct pairs and then using those pairs to run an other query like

SELECT stuff FROM table WHERE Key1 = value1 AND Key2 = value2 ORDER BY RAND() LIMIT 1`

But this gross approach needs to ask as many queries as existing pairs of Key1/Key2 and is taking forever since my table is huge.

I've read things about using subqueries, partition, group by, but I struggle to implement them.
I'm new to SQL and only need to use it for a specific project and I don't really have the time to learn it properly, so I would be very thankful if you guys could give me a hand.

Thanks
JC


texts: Snapshot replication error two days in a row

texts:
We have a mature replication system to move data from OLTP to BI.  It's been working for years with relatively few troubles. This week we've run into the same problem two days in a row with snapshot publications.

The first error was BCP failed to insert due to a NULL constraint issue on the target.  Interestingly, the source data has a non null constraint and therefore none of the source data has nulls.  Note, that this publication hasn't changed and neither has the source or destination schemas.  It worked one day, failed the next.  And to fix it, we simply reinitialized and reapplied the snap.

The next day, a similar issue - different publication, slightly different error (duplicate primary key (0,NULL)).  It's worked every day for months before and to fix it, we reinit the snap and it works.

Looking deeper we see the bcp file that it fails on is MUCH larger than the other files in the group (1Mb vs 1K).  When we open this file, it has about 1K of useful data followed by 999K of NULL characters.  Anyone have a clue as to why this might happen?  Why does it work one day, then fail, then work the next time we try it?

Update:  It has failed almost every day since this original post.  The fix is always reinitialize and reapply (which is just what the two jobs do every night).  It looks like BCP is just creating larger files than it needs.  Any ideas?


texts:
I'm Gergo Nagy, and I'm learning databases. My project is a bus schedule program, in android, but I stucked with the database part.
My program will include 2 main parts:
-If I click on a number of a bus, it shows the whole schedule, and a number, that shows when will the next bus start from the departure point.
-if I click on the address of the bus stop, it shows which buses crosses this stop. But it only shows that buses, which arrives in an hour.

My problem is the many-to-many database, because one bus has a lot of stops, but a stop "has" a lot of buses.
I'll attach a picture, from the schedule, I hope with this it will be easier to understand my question, 'cause my english is really bad, and sorry for that.

I know it's a mess, but with this level of english knowledge, it's my best.
I hope you can help me.
Thank you in advance


texts:
Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server?

We'd like to change our logging location, and in principle I'd think this could be done very simply by changing the /O "\\LOCATION\file.log" strings.

I can see how you'd do that in fact

USE MSDB
GO
update dbo.sysjobsteps
set [command] = replace([command], '\\OLDLOCATION\... ', '\\NEWLOCATION\... ')
WHERE...

BUT I've had it drummed into me that you should never update the sys tables manually, so the idea of doing this makes me beyond nervous. For anything else I'd use the dbo.sp_update_job proc (http://msdn.microsoft.com/en-gb/library/ms188745.aspx for those who've strayed here looking for something else job related), but that procedure doesn't seem to be able to handle this.

I have a hunch that this might possibly be an exception to the "Never Update the Sys Tables" rule though? I can't imagine that the cmdexec string is implied in any further relations in the way, for example, enabled/disabled status evidently is. Wishful thinking?

So I don't know for sure, and I can't seem to find out. Anyone know or have any experience with this?

Thanks
Dan


texts: Reset MySQL table data after each session is completed in php

texts:
I am creating a PHP application demo. The users who login can insert, modify and delete data but when they logout the data must be reset to the default data I set before.

The data must reset depending on the session so that multiple users can access the default data at the same time.

Is this possible using MySQL?

UPDATE 1:
I have created a stored procedure to create a new db and dump the default data, but I am getting an error while using the USE db_name in a procedure.

NOTE: I am using phpMyAdmin.

Error: #1314 USE is not allowed in stored procudures

Here is the stored procedure i have created with sample data.

BEGIN

CREATE DATABASE IF NOT EXISTS `dp_demo2` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `dp_demo2`;

DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `cid` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `company` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `skype_id` varchar(40) NOT NULL,
  `address` varchar(200) NOT NULL,
  `city` varchar(50) NOT NULL,
  `country` varchar(50) NOT NULL,
  `zip_code` int(10) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`cid`, `first_name`, `last_name`, `company`, `email`, `phone`, `skype_id`, `address`, `city`, `country`, `zip_code`, `created`) VALUES
(1, 'Julie', 'Penning', 'Apple', 'juli@example.com', '0402011', '@julie', '20-120, Street', 'Calfornia', 'USA', 40192, '2016-07-16 04:05:22'),
(2, 'Karl', 'Max', 'Ham. co.', 'karl@example.com', '9828101', '@max.karl', '20-820, Street', 'New York', 'USA', 82918, '2016-07-16 04:06:57'),
(3, 'Jhon', 'Doe', 'Microsoft', 'jhon@example.com', '892123', '@jhone.doe', '9-21-23, ham street', 'Troy', 'USA', 83082, '2016-07-16 04:08:48');

END

UPDATE 2:

I am able to create new db and use them, but i got another problem, how can I get dynamic db_name into the stored procedure.

Parameters for the stored procedure is not working for names, but only for the data.


texts: howto choose the right character set in MariaDB

texts:
if there are multiple character sets that can properly encode my data, how to choose one?

Is there any difference in disk or memory usage?
When joining tables do they need the same char set?

Other toughs?


texts: Get major and minor version of SQL Server

texts:
Running following query:

SELECT
    SERVERPROPERTY('productversion') AS version,
    SERVERPROPERTY('ProductMajorVersion') AS major,
    SERVERPROPERTY('ProductMinorVersion') AS minor;

produces the following results:

version      major   minor
------------ ------- -------
10.0.2531.0  NULL    NULL

What am I missing?


texts: SQL hourly data aggregation in postgresql

texts:
I am a newbie with database so I'm seeking your help with this one.

I have a table containing time series data.

2012/01/01 00:10, 10
2012/01/01 00:30, 5
2012/01/01 01:00, 10
2012/01/01 01:40, 10
2012/01/01 02:00, 20

The table is storing interval based data by keeping only the upper limit of the interval. For example the first row represents an interval from [00:00 - 00:10] with a value of 10, second row represents an interval from (00:10 - 00:30] with a value of 5 and the third one represents an interval from (00:30 - 01:00) with a value of 10.

I need an efficient query in Postgres to aggregate hourly data for a structure like the one described above. So the result would be something like this:

2012/01/01 00:00, 2012/01/01 01:00, 25
2012/01/01 01:00, 2012/01/01 02:00, 30

Note that the time-series data is big so any help with indexing this would be much appreciated.

Thanks,
dan


texts: Most efficient way to insert rows into a temp table in a stored procedure

texts:
I am working in SQL Server 2008.  I have a stored procedure with 5 queries inside of it that select rows from very large fact tables (on the order of millions of rows) into another table (call it table_B).  (Actually, the 5 queries used to be lots of queries, but I compressed them down to 5 because each query was doing a LEFT JOIN from the very large fact tables against very small dimension tables, i.e., each very large table was being scanned lots of times.)  I need this other table (table_B) to persist, i.e., every time the stored procedure is executed, rows just keep getting added to it.  table_B can become very large itself.

Currently, for each of the 5 queries, I insert rows into it via the INSERT INTO table_B SELECT ... method.  A colleague recommended to me to de-couple my inserts and selects to gain some performance.  He recommended to first insert the rows into separate temp tables, export the temp tables as flat files, and then insert the flat files into my table_B via SSIS.  That way, we can get the performance of SSIS for inserts.

What is the best way for inserting rows into these temp tables?  (I can either pre-define them or create them on-the-fly.  So, I'm open to using either INSERT INTO or SELECT INTO methods.)  The number of rows returned in each SELECT query will be large, in general.  So, I need it to be as fast as possible.

The tables are very simple, i.e., no indexes, constraints, or primary keys.  (Those may get implemented in the future, but I am not designing for it.)

Also, is there a way to automate the end-to-end process?  I don't want to have users first execute the stored procedure, query the temp tables, export them, and then run SSIS to import them into the final table_B.


texts: Optimizing postgresql maintenance statements on psql

texts:
In a reddit-like app (called Links) I maintain, users post hyperlinks and others can then publicly reply under the said links. The former is saved in a postgresql 9.3.10 table called links_link, the latter in a table called links_publicreply.

My task is to:

Delete all link objects created in a 30 day time window.
Delete all publicreply objects associated with objects in (1), and created within a 45 day time window.

Here's how I just did that for one time-window from 2016:

begin;
  delete from links_publicreply where submitted_on >= timestamp'2016-07-23 01:01:00' and submitted_on < timestamp'2016-09-07 01:00:00' and answer_to_id in (select id from links_link where submitted_on >=timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00');
  delete from links_link where submitted_on >= timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00';
commit;

Notice I'm querying links_link exactly the same way twice. I.e. once each in both delete statements. Now in Django ORM (of which I'm a native), I would have optimized this via first getting all the required links_link object ids separately, and then using them in all proceeding statements. How do I optimize that in the psql command line?

The most pertinent advice I've seen is in this SO answer, but it seems the with clause has to be inside the SQL statement it's being called in.

In other words, the following wouldn't work would it?

BEGIN;
  DELETE FROM links_publicreply
    WHERE submitted_on >= timestamp '2016-07-23 01:01:00'
        AND submitted_on < timestamp '2016-09-07 01:00:00'
        AND answer_to_id in (
            SELECT id
            FROM links_link
            WHERE submitted_on >= timestamp '2016-07-23 01:00:00'
                AND submitted_on < timestamp '2016-08-23 01:00:00'
    );
    DELETE FROM links_link
    WHERE submitted_on >= timestamp'2016-07-23 01:00:00'
        AND submitted_on < timestamp'2016-08-23 01:00:00';
COMMIT;


texts: When a user exports to a flat file she gets an access denied error.  The wizard allows her to preview the data so she has access on the db side.  From Windows she can create a new file in the target destination so she seems to have network permissions.  She is logged into her computer with a network username and into SS2012 with a SS login.  Everyone else can run the export wizard to this location successfully.  I am stumped!


texts:
I'm looking into the possibilities for writing queries in MS Access, as my client currently uses Access and would be reluctant to move away to SQL Server. I've been using the data macros (which are Access 2010 onward's implementation of triggers) in order to create an audit table of sorts. Which looks something like this:

  Modification_Log
---------------------------------------------------------------------------------------------------------------------------
 ModificationID | TableName | FieldName | RecordID | InitialValue | ModifiedValue | ModifiedDate        | ModifiedEmployee |
       1        | Employee  |  Surname  |    3     |   Bloggs     |   Blaggs      | 16/09/2014 16:26:39 |    andrew        |
       2        | Employee  |  Surname  |    3     |   Blaggs     |   Bloggs      | 16/09/2014 16:27:32 |    stu           |
       28       | Employee  | Firstname |    3     |    Joe       |     Jon       | 17/09/2014 09:15:10 |    andrew        |

Which is updated dynamically when changes are made to a record on my Employee table, which looks something like this:

      Employee
----------------------------------------------------------------
 EmployeeID | PositionID | Title | Firstname | Surname | Email |
       3    |     2      |  Mr   |    Jon    |  Bloggs | none  |
       4    |     1      |  Mr   |   Tyrion  |Lannister| none  |

What I'd like to be able to do is to write a sub query which would use Max() to pull up the most recent ModificationID (an AutoNumber) where the RecordID is 3, and have the main query joined to this listing the employee details along with the most recent "ModifiedEmployee" and "ModifiedDate", giving me a recordset something like:

 EmployeeID | PositionID | Title | Firstname | Surname | Email | ModifiedDate        | ModifiedEmployee |
       3    |     2      |  Mr   |    Jon    |  Bloggs | none  | 17/09/2014 09:15:10 |    andrew        |

I don't think that's too much to ask, but seeing as MS Access doesn't allow sub queries at the moment the way I see it, I'll have to do something programmatically to carry out multiple queries in order to extract the same data, something like:

SELECT Employee.*
FROM Employee
WHERE Employee.EmployeeID = <<Selected Employee>>

Then

SELECT Max(Modification_Log.ModificationID) As NewestModificationID
FROM Employee INNER JOIN Modification_Log ON Employee.EmployeeID = Modification_Log.RecordID
WHERE Employee.EmployeeID = <<Selected Employee>>

Then

SELECT Modification_Log.ModifiedDate, Modification_Log.ModifiedEmployee
FROM Modification_Log
WHERE Modification_Log.ModificationID = <<NewestModificationID>>

Of course; that wouldn't be too difficult to implement but I'm just wondering whether there is a more elegant solution? I'm sure there has got to be as it seems like I'm doing an awful lot of leg work just for this relatively small task.

EDIT: Max Vernon suggested that I should be able to use sub queries via the SQL window (which is what I'd been trying to do anyway) but I'm still struggling to get it working, I've attempted to replicate what I saw a user over on Stack Overflow doing:

SELECT Employee.EmployeeID, Employee.PositionID, Employee.Title, Employee.Firstname, Employee.Surname, Employee.Email,  Modification_Log.ModifiedDate, Modification_Log.ModifiedEmployee
FROM Employee INNER JOIN Modification_Log ON Employee.EmployeeID = Modification_Log.RecordID  INNER JOIN
               (SELECT Modification_Log.RecordID, Max(Modification_Log.ModificationID) As NewestModificationID
               FROM Modification_Log
               GROUP BY Modification_Log.RecordID) As LogMax
ON Employee.EmployeeID = LogMax.RecordID AND Modification_Log.ModificationID = LogMax.NewestModificationID
WHERE Employee.EmployeeID = 3 AND Modification_Log.TableName = 'Employee'


texts: How to know what database user I'm using in SQL Server?

texts:
I'm learning SQL with SQL Server and I've see how to create users, I've created without login:

CREATE USER Luis_Gerente WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Mario_Supervisor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Ana_Vendedor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Martin_Vendedor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Carlos_Vendedor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo

But I don't know what user I'm currently using, and with what user I've created those other users.

How can I know that, and how can I change users depending on the person who is using the database server?

I do authentication at server level but at the database level I currently do not see any effect of having users at database level. When I use the database where I created the users I don't have changes or effects.

When I start to use the database I'm not getting any prompt or form needing a specific database user to use the database with the specific privileges of that user.


texts:
I have a database running under PostgreSQL which normally has to have a size of 300 GB. Unfortunately it is now 3,5 TB in size.

I noticed that the directory of this database , the pgdata path is /base/45075466. There are about 3000 files in that directory each 1 GB in size.

I launched a query to check on all the objects of the database, but I didn't detect any large objects:

 SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

I checked the size by schema, but didn't find any size irregularities:

    SELECT schemaname,
     sum(pg_total_relation_size(schemaname||'.'||tablename)) as size
     from pg_tables   group by schemaname
     order by size desc;

With the OID of these files 21037129 and using oi2dname I did not find the name of the table:

-rw-------  1 999 docker  1,0G févr. 11 02:30 121037129.1045
-rw-------  1 999 docker  1,0G févr. 11 02:34 121037129.1046

I think it's orphaned files, because if I use another OID, the name of the table is detected.

I have only 350 GB space on my computer's drive and I think it's a risk to launch a VACUUM FULL (risk of having free space reduced to zero).

Before I launch the VACUUM FULL, is there any other method to resolve this problem? If I delete the database in question, is it possible that my orphaned files will be eliminated and what is the delay? Is there any other method to avoid having to launch VACUUM FULL?


texts: How to reduce size of PostgreSQL table after dropping columns?

texts:
I have a PostgreSQL database. One table is very large. I want to extract a TEXT column into a separate table and see how much I can reduce the size. The problem is that the size appears to stay the same no matter what I do.

I'm obtaining the size by issuing \dt+ in psql.

I've tried VACUUM FULL and dp_dumpall followed by deleting the database and restoring it.

The size of the table did not change.

I added a second TEXT column, watched the size increase by a few hundred MB, deleted the column, and I cannot get the size to go down again.

How can I get the size of the table to go down after deleting these columns?


texts: Why can't I use variables in T-SQL like I imagine I can?

texts:
Forgive me, I am a developer who has moved over to the world of SQL. I thought I could improve some SQL by adding variables but it did not function like I expected. Can someone tell me why this does not work? I don't want a work around, I want to know the reasons why this doesn't work like I imagine it should as I am sure there is a good reason, but currently it doesn't jump out at me.

DECLARE @DatabaseName varchar(150)
SET @DatabaseName = 'MyAmazingDatabaseName'

CREATE DATABASE @DatabaseName
GO

USE @DatabaseName
GO


texts: Postgres - want a database to have two names

texts:
One of my Postgres databases is named entirely in upper-case, and a new bit of software needs to see it in lower-case. I've tried renaming it, but unfortunately that breaks the huge multitude of things that already rely on it being in upper-case (as they've got quotes in the connection string so need it in upper case). Going through and fixing those would be a risky, mammoth undertaking as there is nobody in the company left who originally created the database and all the things that rely on it, and documentation is scarce.

As such, I've come to the conclusion that the best way forwards is to leave the existing database as it is, but to try and have it available by two names - its original name, and a new name. I would imagine just about everyone here already knows of something like this anyway, but I'm relatively new. I've seen terms like alias and synonym in my Googling.

However, I don't believe either of these can be done to an entire database (if I'm wrong then I would be very happy with some information to go on). I've also tried creating an empty database (with the nice new lower-case name) and creating simple views to the original's tables, but of course views don't generally go cross-database.

Anyone got any ideas?

EDIT - also quickly tried dblink, which looks like it could do the job. Its an unknown function, so I'm guessing the version of postgres we're running is old. May see if we can upgrade it.


texts: Why can't you add an Identity to an existing colum in SQL Server?

texts:
What is so special about an Identity column?
I am pretty sure there is a valid reason, but I'm trying to get a better understanding of SQL.
So what is so special about an Identity column that this can't be easily added in retrospect?
Why is this not just a single int/bigint flag somewhere that is being incremented every time you insert a new row?
Is there anything physically stored per row that this is a difficult process?


texts: Strategy to scale a comparison table query and load time

texts:
given a product table

PRODUCT
id   some_column
1    10
2     2
3     8

when comparing all them I get a triangular matrix that I load from a csv file

COMPARISION
id  product_left  product_right   result
1              1              2        3
2              1              3        8
3              2              3        2

where I want to perform a query that returns all the best n comparisons for a given product

SELECT * FROM COMPARISION WHERE ( product_left = 1 OR product_right = 1 ) ORDER BY result DESC LIMIT n

I've made an index for product_left and another for product_right column

But I'm not convinced that it will scale for tens of thousands products

ie:
20k products will produce ~200 million comparisons
the load takes 15 hours in regular desktop computer
and it will get slower to load with more indexes

so, which should be the best strategy to scale it?

partitioning
do a different query
index the result column to help order by clause
...

you can find the solution here
https://github.com/rubentrancoso/cosinsimilarity


texts: Huge logs are generated in mongo db shard logs

texts:
We are using Mongo db Shard set up. What i see that on primary of each shard log file keep growing to GBs. Even in a day it reaches to 10GB for single day.

This appears to be slowing down Mongo over time.

I would like to have Mongo automatically rotate the log once it reaches, say, 100MB.  I've looked at the Mongo documentation, the standard Mongo package does not appear to support automatic log rotation based on size.

I also need to know why log file on primary grows so much.

Please help us with this.

Thanks


texts: How to pass a set or unnamed relation into a PostgreSQL function?

texts:
In PostgreSQL, there are Set-Returning-Functions (SRF). But I'm wondering if there is also a way to pass an unknown relation into a function? In other words, is it possible to write Set-Taking-Functions (so to speak), other than passing the name of the tuple-set?

Obviously, there is dynamic SQL where one can pass a table name. With pgRouting, one can even use an entire string to pass the set of tuples into the function:

SELECT * FROM shortest_path('
   SELECT gid AS id,
          start_id::int4 AS source,
          end_id::int4 AS target,
          shape_leng::float8 AS cost
   FROM network',
1,
5110,
false,
false);

But the SQL string in the parameter does not seem very secure. The other possibility is to store the result into a temporary table first and pass the name of the temporary table, which seems to be against the idea of set-based programming in SQL.

Are there any alternative ways of passing a set into a function?


texts: Merge multiple "is exists" queries into one statement

texts:
I'm trying to figure out how to check if multiple rows exist in multiple tables in one statement.

Given the following tables:

table table_one
========
 id
 'a'
 'b'
 'c'

table table_two
========
  id
 'aa'
 'bb'
 'cc'

This works,

SELECT 1 from table_one where id = 'a'

and

SELECT 1 from table_two where id = 'aa'

works

How can I combine them into one statement?

I tried

 SELECT 1 FROM table_one WHERE id = 'a'
 UNION
 SELECT 1 FROM  table_two WHERE id = 'aa'

but this just merges the results into one column so I cant tell if a particular table was missing a row.

What i'd like is a result row something this

  table_one  table_two  table_three    table_four
  =========  =========  ===========    ==========
  1          1          [null]         1


texts:
This is my query:

select locid,tlid,locdescription from (

  select locid, tlid, locdescription
  from
  trip_log left join trip_log_det on tlid=tldtripid
  left join locations on tldlocation=locid
  where tlid=50 order by tldid

) as foo group by locid,tlid,locdescription

The point of using a subquery is to get data back ordered as per the internal query, so sorted by tldid. I do not need tldid in the data as it is a serial column and it would nullify the group by statement.

Is there a way to tell the subquery to maintain the ordering of the internal query?


texts: Can MongoDB be configured to sit behind a load balancer?

texts:
According to this post:

  In a single replica set, you cannot distribute writes, they all must
  go to the primary. You can distribute reads to the secondaries
  already, via Read Preferences as you deem appropriate. The driver
  keeps track of what is a primary and what is a secondary and routes
  queries appropriately.

According to the Mongo docs:

  You may also deploy a group of mongos instances and use a proxy/load
  balancer between the application and the mongos. In these deployments,
  you must configure the load balancer for client affinity so that every
  connection from a single client reaches the same mongos.

So basically, it seems like if you've got a single replica set of 3 nodes, you can't really use a proxy/load balancer since all writes need to go to the primary and you need client affinity... so all reads also need to go to the primary.

What I'm thinking though is that it might be possible to have applications connect to a load balancer. The load balancer would route all requests to the primary (not very balanced, but whatever)... until/unless the primary went down - at which point the load balancer would start routing requests to a "new primary".

I'm not sure if this is possible however since, how would the load balancer know which mongo server had been elected the new primary (and thus where it should route new requests)?

Assuming it was possible, this would achieve a degree of redundancy, in case the primary ever goes down... I'm also hoping it would also have the side effect of avoiding stale writes when a network partition occurs, since the load balancer (and thus all DB clients) would only ever connect to a single primary.

Or is this a stupid question...


texts: sqlserver 2008r2 service pack incompletely applied on shared features

texts:
I have installed sqlserver 2008R2 service pack 3 on my instance.

The installation went through successfully but the instance still shows version number of SP1 in management studio.

When I start the patch setup again, I see that the main database features are patched correctly but that the shared features are "incompletely applied"

Can someone explain me what to do with this ?

Would a reboot help ?


texts:
While there are other questions out there in forums, I notice that those are several years old, and I hope that things have changed by now.

I am using SQL Server Management Studio 2014 and I have to place all my job steps into a single job because they must run in a specific sequence. My job has over 200 steps so far and I'd like to be able to break them up into several distinct jobs, where one job can start after the preceding job finishes.

The one "answer" from the questions posted a few years back is a third party tool that is simply a non-starter because of the cost.

Has the situation changed, or is it still impossible to run SQL Agent jobs in sequence within SQL Server Management Studio and without the use of expensive third party tools?


texts:
I have, from what I can tell, a unique question. I would like to know if there's a way to construct a query in which the existence of the actual where clause itself is conditional.

I'm writing a stored procedure where I would like to pass in a filter (e.g. Col1=1 or Col2=3) and my query looks something like the following:

set @filter = 'Col1=1 or Col2=3' --assume these variables have been declared already
select * from @tbl where @filter --I need to use a table variable

Note: I tried using sp_executesql and sp_sqlexec but neither take a table variable. I would also like to avoid using an if-else statement. Please do not suggest either of these as your answer. :)


texts: More efficient SQL for different databases with same schema

texts:
I have data stored in different monthly databases that have the exact same name schema for the tables and columns. Each monthly database has ~10 million rows. I'm looking for a more efficient way to select the rows I want from each database based on the same variable. There are only 5 to 10 rows from each month for that variable.

I also need to JOIN this different monthly data to the same separate database. I am currently using UNION to combine the data. So:

    SELECT a.col1, a.col2, a.col3, b.colX, b.colY, b.colZ
    FROM   db_JAN.tab1 a
    INNER JOIN db_OTHER.tabX b
    ON (a.DATE = b.DATE AND a.someID = b.someID)
    WHERE a.col1='variable1'

    UNION

    SELECT a.col1, a.col2, a.col3, b.colX, b.colY, b.colZ
    FROM   db_FEB.tab1 a
    INNER JOIN db_OTHER.tabX b
    ON (a.DATE = b.DATE AND a.someID = b.someID)
    WHERE a.col1='variable1'

    UNION

    SELECT a.col1, a.col2, a.col3, b.colX, b.colY, b.colZ
    FROM   db_MARCH.tab1 a
    INNER JOIN db_OTHER.tabX b
    ON (a.DATE = b.DATE AND a.someID = b.someID)
    WHERE a.col1='variable1'

    .
    .
    .

If the SELECT, JOIN and WHERE clauses are the same across different databases with the same naming schema, is there a more efficient way of writing this query? Something like:

    SELECT a.col1, a.col2, a.col3, b.colX, b.colY, b.colZ
    FROM   (db_JAN,db_FEB,db_MARCH...).tab1 a
    INNER JOIN db_OTHER.tabX b
    ON (a.DATE = b.DATE AND a.someID = b.someID)
    WHERE a.col1='variable1'

This question might be applicable but I'm not sure as I'm relatively new to SQL and am not sure about sequential table scans or indexing:

"How to make a union view execute more efficiently?"

I don't believe it is a hierarchical query and this question doesn't help me either.

NOTE: I am using SAS to run the query connecting to Teradata SQL database and have used a MACRO VARIABLE for 'variable1' in my PROC SQL statement. I am not sure if I can use a MACRO VARIABLE for the databases but that could be an option if I can't do it in SQL itself.


texts: MySQL ignores ZWNJ character in string comparison

texts: If you add a unique index to a char columns you can not add two string that differ only in having ZWNJ characters (e.g. 'فا' and 'ف‌ا'). However I need to store both of them. How can I overcome to this constrain? Is it true for other RDBMS as well?


texts: Cannot get LOB data from SQL Server transaction logs

texts:
I am trying to extract all information (Insert, Update and Delete Operations) from the transaction log file of SQL Server manually. For this work, I am using the undocumented functions fn_dblog and fn_dump_dblog for SQL Server.

There are 3 allocation units (sys.allocation_units) in transaction log file Partition (sys.partitions).

IN_ROW_DATA
ROW_OVERFLOW_DATA
LOB_DATA

In all of these allocation units, we found a RowLogContents0 column that contains the data of that row in hexadecimal format. I can get real data from the RowLogContents0 column, when I have an IN_ROW_DATA allocation unit type in my table. But when I have ROW_OVERFLOW_DATA or LOB_DATA allocation unit type in my table then I am unable to get data from RowLogContents0 column.

Does anyone know how I can get data using RowLogContents0 column which is stored in ROW_OVERFLOW_DATA and LOB_DATA allocation unit type?

I am following these links to get inserted and updated data from logs:

This one for inserted record
This one for modified record

Now I am able to get these records and queries. But facing problem when trying to get LOB records as describe above.

Note : I don't want to use any third party tool to do this, because I am trying to make a third party tool for my program. Right now we are only focusing on SQL Server 2008 R2.


texts: Is there anyway to make sql server search the master database for SQLCLR stored procedures before the current one?

texts:
For T-SQL stored procedures with the prefix sp_, SQL Server will search for and user a procedure in the master database before the current one. This does not appear to be the case with a SQLCLR stored procedure I created  with SQL Server 2010 using Visual Studios built in deploy. The database server is SQL Server 2008 R2 (SP1).

I execute this script:

SELECT name
from master.sys.procedures
WHERE type_desc='CLR_STORED_PROCEDURE' and schema_id = 1

USE tempdb
PRINT 'USING tempDb'
EXEC sp_RAISERROR_CaughtDemo;
GO
USE master
PRINT 'USING master'
EXEC sp_RAISERROR_CaughtDemo
GO
--SELECT * from sys.assembly_modules

And the stored procedure will only execute with an unqualified name from the master database:

name
----------------------------------
sp_RAISERROR_CaughtDemo
sp_RAISERROR_UncaughtDemo

(2 row(s) affected)

USING tempDb
Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 'sp_RAISERROR_CaughtDemo'.
USING master
RAISERROR() Caught Severity 0
RAISERROR() Caught Severity 1
Msg 50000, Level 1, State 1
RAISERROR() Caught Severity 2
Msg 50000, Level 2, State 1
RAISERROR() Caught Severity 3
Msg 50000, Level 3, State 1
RAISERROR() Caught Severity 4
Msg 50000, Level 4, State 1
RAISERROR() Caught Severity 5
Msg 50000, Level 5, State 1
RAISERROR() Caught Severity 6
Msg 50000, Level 6, State 1
RAISERROR() Caught Severity 7
Msg 50000, Level 7, State 1
RAISERROR() Caught Severity 8
Msg 50000, Level 8, State 1
RAISERROR() Caught Severity 9
Msg 50000, Level 9, State 1
RAISERROR() Caught Severity 10
Msg 50000, Level 11, State 1, Line 1
RAISERROR() Caught Severity 11

The code for those procedures is as follows:

[SqlProcedure(Name = "sp_RAISERROR_UncaughtDemo")]
public static void RaiserrorUncaught()
{
    short i = 0;
    using (var cn = new SqlConnection("context connection=true"))
    using (var cmd = cn.CreateCommand())
    {
        cn.Open();
        cmd.CommandText = "RAISERROR('RAISERROR() Uncaught Severity %d', @i, 1, @i)";
        cmd.Parameters.Add("@i", SqlDbType.SmallInt);
        while (true)
        {
            cmd.Parameters["@i"].Value = i++;
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
        cn.Close();
    }
}

[SqlProcedure(Name = "sp_RAISERROR_CaughtDemo")]
public static void RaiserrorCaught()
{
    try
    {
        short i = 0;
        using (var cn = new SqlConnection("context connection=true"))
        using (var cmd = cn.CreateCommand())
        {
            cn.Open();
            cmd.CommandText = "RAISERROR('RAISERROR() Caught Severity %d', @i, 1, @i)";
            cmd.Parameters.Add("@i", SqlDbType.SmallInt);
            while (true)
            {
                cmd.Parameters["@i"].Value = i++;
                SqlContext.Pipe.ExecuteAndSend(cmd);
            }
            cn.Close();
        }
    }
    catch(SqlException) {}
}

Is there anything I can do to make SQL Server search in the master database to find this stored procedure? I tried sys.sp_MS_marksystemobject to no avail.


texts: 'kill ' gives ERROR 1095 (HY000): You are not owner of thread ... even though I am

texts: I did an ALTER TABLE on a huge table on a MariaDB Galera Cluster 10.2, but after a while of waiting I changed my mind and tried to kill the connection. However, MariaDB said ERROR 1095 (HY000): You are not owner of thread 123 even though I most definitely am the owner. So what is going on?


texts:
Suppose that I have two tables:

tableA (id, name);
tableB (id, name);

I also have a textual file with a list of couples of ids:

id1a, id2a
id1b, id2b
...

I would like to run the following query:

select *
from (
id1a, id2a
id1b, id2b
...)
join tableA as a on a.id = id1
join tableB as b on b.id = id2;

How can I write such a query?


texts: Counting repeated occurences for a group of fields

texts:
I am trying to count the continuous repeated value for a field in order to obtain the maximum repeated occurrences for each person in the table. eg:

Time         person        Result
08:12:36     Dave          Heads
08:12:37     Dave          Heads
08:12:39     Sue           Tails
08:12 41     Dave          Tails
08:12:42     Dave          Heads
08:12:44     Dave          Heads
08:12:45     Sue           Heads
08:12:46     Sue           Heads
08:12:47     Sue           Tails
08:12:48     Dave          Heads
08:12:49     Dave          Heads
08:12:50     Dave          Tails
08:12:51     John          Heads

Result should look like this

Dave     4
Sue      2
John     1

The count for Dave would be 4 as between 08:12:42 and 08:12:49, 4 Heads options occur for Dave before Dave gets a Tails option.


texts: MSSQL Audits are growing out of control. 1gb every 4 minutes - any way to get size down?

texts:
I am trying to setup audits for a Veeam backup server.

I used the audit specifications from STIG - https://www.stigviewer.com/stig/ms_sql_server_2016_instance/

I haven't setup any database auditing at this point, just the server from above link.

The audit is writing 1gb every 4 minutes. Over 100gb every day!

Is there any way to get the size down? Maybe removing Select statements from audits? If so, how can I do that. Please point to a tutorial or something - I've searched for days ...

TIA


texts: How does a VoltDB cluster determine time sync between nodes

texts:
I have a weird painful issue with VoltDB clusters on multiple RHEL7.1 vm (KVM).

Time is synced between the VM and their hypervisor with a single local master NTP server - using chrony. Master server sync with external ntp pool.

There is a random error generated by VoltDB:

  FATAL [main] HOST: Clock skew is 120 which is > than the 100 millisecond limit. Make sure NTP is running.

Chrony config has been setup as much as possible as per the perf guide from VoltDB for ntpd - here.

I have searched high and low with my "google-fu" - but I can't seem to find an answer on how VoltDB determines that clock skew error.

The only closest one I can find is in this java code: voltdb/SocketJoiner.java on github from line 603 onwards.

skew = System.currentTimeMillis() - currentTimeBuf.getLong();

I wonder why VoltDB doesn't use a java function call - System.nanoTime() instead, for a precision timing.

Any help or insights will be great.


texts: How can I decide Rebuilding Index with DETAILED or SAMPLED on SQL Server

texts:
I want to rebuild my indexes and statistics on DB.

How can I select this option on production maintenance.

SQL Server Version : Microsoft SQL Server 2012 - 11.0.2100.60 (X64)  (Build 7601: Service Pack 1)


texts:
The docs page states (emphasis mine)

  Exit Status

  psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g. out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

However, I seem to be unable to induce exit code 3.

bash-4.2$ uname -a
Linux mvpg-centos-76.vagrantup.com 3.10.0-957.21.3.el7.x86_64 #1 SMP Tue Jun 18 16:35:19 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
bash-4.2$ psql --version
psql (PostgreSQL) 10.12
bash-4.2$ psql -v ON_ERROR_STOP=1 -c 'select 1/0;'
ERROR:  division by zero
bash-4.2$ echo $?
1
bash-4.2$

I've also reproduced this behaviour on FreeBSD 11.3 and Darwin 19.3. Am I misunderstanding the documentation or using a faulty syntax?


texts: Breaking Semisynchronous Replication in MySQL 5.5

texts:
I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7.

My application is running and updating the database of the master server and same is being updated in the slave database server.

But due to some unknown reasons sometimes, Replication breaks.

On running the command:

SHOW STATUS LIKE 'Rpl_semi_sync%';

It gives this status:

'Rpl_semi_sync_master_no_times', '0'
'Rpl_semi_sync_master_no_tx', '0'
'Rpl_semi_sync_master_status', 'ON'     <<-------------
'Rpl_semi_sync_master_timefunc_failures', '0'
'Rpl_semi_sync_master_tx_avg_wait_time', '338846'
'Rpl_semi_sync_master_tx_wait_time', '29479685'
'Rpl_semi_sync_master_tx_waits', '87'
'Rpl_semi_sync_master_wait_pos_backtraverse', '0'
'Rpl_semi_sync_master_wait_sessions', '0'
'Rpl_semi_sync_master_yes_tx', '3106'

Ideally, in semi synchronization, when the sync breaks the status should come as OFF since master is not able to receive any acknowledgement from the slave.
Please help us in this regard.


texts: how to deal with foreign keys with fixed values (on a db-table-basis)

texts:
I have a control table that looks like this:

CREATE TABLE `tbl_control` (
  `tm_db_tbl` varchar(64) NOT NULL,
  `tm_key` int(11) unsigned NOT NULL,
  /* a bunch of other fields here */
  PRIMARY KEY (`tm_db_tbl`,`tm_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

From the perspective of my other tables, when I want to join on this control table, I join on the tm_key in my table along with a string literal designating my database and table against the tm_db_tbl field above.  With the client table below, I would join WHERE ct.tm_db_tbl='my_db.some_client_table' AND ct.tm_key=sct.tm_key.

CREATE TABLE `some_client_table` (
  `tm_key` int(11) NOT NULL,
  /* a bunch of other fields here */
  PRIMARY KEY (`tm_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is a tiny bit of optimization so I don't have a tm_db_tbl column in my client table with all the same values.  The relationship here is many-client-tables-to-one-control-table.

But now, I run into a bit of a problem when I want to define a foreign key on my client table because the corresponding tm_db_tbl is a string-literal.

Should I just bite-the-bullet and add tm_db_tbl filled all with the same value to my table or is there a better way around this problem?

If it makes any difference, I am using mysql.


texts: How to access user information via sys.fn_dump_dblog

texts:
I am reviewing sys.fn_dump_dblog and I can see the old states of the records, but I can not see which users are doing the operations.
SPID, Transaction SID columns are always null, how to fix it, or any options?

I am using SQL Server 2016.

My query:

SELECT
    *
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\LogBackup\blabla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (
[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (
([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))
)
AND [PartitionID] IN (72057595034533888)
)
OR ([operation] = 'LOP_HOBT_DDL')


texts: Database Connection vs Raw TCP Connection

texts:
I have some fundamental questions about how database clients & database interact

Do databases support multiple transactions simultaneously on a single database connection from client? If not, why not? (as multiplexing would save on resource overhead per connection & connection pools are a source of contention when thousands of simultaneous queries needs to be executed simultaneously, which multiplexing for sure avoids)
Whats the relationship between database client's level Connection vs physical raw TCP connection. Is it many-to-one[multuplexing] (or) one-to-one? If not multiplexed why not?
If multiplexed, does the database server maintain a single logical connection from its end (or) multiple logical connections

PS: I understand some of these details will vary from database to database, buit want to know in general how popular implementations such as Postgres, Mysql, Oracle, SQL server & DB2 implement these


texts: Is it possible to group by a fraction of the result set?

texts:
I have a database of products with prices. What I'd like to be able to do is figure out 5 different price ranges such that each range, when ordered by price, will have approximately the same number of rows in it, i.e., if the database has 25 products in it, I'd like to be able to to find out the MAX price of the 5 lowest priced products, the max price of the second group of 5 lowest priced products, etc.

Something along the lines of:

SELECT MAX(price) FROM products GROUP BY count(5)

I realize that using count(5) in the GROUP BY won't work, I'm just curious if something like this is even possible?


texts: How to call Oracle's stored procedure from DB2 stored procedure?

texts:
I am new to writing stored procedures and wondering if it's possible to call a Oralce' stored procedure from DB2 stored procedure.

I have a scenario where a trigger on a DB2 table will invoke a DB2 stored procedure. And further this stored procedure have to call to connect with Oracle DB to do some certain operation (a stored procedure).

Is my understanding is right, and can Oracle's stored procedure can be called through DB Link?

Edit: The version of DB2 is 9.7 and my platform is AIX 6.


texts: Get Insert/Delete/Update operations with data from the transaction log

texts:
Can anyone please provide a script using fn_dblog to find Insert/Delete/Update operations with data (like Change Data Capture) that are made to the SQL Server from transaction log.

I can't enable Change Data Capture since I am using SQL Server 2012 Standard Edition.

Change Tracking is not suitable since I need before and after values.


texts: Why query execute faster after first execution

texts:
I have mysql (5.7) innodb table, and run a select query and using an Id foreign key. for the first query on this Id foreign key condition, the query took a lot of time to execute, and If I run another condition on select with the same id foreign key, it run so fast.

Why is that so? and how could I optimize my queries select to run fastely in the first execution?


texts:
I am very new to DB2.have installed Express C version and facing some problem while running a simple query.

SELECT A,B,C,D,E,F,G, H  FROM TABLE_NAME
 where A=9999  ORDER BY  D

Note: A through H are the only columns in the table.
When I do this, I get this exception:

  DB2 SQL error: SQLCODE: -1585, SQLSTATE: 54048, SQLERRMC: null
   Message: A temporary table could not be created because there is no available system temporary table space that has a compatible page size.

More exceptions ...

  DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-1585;54048;

This is just a simple query with where and order by clause. Why am I getting this error?


texts: Optimizing Queries HAVING COUNT and INSERT...ON DUPLICATE KEY UPDATE

texts:
There are two main queries I'm tying to improve, one question is much more conceptual and learning about databases and another is much more straightforward.

Both cases deal with the same table
It has 2 relevant columns as far as the question is concerned, they make up the primary key of the table. Each of the elements of the primary key is hypothetically the key to another table n their own right. The first row is an int (this is an item) an the second row is a VARCHAR (this is a user) limited to 200 characters.

The first issue comes in trying to find an intersection of users. I am looking to find an intersection of users that share a number of items, 5 in the example

SELECT userid
FROM   (SELECT b.userid,
               b.showid
        FROM   ratings AS A
               JOIN ratings AS B
                 ON A.userid LIKE :user
                    AND B.userid NOT LIKE :user
                    AND A.showid LIKE B.showid) AS C
GROUP  BY userid
HAVING Count(*) > 4

On a fast run this takes up about 2/3 of the runtime of my program (appearing twice, one nested in another query taking little longer than this one). I've tried looking at it but I don't see any obvious way to restructure the query to save time, but queries operating over many more rows are coming up with runtimes of less than 1 second while this takes 2 seconds.

The second issue is more conceptual. As mentioned before I have a composite primary key. I've found elsewhere that with InnoDB tables this can make inserts take up a lot more time. This is exactly whats happening with an INSERT ... UPDATE ON DUPLICATE KEY query that I'm running. What is the best way to go about reducing this slowdown.

This is what the insert looks like, it only runs if the show has been rated

INSERT INTO shows
            (
                        showid,
                        showname,
                        showimage
            )
            VALUES
            (
                        :id,
                        :name,
                        :image
            )
on duplicate KEY
UPDATE showimage=:image,
       showname= :name;
INSERT INTO ratings
            (
                        userid,
                        showid,
                        rating
            )
            VALUES
            (
                        :user,
                        :show,
                        :rating
            )
on duplicate KEY
UPDATE rating=:rating

Shows

CREATE TABLE "shows" (
 "ShowId" int(11) NOT NULL,
 "ShowName" varchar(400) NOT NULL,
 "ShowImage" varchar(400) NOT NULL,
 "ShowAgreement" int(11) NOT NULL DEFAULT "0",
 PRIMARY KEY ("ShowId")
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Ratings

CREATE TABLE "ratings" (
 "UserId" varchar(200) NOT NULL,
 "ShowId" int(11) NOT NULL,
 "Rating" int(11) NOT NULL,
 PRIMARY KEY ("UserId","ShowId"),
 KEY "ShowId" ("ShowId"),
 CONSTRAINT "ratings_ibfk_1" FOREIGN KEY ("ShowId") REFERENCES "shows" ("ShowId") ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

For a single test case the INSERT takes an additional 8 seconds over the UPDATE for 160 entries.

Should I simply run the conditional part of the query codeside and removing the primary key.
Should I drop the primary key and replace it with a unique constraint?
Should I create a new table for the UserId that creates a VARCHAR -> INT  identity for userId's and give all existing tables the INT version of the id rather than the CHAR?
Should I do something else entirely that hadn't occurred to me?


texts:
We have this query:

SELECT "messages".*
FROM "messages"
WHERE "messages"."account_id" = 1
AND "messages"."status" = 'unread'
AND (created_at >= '2014-04-24 01:00:00.000000')
ORDER BY id DESC

It runs fine under 0.1ms. But when we add LIMIT:

SELECT "messages".*
FROM "messages"
WHERE "messages"."account_id" = 1
AND "messages"."status" = 'unread'
AND (created_at >= '2014-04-24 01:00:00.000000')
ORDER BY id DESC
LIMIT 5

It runs over 20,000ms. Some notes:

The messages table has over 300,000 rows, not that big.
We have indexes on all 3 columns in the WHERE condition.
The account_id and created_at conditions filter out 3,000 messages from the 300,000 rows. Out of the 3,000 messages in that account_id, there are only 3 unread messages.

So we isolated each WHERE condition and found out about a certain behaviour -- we tweaked the LIMIT to correspond with the amount of unread messages:

SELECT "messages".*
FROM "messages"
WHERE "messages"."account_id" = 1
AND "messages"."status" = 'unread'
AND (created_at >= '2014-04-24 01:00:00.000000')
ORDER BY id DESC
LIMIT 3

It runs under 0.100ms. Any explanation for the difference in performance?


texts: Selecting multiple columns from multiple rows in one column (subquery?)

texts:
I'm working with MySQL 5.1.68.

I have a situation where I'd like to select rows from another table into a column.

I'd like to output this:

id # name # citizen_name (multiple citizen names)

1 # The Hague # Barack Obama, Marc Zuckerberg, George Bush

I know how to do this with multiple queries, but I'd like to do this with just one query. I've been looking at group_concat and concat_ws, but unfortunatly this hasn't brough me the result I was looking for. I've also tried to put the results from a SELECT sub query into a variable and CONCAT those results, but that didin't work either.

My SQL fiddle to show the schema and some test data: http://sqlfiddle.com/#!2/c7c7e/1.

What can I do?


texts:
For a mobile application we are considering validating a user based on their mobile phone number.
In the app we will restrict the user input so that it will be stored in the following format:

+46700112233

That would mean in this case a Swedish phone number (+46), remove the initial 0 and then keep the remaining 9 digits (mobile phone numbers here are 10 digits including the leading 0).
However, the data currently saved in database include a lot of rows and no validation was made upon input. Meaning that the numbers saved could be in many different formats including

0700112233

070-0112233

460700112233
and so on.

So the type of the column in the database would probably be varchar.
At this time I'm unable to actually update the numbers already saved in the database to one general format, using for instance libphonenumber. I would prefer to limit the number of actual queries to the database as well.
The database currently includes phone numbers for about 10+ countries all saved in different formats. The DBMS used is MySQL.
What would be an efficient way to match the numbers accurately without querying the database for any of the possible formats?


texts: SQL Server: UPDATE permission for table variable

texts:
SQL Server 2012

I have a maintenance script that I wrote for some of my operators.  They are members of the fixed db_datareader and db_datawriter roles.  Part of the script creates a table variable, then populates the variable with some statistics generated during the script.

The problem occurs when the script attempts to UPDATE the table variable.  INSERT appears to work, but not UPDATE.

...
--!!! THIS WORKS !!!--
INSERT INTO @tblRecCounts([table_name])
select name from sys.tables where
name not in
('sysdiagrams','sysssislog')

--!!! THIS FAILS !!!--
USE db1
UPDATE @tblRecCounts
SET f.db11PreLoadCount=dtRC.row_count
FROM @tblRecCounts f
INNER JOIN
(
<some subquery>
)...

The script works if I run it with higher permissions (e.g. owner).  The error that db_datawriter gets is "The user does not have permission to perform this action."

What and how do I set permissions for these roles for a table variable that they create??? I would think the entity that creates the variable (table) would own it.


texts: How to confirm if the backups are going to tape in SQL Server 2005 and 2008 R2

texts:
We have many of our SQL Servers where we take the backup locally on the server as well as over the network to other server in case of disaster.

Also, we have a process set that server support team takes those backups on drives for a defined time out to tapes.

Recently we had an issue where server went down and didn't had the backups on its alternative server and so needed to recover them from tape. Initially even from tape that could not be located, something was a miss for sure but we got it finally.

So instead of being dependent on other team, is there a way in SQL may be via script we can get a report or build something that confirms backups are going successfully onto the tape and when?

Please advise, if it can be achieved. Thanks!


texts: Get last full backup and transaction log backup for each database

texts: How to write script or a query that will display the last full backup  and the last log backup for each database on each of the production servers using the system table "backupset" and related backup tables.


texts: Paging in SQL Server 2012 vs. previous versions

texts:
I have used lot of paging code in SQL Server 2008. The format is

With CTE(
 -- SQL With RowNumber
)

-- SELECT ALL Page N

This is working fine. But I have heard that SQL Server 2012 has better paging support. So, if I change all my stored procedures with paging to the new method, will my queries be more efficient?


texts:
I am working on a project in which we need to do IP Address lookups. So for this we have got the datasets and now the next steps is to populate this datasets in to the table. And then have some service setup that will accept IP Address as an input and then lookup the database basis on IP Address. So below is the dataset will look like-

ip_address      country region  city           metro-code   latitude    longitude   postal-code   country-code   region-code    city-code   continent-code  country-code

24.32.116.116   usa       tx    clarksville    623           33.6103    -95.0498    75426            840           44             34918            6            us
65.29.108.232   usa       mi    novi           505           42.4637    -83.4604    48375            840           23             4339             6            us
66.169.99.69    usa       tx    ft worth       623           32.7016    -97.3823    76109            840           44             771              6            us
72.70.100.111   usa       ma    peabody        506           42.5332    -70.9726    1960             840           22             1392             6            us
174.252.116.137 usa       va    oakton         511           38.8892    -77.3222    22124            840           47             3860             6            us
68.55.144.242   usa       md    pasadena       512           39.1276    -76.5125    21122            840           21             4358             6            us
174.252.83.27   usa       pa    lancaster      566           40.0459    -76.3542    17603            840           39             333              6            us
174.78.192.31   usa       ga    warner robins  503           32.5958    -83.6384    31088            840           11             5052             6            us
98.192.146.235  usa       fl    panama city    656           30.1804    -85.5598    32404            840           10             671              6            us
71.192.181.20   usa       ma    springfield    543           42.1187    -72.5483    1109             840           22             967              6            us
76.183.54.227   usa       tx    dallas         623           32.7202    -96.6769    75217            840           44             77               6            us
69.62.143.140   usa       ca    granite bay    862           38.7442    -121.191    95746            840           5              49451            6            us
69.142.76.32    usa       nj    penns grove    504           39.707     -75.4467    8069             840           31             2335             6            us
70.173.54.93    usa       nv    las vegas      839           36.2059    -115.225    89108            840           29             173              6            us
98.242.8.222    usa       ca    fresno         866           36.7968    -119.883    93722            840           5              19               6            us

Problem Statement:-

We are expecting traffic around 100 Millions calls per day for the IP Address lookup.

And this dataset will be worldwide datasets meaning for all the countries. And we are planning to store these datasets in MySql database which is In Memory Database here.

Now my question is should I create only a single table with the above mentioned columns? Or should I normalize the above table into two or three? And do I need to create an index on any columns if any.?

Basically I am trying to know how should I setup the schema for this table so that lookup doesn't take that much time with the traffic we are going to expect. And our service which will be doing lookups should return the response very fast.

I was going through the star schema so If I need to go forward with Star Schema then how can I do that?


texts:
Can we have a sequence and table with the same name? I am trying to create a sequence with the same name as an existing table, but I get this error:

  There is already an object named 'XYZ' in the database.

I haven't found any documentation about this.


texts:
I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those files like mysql-bin.000123, mysql-bin.000223 etc. I have checked the mysql replication by doing show master status and show slave status they are using some mysql-bin files at certain positions, but I guess all the other bin files are leftovers which will not be used anymore. In this case is it safe to delete all those mysql-bin files except the ones that replication is currently pointing at ?

If it is safe to delete, then is there anything I could do to automatically delete those files once they are not in use ?


texts: One to one relationship or another approach?

texts:
I need some addition thoughts on how to approach a database design I'm working on.  The issue is I have three related items...

* Clan (has many games)
* Game (belongs to a clan, has many teams)
* Team (belongs to a game)

Clan -< Game -< Team

Each of them is also treated as a "Site" and they can only ever have one site.  Each site has a standard set of properties, but Clan / Game / Team each have their own unique properties.  It's like Clan, Game & Team are subtypes of a Site.  How would I best model this in a database as the relationships go?

Idea 1: A 1-to-0/1 relationship between multiple tables (doesn't feel like it's a good idea).

Sites
  object_id (primary key, uuid)
  + additional site properties
Clan
  object_id (primary key, foreign key sites.object_id)
  + additional team properties
Game
  object_id (primary key, foreign key sites.object_id)
  + additional team properties
Team
  object_id (primary key, foreign key sites.object_id)
  + additional team properties

Idea 2: Clan, Game & Team each have a column "site_id" that is a foreign key to "site.id".  This makes sense but doesn't provide a way in the database to enforce one Clan/Game/Team per site.

Idea 3: A relationship table, (id, item_type [clan,game,team], item_id, site_id), with a unique constraint on the site_id.  I'm sure this is a "no-no" as I can't do any foreign keys on item_id.

Idea 4: Multiple relationship tables (clan_site, team_site, game_site), still doesn't solve limiting a site to only one item.

Idea 5: Have clan_id, game_id and team_id columns added to the site table.  Although this would work for foreign keys, it doesn't seem right as only one of those columns would hold a value.  Can't think of a way to stop two or more columns being populated and since NULL is supposed to be unknown, would that mean I should really have: clan_id = 2, game_id = 0, team_id = 0 ?

I'm probably missing something obvious or looking at this from the wrong side. :)


texts: Partition function with all future ranges


texts: What settings / statements are pushed to subscribers in transactional replication?

texts:
Version: SQL Server 2014 (Web edition)
Replication: Transactional

Are the following pushed to subscribers in transactional replication?

SET_QUOTED_IDENTIFIER statement;
SET ANSI_NULLS statement;
CREATE PROCEDURE statement (we have "CREATE PROCEDURE" on publisher and "CREATE PROC" on subscriber);
Comments.


texts:
Scenario:

two databases: DB_A and DB_Archive  with one very big table called
tableA.
every day, records older than 60 days are deleted from DB_A  and moved
to DB_Archive mainly to leave thing "separated" because tableA is heavily queried on DB_A for records of the past 2 months.

I want to get rid of this process because it is slow and consumes a lot of resources.
I'm thinking of implementing table partitioning on DB_A with a partition function on a date column and storing all records < 2 month on one partition and all records > 2 months on another partition. My questions:

is this scenario going to behave like if I had 2 different databases? If I
query my tableA for records > getdate() - 30, is it going to read the archiving partition?
I supposed I have to partition the indexes as well, right?
How do I deal with the fact that tomorrow my partition function will
"change", I mean, if I create the function today (2nd of July, its
range will be 2nd of May, but tomorrow would be the 3rd of May). Can
I create a dynamic partition function?


texts:
I havent worked with Pivot. Not sure if pivot is the right thing to use. Is it possible to show this data:

-------------------
| Name     | Code |
-------------------
|Test A    | 1    |
|Test B    | 2    |
|Test C    | 3    |
-------------------

In this format:

------------------------------
| Customer | Fleet | Account |
------------------------------
|Test A    |Test B |Test C   |
------------------------------

If Code is 1 it is Customer.  If Code is 2 it is Fleet.  If Code is 3 it is Account.


texts: Is there a tool to check if my database is normalized to the third normal form?

texts:
I learned about normalization recently, and understand how important it is when implementing a new schema.

How can I check if my database is 2NF or 3NF compliant ?

Manual review is a sure option, but I'm looking for an automated tool here.

I'm not looking for a point-and-click tool, more something that would highlight possible optimizations to make a table 3NF compliant. I guess it might use statistics based on good sample data and/or semantic analysis of columns names.


texts: Difference between transaction log and redo log in MySQL

texts: I have read about MySQL. In my opinion, I see two log types as very similar. MySQL saves how and when data is changed in log. Information is used for recovery of MySQL. I am confusing the function of two log types.


texts: Multiple filegroups vs. multiple files in a single filegroup

texts:
From what I've read online, it seems that multiple filegroups offer only two advantages over having multiple files in a single filegroup:

The ability to isolate a specific table to a specific drive - the only way to accomplish this is to add a new filegroup with a file on the target drive, and then move the table there.
Restores can be sped up by doing a piecemeal restore with a read-only filegroup.

Are there any other advantages that multiple filegroups would have over multiple files in a single filegroup?  I'm most interested in performance advantages, but welcome any ideas.


texts:
Using sqlite3 on Linux, how can one work with multiple databases?

I'd like to do something like the following in database 1?

sqlite3 database1.db
insert into database1.mytable values (select * from database2.mytable)

How do I write the SELECT query for database2 above?


texts:
I'm trying to implement a behaviour similar to Facebook (okay, most similar to YouTube).

In my table I got various Products, each Product can be liked or disliked by a Customer. I don't want to let the users rate (let's say between 1 and 5 stars), so a simple like or dislike is enough.

What's the best way of representing that?

I've thought about three essential tables (next to Customers):

Products (Id, Name, ...)

ProductLikes (Id, CustomerId, ProductId, CreatedOn)

ProductDislikes (Id, CustomerId, ProductId, CreatedOn)

But that would mean to have two tables with nearly the exact same data.

Another idea was to have the following:

Products (Id, Name, ...)

ProductLikes (Id, CustomerId, ProductId, IsLike, CreatedOn)

But that would mean I cannot provide a third state later on, if I decided to do so.

And my last idea:

Products (Id, Name, ...)

ProductRatings (Id, ProductId, CustomerId, Rating, CreatedOn)

Where a "like" would be a Rating of 5.0, a "dislike" one of 1.0.


texts: Shrink Log File without breaking Log Chain

texts:
What is the best way to shrink a log file without breaking the log chain?  I realize you can do something like:

alter database yourDb
set recovery simple
go

dbcc shrinkfile(LogFileName, 0)
go

alter database yourDb
set recovery full
go

But I really don't want to break the log chain.  Is there any way to accomplish this?


texts: How to give title to the result of a select query in postgresql?

texts:
For example I want to do like this:

select id,name from first;

It should look like this:

The Query Result // this should be the title of the query
id name
1  Ali
2  Sami


texts: Is it possible to install Developer Edition of SQL Server as the primary development server?

texts:
Is it possible to install Developer edition of SQL Server on a Windows server environment as development server? Each developer will have a local copies of same SQL Server edition and will connect to this development server for data/object synchronization.

Also, if you can refer a link for a full description of this product.


texts: MS SQL 2008 Databse in Suspect mode and CHECKDB returns error: Object ID 3 has chain linkage mismatch

texts:
We did a disk expansion for VM DB server. However, after this operation the DB became suspect mode and cannot be open.

Then we tried somethings like that;

https://www.sqlrecoverysoftware.net/blog/repair-database-from-suspect-mode.html

But the first CHECKDB step returned the following results:

  Msg 7987, Level 16, State 1, Line 1 System table pre-checks: Object ID
  3 has chain linkage mismatch. (1:13853247)->next = (1:446), but
  (1:446)->prev = (1:17209791).

  Check statement terminated due to
  unrepairable error. DBCC results for 'myDB'. CHECKDB found 0
  allocation errors and 0 consistency errors in database 'MyDB'.

Also, we bought some data recovery tools but they can transfer some of the data, not all of it.

Are there any suggestions?


texts: Database keeps using utfmb4 instead of utf8

texts:
Using MariaDB 10.1. The default encoding is utf8mb4, but I have an existing database that uses utf8.

So in order to re-use the same DB, I would have to keep using utf8 for now. So I set the server variables to the following in my /etc/mysql/my.cnf:

[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

To ensure it's working, I checked the following:

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_client     | utf8            |
| character_set_connection | utf8            |
| character_set_database   | utf8            |
| character_set_filesystem | binary          |
| character_set_results    | utf8            |
| character_set_server     | utf8            |
| character_set_system     | utf8            |
| collation_connection     | utf8_general_ci |
| collation_database       | utf8_unicode_ci |
| collation_server         | utf8_unicode_ci |
+--------------------------+-----------------+

However, everytime I run migration with new tables and columns, they are created with encoding: utf8mb4, collation: utfmb4_general_ci, which further throws the Specified key was too long; max key length is 767 bytes error because I have column with varchar(255) which needs to be indexed.

Have been working on this for hours and couldn't find anything else to try. Please advise. Thank you.


texts: SQL Server Express 2017 database size limitation

texts:
I have a database hosted on SQL Server Express 2017. The size was small like 200MB. Today I have copied 24GB tables to the same database from external database hosted on different server.

The surprising thing is that everything is working perfectly!
As far as I know that size limitation of a database hosted on SQL Server Express 2017 is 10GB. Can someone please help me understand what I am missing?

Edit: When I run: sp-spaceused on the same database I get this output: Database_size: 24510.38 MB


texts: SQL Server imports data in wrong charset with SSIS

texts:
I have a flat file which I am importing into a SQL Server table using a SSIS package. The code page is set to 65001.

However, when I run a

SELECT * FROM tableName

the data appears mangled. For some reason it is not being stored in Unicode. The column is nvarchar, so that should not be an issue.

Also, if I insert data manually, the data is stored as expected.

What could be going wrong?