texts:
Sample data :

LINE_NO E_FIELD F_FIELD G_FIELD HSA_STATUS  FAMILY  CACHE_FAMILY    Count
23053B  00000   00000   00000   S           SUMMIT      WER           43
23053B  00000   00000   00000   T           SUMMIT      WER           144
23053B  00000   00000   00684   T           SUMMIT      WER           2
23053B  00353   00418   00684   T           SUMMIT      WER           1
23053B  00353   00418   00763   T           SUMMIT      WER           1
23053B  00353   00418   01512   T           SUMMIT      WER           1
23053B  00353   00418   06797   T           SUMMIT      WER           1
23053B  00353   00418   30228   T           SUMMIT      WER           1
23053B  00353   00418   31935   T           SUMMIT      WER           2
23053B  05601   01402   00758   T           SUMMIT      WER           1
23053B  05601   01402   09091   T           SUMMIT      WER           1
23053B  05601   01402   65053   T           SUMMIT      WER           1

This is my query:

SELECT LINE_NO,
  E_FIELD,
  F_FIELD,
  G_FIELD,
  HSA_STATUS,
  FAMILY,
  CACHE_FAMILY,
  Count = ((SUM(TOTAL)) )
FROM
(
  SELECT LINE_NO,
    E_FIELD,
    F_FIELD,G_FIELD,
    HSA_STATUS,
    FAMILY,
    CACHE_FAMILY,
    Count(LINE_NO) as Total
  FROM TX_HSA_SUMM
  WHERE MT_TIMESTAMP2 BETWEEN ('2013-03-07 10:10:00') AND ('2013-03-08 10:20:00')
  GROUP BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY
) as a
GROUP BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY,total
ORDER BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY,total

This is my sample data actually. I already make the count. As You can see the column header. My problem is I need to sum the count and the for each row I need to get the percentage. For example the total for the above record is 199 so for the first record count is 43 so the calculation should be 43/199 * 100. How can I view the percentage? Please help me I need this urgently.


texts: How do I create a bounded range type in the SQL Server?

texts:
I want to create a bible verse table that has one field representing a range of bible verses.  Currently I represent this range as pair of values (VerseBegId and VerseEndId).  I want something like the Sql Server spatial data type

Instead of

CREATE TABLE BibleVerse AS TABLE
(
    VerseBegId int NOT NULL,
    VerseEndId int NOT NULL
)

I want

CREATE TABLE BibleVerse AS TABLE
(
  VerseRange ??? NOT NULL
)


texts:
I'm converting some stuff over to be compatible with SQL Server 2012 and had a couple of questions. I'm taking out all of the sp_dboption commands and making them ALTER commands instead, but there were a few options I haven't been able to tell the equivalent of. I was wondering if anyone here knew.

Here are the sp_dboptions I couldn't find:

bulkcopy
trunc. log
dbo use
single
ansi null default
db chaining

Here is the website I was using if it helps:

http://technet.microsoft.com/en-us/library/bb522682.aspx


texts: Create SQL Server Agent job for stored procedure with input parameter

texts:
Could you suggest please how to create SQL Server Agent job for a stored procedure that have 1 input parameter?

The procedure is correctly created and i executed it using this code :

EXECUTE dbo.MYProcedure N'2016-02-25';

Could you suggest please how to create SQL Server Agent job for a stored procedure that have 1 input parameter?

The procedure is correctly created and i executed it using this code :

EXECUTE dbo.MYProcedure N'2016-02-25';
Is there a way to create a SQL Server Agent job for this procedure that have parameter ?

So i'm trying the basic way that is add this ligne in  EXECUTE dbo.MYProcedure N'2016-02-25'; to the window of step in job But the paraméter can change


texts: Oracle: force shutdown as root without knowing logins

texts:
I am root on a GNU/Linux machine with Oracle 10.2.0.1.0 XE installed. I need to shut down Oracle, but I do not have admin logins to Oracle.

How do I as root force Oracle to shutdown?

I have a normal user login. When using that I get:

ORA-01089: immediate shutdown in progress - no operations are permitted

This does not change even if I run:

sudo /etc/init.d/oracle-xe restart

I have the feeling I need to create an administrative user from the Unix prompt, login in as this user, and run some magic command (probably one of Oracle Shutdown Method).

My guess is you probably need to fiddle with these files:

$ ls $ORACLE_HOME/dbs
hc_XE.dat  init.ora  lkXE  orapwXE  spfileXE.ora

But exactly what needs to be done?


texts: How do you index alphanumeric foreign keys on large fact tables?

texts:
I have a field that is an alphanumeric field that ideally is a encrypted field for a non-unique identifier. It's used to relate other rather large fact tables in a many-to-many relationship. I do not have the related dimension for this field because there is no other attributes for this FK.

Example: Abcdefgh12345

This field is in a rather large and growing data warehouse where the Fact tables are clustered on time and non-clustered on keys like these.

The column is VARCHAR(50) and only vary between 45 and 50. Would have to check, but I would assume the collation is SQL_Latin1_General_CP1_CI_AS. I use no FK for optimization reasons. All controlled by the ETL.

Fragmentation

Due to the type of key, it makes it hard to index. It's fragmentation is managed by a series of tests I did recently that showed 75% fill factor at least manageable with reducing the fragmentation on daily incremental loads for a least a week until a full rebuild may be needed, which is fine once a week.

Performance

With the reducing of the fill factor from 100% to 75%, the inserts and reads have become slower. The records have grown bigger as expected too. Any indexes with includes are pretty much driving the performance into the ground on inserts, but of course helping queries out that need them 10x better.

Question

Does anyone have any good experience working with alphanumerics in a data warehousing environment? How it's handled and index now is fine, but I think it could be better. I was dabbling with the idea of ripping out the keys, forming a new dimension and adding a more manageable key during the ETL process.


texts: MySQL: how to update a table based on changes to another table with trigger or stored procedure

texts:
Because of the limitation of a wordpress plugin I am using I cannot update tables from the website when they are created from JOIN statement. So I created a bigger table with data of all tables. But I want that when a website user edits a field in this table then the update should be reflected to the "joined" tables.

For example I have these tables (in reality the tables have ~20 fields and the big one has ~50 fields):

Students:

+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | John |  34 |
|  2 | Mary |  21 |
+----+------+-----+

Courses:

+----+--------+----------+
| id | course | semester |
+----+--------+----------+
|  1 | math   | 1st      |
|  2 | geo    | 2n       |
+----+--------+----------+

Registrations:

+----+------------+------+-----+-----------+--------+----------+
| id | student_id | name | age | course_id | course | semester |
+----+------------+------+-----+-----------+--------+----------+
|  1 |          1 | John |  34 |         1 | math   | 1st      |
|  2 |          2 | Mary |  21 |         2 | geo    | 2n       |
+----+------------+------+-----+-----------+--------+----------+

So when user for example a user edits the name of a student then the edit has to be made both to the Registrations table and to the Students. But it has to be done via a trigger or a stored procedure in phpmyadmin because I dont want to change the functionality of the wordpress plugin. How I can create this kind of trigger or stored procedure in Students or Courses?


texts:
Consider the following scenario. I have MySQL installed on a Windows Server. All files in this server are backed up to a NAS drive by Acronis. My server is broken down. I re-installed operating system and MySQL server. MySQL server is up now. How can I restore my databases from my backup files? Where are the database files located?
Note: The backup has the whole files of the computer.


texts: Is there a way to prevent developers from creating new user-defined functions that use a cursor?  Would a database trigger that can read the code of a UDF do?


texts: Getting "Cannot open user default database. Login failed." for connections that don't specify a default database

texts:
I have two virtual machines. Server A is running SQL Server 2000. Server B is running SQL Server 2008R2. I am using the same domain user to log in to both boxes.

But I cannot log in to SQL Server locally on server A. I get Cannot open user default database. Login failed. when attempting with Query Analyzer or Enterprise Manager. I can connect from server B using SSMS if I specify a database to connect to. Once thus in, I have tried changing the default database for my user to 'master' via the GUI and via exec sp_defaultdb 'DOMAIN\username', 'master' but it does not change things. I have tried setting other databases as the default and verified that those databases are queryable by me. No luck. Unless I open a connection with SSMS and specify a database, the login fails.

This login problem is also preventing me from running linked queries against server A. How can I get this fixed?


texts:
I have 980 DBs on 1 server and I am supposed to grant readonly access to a couple of users.

I certainly don't want to run alter DB add user... statement since it is definitely time consuming.

I don't know if exec sp_addrolemember 'db_datareader', 'user' works?

Can someone let me know if something else can be done?


texts: Can a database's files be copied while a database is online?

texts:
I'm working on setting up a development copy of a production database on SQL Server 2008 R2 SP1. The live database is getting lightly used by two developers for read only queries currently, but the new database will have updates done on it as well.

Since the database is 2.1TB and took a total of 3 days to restore and update to the latest build we need for testing, my original plan was to create a new set of backup files and then restore from those files. This would allow me to create the development copy of the database on the same SQL instance and machine, without having to take the current database offline.

However, in order to save a couple of those days, I was thinking that it might be a good idea to just copy over the physical database files and attach the new copy of the database. Unfortunately when I try to copy I get an error referring to the lock that SQL Server puts on those files.

Since I can't take the database offline for anything but transferring the log files (I can finish this before people get in in the morning), is there any way I can copy the live database files without putting the database into an offline state? Or should I wait until people go home to do that?


texts: Different Results From sys.databases As Different Users

texts:
We have a SQL Agent job that is configured to run as user "dbo".  This job gets a list of databases defined in sys.databases that match a naming scheme.  The job then performs some dynamic SQL to cleanup some old transaction tables.  The job starts against tempdb.

The agent job runs fine on my own development database (SQL Server 2008 R2 Standard Edition 10.50.2500.0).  The job correctly gets the list of databases.  However, on a customer system (SQL Server 2008 R2 Standard Edition 64-bit 10.50.1600.1), the job does not find any matching databases despite them existing.

On the customer system, a simple select * from sys.databases run against tempdb in a query window returns all 10 databases in the system.

Using the profiler, I saw SQL Agent call EXECUTE AS USER = N'dbo' WITH NO REVERT when running this job.  I duplicated the issue using the following SQL:

use tempdb

-- results: tempdb, <my SQL user>, dbo
select
    db_name() as [Current Database],
    suser_name() as [Current Context],
    user_name() as [Current User]

-- results: tempdb, 10 (which I expect)
select
    db_name() as [Current Database],
    count(*)
from sys.databases

execute as user = N'dbo'

-- results: tempdb, sa, dbo
select
    db_name() as [Current Database],
    suser_name() as [Current Context],
    user_name() as [Current User]

-- results: tempdb, 2 (?!?)
select
    db_name() as [Current Database],
    count(*)
from sys.databases

revert

If I change 'use tempdb' to 'use master', the results are the expected 10 databases for each query.

Why would sys.databases return different results when run as the user dbo from the tempdb database?  And why does it return all databases with use master as the dbo user?

More information - If I run this script in SQL Server Management Studio without including the revert at the bottom, my list of databases shrinks to the same two that this query  returns from sys.databases.


texts: How to insert several csv files into Elasticsearch?

texts:
I have several csv files on university courses that all seem linked by an ID, that you can find here, and I wondered how to put them on Elasticsearch. I know, thanks to this video and Logstash, how to insert one sole file csv file to Elasticsearch. But do you know how to insert several such as those in the provided link ?

At the moment I started with a first .config file for a first csv file : ACCREDITATION.csv. But it would be painful to write them all...

The .config file is :

input{
    file{
        path =>"Users/mike/Data/ACCREDITATION.csv"
        start_position => "begining"
        sincedb_path => "/dev/null"
    }
}

filter{
    csv{
        separator => ","
        columns => ['PUBUKPRN', 'UKPRN', 'KISCOURSEID', 'KISMODE', 'ACCTYPE', 'ACCDEPEND', 'ACCDEPENDURL', 'ACCDEPENDURLW']

    }
    mutate{convert => ["PUBUKPRN","integer"]}
    mutate{convert => ["UKPRN","integer"]}
    mutate{convert => ["KISMODE","integer"]}
    mutate{convert => ["ACCTYPE","integer"]}
    mutate{convert => ["ACCDEPEND","integer"]}
}

output{
    elasticsearch{
        hosts =>"localhost"
        index =>"accreditation"
        document_type =>"accreditaiton keys"
    }
    stdout{}
}

Update May, 3rd

Without knowing how to use a .config file to implement csv files to Elasticsearch, I fell back to Elastic blog and tried to do a shell script importSVFiles for a first .csv file before trying to generalize the approach :

importCSVFiles :

#!/bin/bash
while read f1
do
   curl -XPOST 'https://XXX.us-east-1.aws.found.io:9243/courses/accreditation' -H "Content-Type: application/json" -u elastic:XXX -d "{ \"accreditation\": \"$f1\" }"
done < AccreditationByHep.csv

Yet I received a mapper_parsing_exception on the terminal :

mike@mike-thinks:~/Data/on_2018_04_25_16_43_17$ ./importCSVFiles
{"error":{"root_cause":
            [{"type":"mapper_parsing_exception","reason":"failed to parse"}],
          "type":"mapper_parsing_exception",
          "reason":"failed to parse",
          "caused_by":{"type":"i_o_exception","reason":"Illegal unquoted character ((CTRL-CHAR, code 13)):
              has to be escaped using backslash to be included in string value\n at [Source: org.elasticsearch.common.bytes.BytesReference$MarkSupportingStreamInputWrapper@e18584; line: 1, column: 88]"}
         },"status":400
}


texts: Dynamic where condition in a stored procedure

texts:
I am using Oracle 11g. I am trying to get the table data with this procedure:

CREATE OR REPLACE PROCEDURE test(p_table IN varchar2)
IS
v_sqltxt varchar2(4000);
BEGIN
v_sqltxt:='select count(*) from '||p_table;
dbms_output.put_line(v_sqltxt);
END;
/

But I am unable to give the where condition dynamically. How can I do that?


texts:
I have a fail-over cluster set between two Windows Server 2016 machines, and I'm seeing errors regarding the DNS record, both for the cluster itself and for any listener I try to add in SQL high availability.

  Cluster network name resource 'Cluster Name' failed registration of one or more associated DNS name(s) for the following reason:
  DNS server failure.

  Ensure that the network adapters associated with dependent IP address resources are configured with at least one accessible DNS server.

Properties of the cluster resource - img

I've looked through this link and I do see the 8.8.8.8 DNS on my machines, after the records for the domain DNS - these DNS settings are automatically pushed from our DC and I'm not sure I can change them.
Has anyone experienced this? Is there another solution?


texts: VLDB with large quantity of data file imports

texts:
I am a little out of my league with this scenario and hope this community can help get me on the right direction.

I have recently been tasked with re-designing and implementing a database application where the company I work for processes a large quantity of text files (sizes ranging from a 100MB to 3GB).  We currently process approximately 600 of these files per year.  This data needs to be accessible after our initial processing.

The current implementation has each file being imported via Application bulk load or SSIS to a "temp" database as individual tables.  I would like to keep this process in place as it gives a good entry point for when manual entry into processing is needed.

I am in the process of trying to create a new MS SQL Server installation and architecture, and am looking for advice/articles which would cover a similar scenario as to best practices (partitioning, filegroups, etc.).  I have worked in databases for a few years, but not at this level.

So if you have any experience in this type of scenario or know of a good article or book you could point me to, I would be extremely grateful.  I just need a nudge in the right direction.  Thank you in advance.


texts: Deadlock Error from SQL Server 2000 to SQL Server 2008

texts:
After migrating  data from  SQL Server 2000 to SQL Server 2008, when I am doing any a transaction in the table which find out more row on  the database, that's gives me error.

 Transaction (Process ID 59) was deadlocked on lock resources with
 another process and has been chosen as the deadlock victim.
 Rerun the transaction.

There are 3 triggers on the table (Insert,Update,Delete). How can I solve  this problem?


texts:
Scenario:

We have two tables Tbl1 & Tbl2 on the Subscriber Server. The Tbl1 is being replicated from Publisher Server A and it has two triggers - insert and update. The triggers are inserting and updating the data into Tbl2.

Now, we have to purge (approx. 900 million records) from Tbl2 which has total 1000+ million record. Below is the data distribution for one month to one minute.

One Month - 14986826 rows
One day - 483446 rows
One hour - 20143 rows
One minute - 335 rows

What I am looking for;

The fastest way to purge that data without any production issue, data consistency, and possibly no downtime. So, I am thinking to follow the below steps but stuck :(

Steps:

BCP Out the required data from the existing table Tbl2 (around 100 million records, it may take approx. 30 mins).

Let’s assume I started doing the activity on 1Fab2018 10:00PM, it finished at 1Fab2018 10:30PM. By the time activity will be completed, the table Tbl2 will get new records that becomes delta

Create a new table in the database with name Tbl3
BCP in the exported data into the newly created table Tbl3 (around 100 million records, it may take approx. 30 mins)
Stop the replication job
Once BCP-in get completed, use tsql script to insert the new delta data.
The Challenge is - How to deal with delta “update” statement?
Start the Replication

Additional Question:

What is the best way to deal with scenario?


texts: SQL Server: Transaction log still very large after log backup

texts:
I have a database that is on Full recovery mode.

I previously made a full backup, and then on a daily basis add differential backups.

Now I have backed up the transaction log for the first time, which reflected in the greatly increased file size of the .bak file. However the log file in the \DATA directory is still the same size.

I was under the impression that the transaction log file would automatically be truncated on a log backup, as mentioned here and elsewhere.

Previously I asked a question on Stack Overflow (here) where the answer indicated that the log file info was not up-to-date with the actual log on disk. Is that the case with this observation?

If so, how can I get the Windows file to refresh to display up-to-date data?


texts: What is shared between two instances on the same server?

texts:
We want to install multiple instances of SQL Server on a single Windows server.  We cannot use VMs because the server is bare metal Windows 2008 R2.

For security purposes we need to ensure that the two instances share nothing, including management, Windows services, accounts, etc.  So I guess the question is:

When installing more than one "instance" of SQL Server on a Windows server, what is shared?


texts: Index creation with multiple columns - should I combine as many as possible?

texts:
When creating indexes and especially those with multiple columns, should I look to combine as many columns in the index as would be useful?

I've ran a script on the stats to pick up missing indexes and there's some suggestions such as:

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [StringId], [DateNextDeadline])

And

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [DateNextDeadline])

And

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [ClientId], [DateNextDeadline])

They all have the same [Id] and [DateNextDeadline] columns in there, and they all are indexed on the same two main columns.

Should I, in this instance, create a single index with ([Id], [StringId], [ClientId], [DateNextDeadline]) and will SQL Server use the same column indexes to and pick the including columns depending on the query? Or would it only use the index if there's an exact match? So I would need to create three indexes?


texts:
I have class named 'User'. It has the following properties

firstname
lastname
mobile
email
pin

I want to design a table or hibernate entity that will have unique email for each row; the same for mobile. One of email or mobile will be allowed to be null. I should be able to fetch a record by using email or mobile independently.


texts: Insert from one row to another using cases

texts:
My original table was(being used since 2005):

CREATE TABLE `request` (
    `msg` VARCHAR(150) NOT NULL,
    `id` VARCHAR(20) NOT NULL,
    `ctg` VARCHAR(10) NOT NULL DEFAULT 'misc',
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `status` VARCHAR(10) NOT NULL DEFAULT 'empty',
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    UNIQUE INDEX `msg_id` (`msg`, `id`),
    UNIQUE INDEX `msg_ctg` (`msg`, `ctg`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
CHECKSUM=1
AUTO_INCREMENT=18491;

Since, it was neither good management of table nor appreciable by me, I created a newer table:

CREATE TABLE `requests` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ctg` VARCHAR(15) NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `filled` ENUM('Y','N') NOT NULL DEFAULT 'N',
    `dated` DATETIME NOT NULL,
    `filldate` DATETIME NULL DEFAULT NULL,
    `filledby` VARCHAR(32) NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `nick_msg` (`nick`, `msg`),
    UNIQUE INDEX `ctg_msg` (`ctg`, `msg`)
)
COMMENT='Requests from users in any of the categories.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

What I want to do now is:

SELECT data from first table and insert as follows:

msg to msg, id to nick, ctg to ctg and date to dated are copied as is.
status in original table is either empty or filled. I want to use a CASE clause while insertion so that Y and N are inserted accordingly.

My query:

INSERT INTO `requests`( `id`, `ctg`, `msg`, `nick`, `dated`, `filled` )
SELECT `fid`,
    `ctg`,
    `msg`,
    `id`,
    `date`,
    CASE
        WHEN `status` = 'empty' THEN 'N'
        WHEN `status` = 'filled' THEN 'Y'
    END CASE
FROM `request`

My question is

  Is the query above perfect? Also, one of the sample string from request table with 'filled' status is like:

.hack//SIGN (filled by hjpotter92 in 02/08/12 09:47:07 )

the format is the same: filled by <<nick>> in <<<date>> in <<mm/dd/yy H:I:S>> for all filled entries. Can I extract the date from there and use it with STR_TO_DATE() to pass it in filldate field too? I know it'd need me to create a procedure/function.

For now, I am thinking of using PHP for the task but I wanted a clean MySQL solution if possible.


texts: WHERE...LIMIT much slower than WHERE...ORDER BY...LIMIT in MySQL 5.5

texts:
I'm using MySQL 5.5 (but i see the same behavior on 5.7)
Here is the table schema:
CREATE TABLE `so_table` (
  `p` char(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `c1` int(10) DEFAULT NULL,
  `c2` smallint(5) NOT NULL,
  `c3` tinyint(3) NOT NULL,
  `c4` int(10) NOT NULL,
  PRIMARY KEY (`p`),
  INDEX `so_table_index_1` (`c1`,`c2`),
  INDEX `so_table_index_2` (`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

The table contains around 2 million rows. The query below is considered a slow query and sometimes takes 20 seconds to execute:
SELECT p, c1 FROM so_table WHERE c1 IS NOT NULL AND c3 = 1 AND c4 < 195217615 LIMIT 100;
Running EXPLAIN on the query above returns the following:

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
Extra

1
SIMPLE
so_table
NULL
range
so_table_index_1,so_table_index_2
so_table_index_1
5
NULL
1,281,736
Using index condition; Using where

I was reading about optimizing LIMIT and it seems that adding ORDER BY makes things better, so the following query indeed has a better execution plan (i haven't tried it on the real data though)
SELECT p, c1 FROM so_table WHERE c1 IS NOT NULL AND c3 = 1 AND c4 < 203322189 ORDER BY p ASC LIMIT 100;
And EXPLAIN shows the following:

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
Extra

1
SIMPLE
so_table
NULL
index
so_table_index_1,so_table_index_2
PRIMARY
48
NULL
428
Using index condition; Using where

So it seems that this query is going to be faster. My guess is that ORDER BY forces the optimizer to use the primary index which is unique and maybe this helps the optimizer decide to not actually run through all rows. I was reading MySQL docs, but i couldn't figure out if my theory is right or what's the real reason is.


texts:
According to the documentation, so long as I'm not connected to a database, I can either delete a database in the console using:

DROP DATABASE dbname;

Or I can drop it using the wrapper tool dropdb.

Both give me an error saying the database doesn't exist, yet when in the console and typing the command \l, I get a list of databases including the one I want to delete.

                                          List of databases
           Name            |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
---------------------------+-----------+----------+-------------+-------------+-----------------------
 Blog_development          | myusername | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Blog_test                 | myusername | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

The database name is Blog_development (and the one below it). I was playing with rails and trying to learn from the online documentation. I wanted to start over and delete everything.

When trying to delete it however, it says it doesn't exist.  I'm brand new to PostgreSQL so I'm a bit lost, nothing in the documentation about this error other than it popping up when it doesn't exist. Of course it exists, it's right there.


texts: Is it possible to alias a column with a constant variable defined in the package header of a PL/SQL package?

texts:
I am trying to alias some columns that are being used to interface with a 3rd party data aggregation system. This company has stated that they might change their naming scheme in the near future, so I preemptively created constants to be used as column aliases in my PL/SQL package. However, it doesn't seem like constants can be used to alias columns.

After quite a bit of searching I found that variables can't be used to alias column names dynamically (there are lots of questions about dynamic aliasing), but I wasn't able to find anything stating whether or not they can be aliased using constants.

My attempt to do so failed and Google search results returned negative, so I assume the answer is no.

If the example below isn't feasible are there any alternative ways to alias columns, so that they only need to be changed in one location?

Example PL/SQL Code

CREATE OR REPLACE PACKAGE sample_package AS
    CONSTANT_VARIABLE_ONE CONSTANT VARCHAR(32) := 'W Y S I W Y G F T W & F Y I';
    CONSTANT_VARIABLE_TWO CONSTANT VARCHAR(32) := 'REallY BaD coLM NamE';
END sample_package;

CREATE OR REPLACE PACKAGE BODY sample_package AS
    PROCEDURE procedureOne(output_reference_cursor OUT SYS_REFCURSOR) IS
    BEGIN
        OPEN output_reference_cursor FOR
            SELECT
                updated_timestamp AS CONSTANT_VARIABLE_TWO,
            FROM
                emp
        ;
    END procedureOne;

    PROCEDURE procedureTwo(output_reference_cursor OUT SYS_REFCURSOR) IS
    BEGIN
        OPEN output_reference_cursor FOR
            SELECT
                updated_timestamp AS CONSTANT_VARIABLE_ONE,
            FROM
                users
        ;
    END procedureTwo;

    PROCEDURE procedureThree(output_reference_cursor OUT SYS_REFCURSOR) IS
    BEGIN
        OPEN output_reference_cursor FOR
            SELECT
                updated_timestamp AS CONSTANT_VARIABLE_TWO,
            FROM
                administators
        ;
    END procedureThree;

    PROCEDURE procedureFour(output_reference_cursor OUT SYS_REFCURSOR) IS
    BEGIN
        OPEN output_reference_cursor FOR
            SELECT
                updated_timestamp AS CONSTANT_VARIABLE_ONE,
            FROM
                random_person
        ;
    END procedureFour;
END sample_package;


texts: Why should I apply a CU for SP2 that was released after the release of SP3?

texts:
Checking SQL Server 2014 build versions to identify the right update for my server I got a little confused as what update to use. Did some research and found out I should Apply the latest Service Pack and Cumulative Update. Ok, but one question remained:

I currently have SP2 installed and there's a CU for SP2 with the release date of July 29, 2019, but SP3 has been released October 30, 2018.

The way I see it, the CU15-CU18 for SP2 shouldn't exist. (I know I'm wrong, but I don't know why)
What's there in a service pack that one might not want to install it but will install other CUs that were released after the most recent SP?


texts: MySQL row replication - running triggers on slave only to keep track of changes

texts:
I have this configuration:

I have two track inserts/updates/deletes on user table and save those changes to user_events table but I can't create new tables or triggers on master.

I have setup slave and it perfectly replicates data from user to user table. However triggers on slave are not fired. Tried binlog_mode MIXED and STATEMENT. Neither worked.


texts: Upgrade older SQL 2008 to 2008 R2 on active/passive cluster

texts:
I have an active/Passive SQL Server 2008 cluster in production that I need to upgrade to SQL Server 2008 R2. I just want to make sure I understand all the steps before starting this in production.

Run the SQL installer on the passive node and get through the setup files. Exit the installer and reboot. fail the cluster to the passive node.
Run the installer on the active node and update the shared components and SQL Setup files. Exit the installer and reboot. Fail the cluster back.
Run the installer on the passive node and select the Upgrade option.
Reboot and patch (service pack, etc) until no new patches are available on the passive node.
Fail the cluster to the passive node.
Upgrade and patch the original active node.
Fail the cluster back to the original Active node.

Did I miss anything? Are there any major issues I should be aware of?

I am only running the SQL instance and full text search. SSAS and SSRS are not installed.

I am using this KB as my guide - http://msdn.microsoft.com/en-us/library/ms191295.aspx


texts: IP configuration for MultiSubnet AAG setup

texts:
Is it  a good practice to have all the ip's in a different subnet for an AAG setup as mentioned in the link below? When I wanted to setup a 2 node AAG, the windows team in my office built the cluster and sent it over to me but apparently, all the ip's belong to the same subnet. The 2 Node setup is within the same Datacenter. We plan to expand this with a 3rd node in another site at SanJose later on

https://blogs.msdn.microsoft.com/sambetts/2014/05/16/building-true-multi-subnet-windows-server-failover-clusters/


texts: Where does SQL_Variant datatype come from in select statement using LOGINPROPERTY

texts:
In the below code I do not understand where SQL_Variant is coming from - The column it is complaining about is LOGINPROPERTY([name], 'PASSWORDLASTSETTIME') AS LastChangeTime According to MSFT documents this is a DATETIME column.

Where does the SQL_Variant datatype come from?

DROP TABLE #PasswordChange;
CREATE TABLE #PasswordChange (name VARCHAR(50),
lastPWChangeTS DATETIME,
PWhash VARBINARY(26));

INSERT INTO #PasswordChange (name,
lastPWChangeTS,
PWhash)
SELECT [name],
LOGINPROPERTY([name], 'PASSWORDLASTSETTIME') AS LastChangeTime,
LOGINPROPERTY([name], 'PASSWORDHASH') AS PWhash
FROM sys.syslogins
WHERE loginproperty([name], 'PASSWORDLASTSETTIME') > DATEADD(HOUR, -24,
GETDATE());

Msg 257, Level 16, State 3, Line 6
Implicit conversion from data type sql_variant to datetime is not
allowed. Use the CONVERT function to run this query.


texts: In sql server row and page lock can be escalated to table level. Can table level locks be escalated to a schema level, schema locks to database level and so on?


texts:
This is a problem I've been trying to work through on and off for the past month, and haven't made any progress. Essentially, I am working with a HR system that stores two major types of position for employees: Primary (P) and Acting (A). The position types aren't hugely relevant, but are important to know to understand the way the data is structured. An employee will always be in a Primary position, but may be put into an Acting position temporarily while it is vacant, or while the Primary person for that role is on leave.

The way that this is represented in the system is diagrammed below (as an example and not to scale):

P (2018-07-28 - 9999-99-99) <------------------------------------------------------------->
A (2018-09-04 - 2018-09-05)             <--->
A (2018-11-12 - 2018-11-13)                                          <--->
A (2018-11-19 - 2018-11-20)                                                  <--->

Due to a set of data I'm trying to join the position information with, I need it to be intersected to look like this, where the Acting position always takes precedence over the Primary position.

P 2018-07-28 - 2018-09-03 <---------->
A 2018-09-04 - 2018-09-05             <--->
P 2018-09-06 - 2018-11-11                  <----------------------->
A 2018-11-12 - 2018-11-13                                           <--->
P 2018-11-14 - 2018-11-18                                                <----->
A 2018-11-19 - 2018-11-20
<--->
P 2018-11-21 - 9999-99-99
     <----->

Below is a representation of the data directly from the database, to give an idea of what it looks like overall:

Employee    Position    Start Date  End Date    Position Type
1           30          2016-12-10  2016-12-11  P
1           30          2016-12-12  2017-12-08  P
1           20          2017-01-31  2017-02-02  A
1           20          2017-03-29  2017-03-31  A
1           20          2017-04-19  2017-04-21  A
1           20          2017-06-22  2017-06-23  A
1           20          2017-06-29  2017-06-30  A
1           20          2017-10-03  2017-10-06  A
1           30          2017-12-09  2018-07-26  P
1           20          2018-02-12  2018-03-02  A
1           20          2018-07-19  2018-07-20  A
1           30          2018-07-27  2018-07-27  P
1           30          2018-07-28  9999-99-99  P
1           20          2018-09-04  2018-09-05  A
1           20          2018-11-12  2018-11-13  A
1           20          2018-11-19  2018-11-20  A

As you can see, there are even multiple entries for the same position where the dates are contiguous. These need to be combined into a contiguous entry until they are intersected/overridden by Acting Positions. I've been scouring different sources on the internet for the past month and haven't even come close to a viable solution. The closest I've come is from SQL – Combining Overlapping Date Rows, which I've converted from Oracle but I don't have enough knowledge of T-SQL to take it all the way to what I need, original Oracle code below:

SELECT
  KEY,
  MIN(TD) MINTD,
  MAX(TD) MAXTD
FROM
  (SELECT
    KEY,
    TD,
    SUM(C) OVER (PARTITION BY KEY ORDER BY TD) SC
  FROM
    (SELECT
      KEY,
      D,
      TD,
      TPD,
      DECODE(SIGN(LTD-TPD),1,1,0) C
    FROM
      (SELECT
        KEY,
        D,
        TRUNC(D,'MM') TD,
        ADD_MONTHS(TRUNC(D,'MM'),-1) LTD,
        LAG(D,1) OVER (PARTITION BY KEY ORDER BY D) PD,
        LAG(TRUNC(D,'MM'),1) OVER (PARTITION BY KEY ORDER BY D) TPD
      FROM
        T1)))
GROUP BY
  KEY,
  SC
ORDER BY
  KEY,
  SC;

Any ideas on a SELECT query to group and intersect the dates as outlined above?


texts:
For a small startup that doesn't have much expected traction in the very beginning. We are currently working with only one postgres database without replications and at risk of failures. I've done some research and realized that the primary methods everyone uses nowadays are either master-master or master-slave replications. My question is in general for small guys like us, what would be the urgency and need to have these replications in place for the additional cost of servers? Would we be able to survive on a single server? As well, would master-master or master-slave suit us better?

Thanks!


texts:
I'm working on a school project and I need some help.

I want to create three tables which should contain these fields:

User
UserID (AI, PK),
UserEmail,
UserPassword

WorkGroupMember
GroupID(PK, FK WorkGroup)
UserID (PK, FK User)

WorkGroup
GroupID (AI, PK),
GroupFacilitator,
GroupName

The question is, GroupFacilitator in WorkGroup is actually UserID, can or should it be a foreign key?

Thank You


texts:
I used to have SQL Server 2005 installed on my server. In my maintenance plan, the backup name format was like this: mydatabase_backup_yyyymmddhhss.bak.

Now I am using SQL Server 2012 but I am getting a totally different backup name format. I would like to have the same format name as 2005.

How can I do this?


texts: SQL Server Import Job Removed Data After Job Failed

texts:
I have a SQL Server Job set up to import data from a table to a table on another server, using the Import/Export data wizard.

In one instance, the job failed (with, I believe a SQL connection issue), and with that, the data from both tables were deleted. The tables on both the source and destination server were removed.

Does this make sense? If anything, why would the data be removed from the source?

The source is SQL Server 2012 and the destination is SQL Server 2008.


texts:
Is there any way of seeing details of sessions from the past (I fully expect the answer to be "no"). I am trying to find out what server a certain application is running on, that application accessing a certain database. I know the application runs at some point on a Friday evening so I was hoping that SQL server maintains a session history so I could just look up sp_who-like details for the hours in question.

Like I say, I don't expect this to be possible but right now I am absolutely clutching at straws.

Thanks in advance.

EDIT: I found the server in question by other means in the end; the job executed a SQL statement to create a file which was then FTPd to another site - that site has FTP logging that could give me the name and IP address of the source server. Thanks for the answers - I only wish I could accept them both as they both make very good points and give very good suggestions.


texts: How to disable Read-Only routing in SQL Server

texts:
I know I can just change the connection string to remove the applicationintent property, but how do I remove the configuration from within SQL Server?

For example, if we query sys.availability_read_only_routing_lists, how do I get an empty result?


texts: How To Prevent Live Servers From Detecting a Forced Failover?

texts:
We are using an AlwaysOn Availability Group in SQL 2017 on Windows Server 2016 which consists of two servers on our primary site and one DR server on a remote site.

We recently did an internal IT DR test as proof-of-concept during which the rest of the company continued using the primary site. We isolated our remote DR network to simulate a catastrophic failure of our main site and tested the DR site to make sure we could bring it up and use it successfully.  The test went very well up to this point, but we had a major issue when setting things back to normal.

Because the main site was still in use during the test, my main concern was to ensure that the live database would not be corrupted by the DR database which had also been used during the test, so I dropped the DR database before we opened the VPN and I expected the main site to start automatic seeding back to the DR site.  But, because I had to force failover on the DR site, as soon as the network link was re-established, the primary site detected that the DR server had been started with the Force Quorum switch ( /fq) and it basically shut down the two live servers.  I could not force failover back to the Live servers and had to rebuild the cluster.

Question: How do I prevent the Live servers from detecting that a forced failover was performed on the DR site?

I did consider shutting down the DR server before opening the network link, but am still not sure what would happen when it’s brought online.  In any case, the remote database would need reseeding from Live.


texts: ANSI/ISO plans for LIMIT standardization?

texts:
Are there currently plans to standardize one best way of limiting the number of results returned by a query?

The stack overflow question at Is there an ANSI SQL alternative to the MYSQL LIMIT keyword? lists the various ways to handle this behavior in different languages:

DB2 -- select * from table fetch first 10 rows only
Informix -- select first 10 * from table
Microsoft SQL Server and Access -- select top 10 * from table
MySQL and PostgreSQL -- select * from table limit 10
Oracle -- select * from (select * from table) where rownum <= 10

I don't play in DBs that often so I'm speaking from ignorance here, but it seems like this is a pretty important functionality - at least important enough that I scratch my head when I see it has been left up to vendors.


texts: How do I pass organized subsets of data to java-script efficiently?

texts:
Disclaimer: I'm a newb to sql, And This is a more general question than I would prefer to ask.
So I have a table with a list of English words and a column labeled votes with a bigint value representing the frequency of that word's use. I'm just learning now how to get sql to talk with my node production environment. I am also taking a crash course in standardized query language itself. So far, I'm not seeing an analogue to the javascript data structures and iteration functions I'm used to, So how would one efficiently:
A) (while alphabetized) isolate 1/128th of the records at a time
B) assign the top 8 most voted words of each isolated 'chunk' (still in alphabetical order) to javascript vars with iterated numbers in the name (or an array)
C) assign 9th through 68th in terms of voting, also still in alphabetical order, but assigned to indexes after the first 8.
Also, the 1/128 grouping also has to have an iterate in it's naming scheme, applied either to the var name before the other iterate, or in the name of the index that contains the sorted word strings.
Also also, I forgot to mention that I am using MySQL with MySQL workbench.
I'm asking for any guidance, I could probably be at least sent to some resources that are more targeted to my needs. To some extent, I don't know even what to search, I don't know what best practices are, like if a few thousand variables is better or worse than 128 arrays for performance, etc...
addl info: assuming I don't know anything and my approach is wrong, my end goal is to push to my javascript something like:
Var Content00101 = a (first alphabetical group, first alphabetical among most popular)
Var content 00302 = by (third alphabetical group, second alphabetical among most popular)
Any efficient way to do this is good by me


texts: I want to add a new node to galera without affecting existing node (donor). For example: node 1 is receive inserted data, and then node 2 join to cluster, node 1 is stuck and can't receive inserted data. How could I let node 1 receive inserted data continuously? Thanks!


texts:
Can you help correct the syntax for this code?

INNER JOIN Users u
ON case when c.sender = ? then c.reciever = u.id else c.sender = u.id

How do I do I put a CASE statement in a JOIN clause?


texts: Strategies for "checking out" records for processing

texts:
I'm not sure if there is a named pattern for this, or if there isn't because it's a terrible idea. But I need my service to operate in an active/active load balanced environment. This is the applicaiton server only. The database will be on a separate server. I have a service that will need to run through a process for each record in a table. This process can take a minute or two, and will repeat every n minutes (configurable, usually 15 minutes).

With a table of 1000 records that needs this processing, and two services running against this same data set, I would like to have each service "check out" a record to process. I need to make sure that only one service/thread is processing each record at a time.

I have colleagues that have used a "lock table" in the past. Where a record is written to this table to logically lock the record in the other table (that other table is pretty static btw, and with a very occassional new record added), and then deleted to release the lock.

I'm wondering if it wouldn't be better for the new table have a column that indicates when it was locked, and that it is currently locked, instead of inserting an deleting constantly.

Does anyone have an tips for this kind of thing? Is there an established pattern for long(ish) term logical locking? Any tips for how to ensure only one service grabs the lock at a time? (My colleague uses TABLOCKX to lock the entire table.)


texts: Large INSERT INTO SELECT [..] FROM gradually gets slower

texts:
I've written a program that does a large INSERT in batches of 100,000 and shows its progress.

The source table contains 2.5GB of data:

CREATE TABLE wikt.text (
    old_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    old_text MEDIUMBLOB NOT NULL,
    old_flags TINYBLOB NOT NULL,

    PRIMARY KEY (old_id),
    KEY old_id (old_id)

)   ENGINE=INNODB
    AUTO_INCREMENT=23565544
    DEFAULT CHARSET=binary;

CREATE INDEX old_id ON text (old_id);

This is the destination table:

CREATE TABLE domains.dictionary_language (
    text_id     INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    english     TINYINT(1) UNSIGNED NOT NULL,

    PRIMARY KEY (text_id),
    KEY         english (english)

)   ENGINE=INNODB
    AUTO_INCREMENT=23565544;

This is the query being run in batches of 100k:

INSERT INTO domains.dictionary_language
    SELECT      old_id,
                IF(old_text LIKE '%==English==%', 1, 0)

    FROM        wikt.text

    LIMIT       {batch}, 100000;

The query is getting slower and slower. The first 1 million records inserted in 8 minutes. After that, records #1.2m - #1.3m alone took 7 mins. Now #2.3m - #2.4m just finished in 15 mins.

Here's the log of how long each batch of 100k takes to import.

As you can see, the first 12 batches (1.2 million records) insert in < 1 minute each. After that, the performance drops, with each batch taking a bit longer than the last!

24/11/2013 19:18:40 Ready

24/11/2013 19:18:42 Dictionary import started from Wiktionary
24/11/2013 19:18:42 Records:    3,729,613
24/11/2013 19:18:42 Batches of: 100,000

24/11/2013 19:19:11 Batch 1 finished in 00:00:29.3146767
24/11/2013 19:19:33 Batch 2 finished in 00:00:22.2142706
24/11/2013 19:19:41 Batch 3 finished in 00:00:07.6104353
24/11/2013 19:19:53 Batch 4 finished in 00:00:12.7057267
24/11/2013 19:20:08 Batch 5 finished in 00:00:14.9248537
24/11/2013 19:20:25 Batch 6 finished in 00:00:16.9849715
24/11/2013 19:20:43 Batch 7 finished in 00:00:17.7930177
24/11/2013 19:20:49 Batch 8 finished in 00:00:06.2453572
24/11/2013 19:21:07 Batch 9 finished in 00:00:17.2549869
24/11/2013 19:21:38 Batch 10 finished in 00:00:31.4577993
24/11/2013 19:22:02 Batch 11 finished in 00:00:23.7003556
24/11/2013 19:22:17 Batch 12 finished in 00:00:15.4078813
24/11/2013 19:23:40 Batch 13 finished in 00:01:22.9637452
24/11/2013 19:25:25 Batch 14 finished in 00:01:44.8639979
24/11/2013 19:27:40 Batch 15 finished in 00:02:15.1387295
24/11/2013 19:30:07 Batch 16 finished in 00:02:26.7553939
24/11/2013 19:33:01 Batch 17 finished in 00:02:54.3109701
24/11/2013 19:36:17 Batch 18 finished in 00:03:15.8252006
24/11/2013 19:39:57 Batch 19 finished in 00:03:40.1275906
24/11/2013 19:44:28 Batch 20 finished in 00:04:30.3824650
24/11/2013 19:49:48 Batch 21 finished in 00:05:20.6873423
24/11/2013 19:55:45 Batch 22 finished in 00:05:56.7674059
24/11/2013 20:02:37 Batch 23 finished in 00:06:52.0925703
24/11/2013 20:10:32 Batch 24 finished in 00:07:54.8921622
24/11/2013 20:18:12 Batch 25 finished in 00:07:39.9433072
24/11/2013 20:26:34 Batch 26 finished in 00:08:21.4696824
24/11/2013 20:36:00 Batch 27 finished in 00:09:26.3163915
24/11/2013 20:45:07 Batch 28 finished in 00:09:07.1472950
24/11/2013 20:54:48 Batch 29 finished in 00:09:41.0222326
24/11/2013 21:04:19 Batch 30 finished in 00:09:31.2316726
24/11/2013 21:14:35 Batch 31 finished in 00:10:15.3521962
24/11/2013 21:25:10 Batch 32 finished in 00:10:34.9583176
24/11/2013 21:36:27 Batch 33 finished in 00:11:17.6047568
24/11/2013 21:47:52 Batch 34 finished in 00:11:24.3261412
24/11/2013 21:59:32 Batch 35 finished in 00:11:40.2410515
24/11/2013 22:12:31 Batch 36 finished in 00:12:59.1605654
24/11/2013 22:26:12 Batch 37 finished in 00:13:40.9209540
24/11/2013 22:40:12 Batch 38 finished in 00:13:59.8160347

24/11/2013 22:40:12 Dictionary import finished

It's getting slower and slower with each batch of 100k! Why? Here's the EXPLAIN output.

The server itself is tuned up with a 4GB buffer pool etc.


texts: SELECT * FROM INFORMATION SCHEMA on V5R3M0

texts:
I have access to a client's V7R1 AS/400 and I can run queries like SELECT NAME FROM INFORMATION_SCEMA.TABLES and

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    TYPE_NAME,
    COLUMN_SIZE,
    COLUMN_TEXT
FROM "SYSIBM"."SQLCOLUMNS"
WHERE
    TABLE_SCHEM = 'MYLIB'
    AND TABLE_NAME = 'SOMEPF'

Just fine on that. I am QSECOFR on that system.

However, on a free greenscreen account on PUB1.DE running V5R3M0, I cannot run that command from either STRSQL or from the db2 command inside of QSH. I tried the alternative format of INFORMATION_SCHEMA/TABLES I've seen around as well. I get the following errors from STRSQL:

> SELECT name FROM INFORMATION_SCHEMA/TABLES
  TABLES in QSYS2 type *FILE not found.
> SELECT name FROM INFORMATION_SCHEMA.TABLES
  Qualified object name TABLES not valid.

Is this a permissions problem, a syntax difference between V5 and V7 or something else?


texts: generate unique primary key for all clients from the local database and NOT the server

texts:
I'm using MySQL Innodb database to work as a distributed database among many places and also each place will work offline but at the end all the data will be centralized in one server,
I want to generate a unique primary key for registration table that will be unique for all clients, first I thought about int auto_increment but then I realized that this will cause conflict between each client as all of them will generate a new auto_increment from their local database and not from server so in this case all will start from 1 and increment for each new register
now I'm thinking of using a CHAR or VARCHAR to put a prefix for each registration in client's local database, or is there another way to do so?


texts: Why can't start pgbouncer process when setting logfile to /var/log/ path?

texts:
This pgbouncer.ini setting can't start pgbouncer:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
admin_users = someuser

No pid file generated, no log file created. No process running(ps aux | grep pgbouncer).

But if set logfile and pidfile to:

logfile = pgbouncer.log
pidfile = pgbouncer.pid

pgbouncer can be started by run pgbouncer -d pgbouncer.ini. It will create log file and pid file at the same path of pgbouncer.ini.

I am using normal user doing it.


texts: What's the fastest way to execute a MySQL Dump of a large Database?

texts:
I currently have a rather large database that I create dumps from the our websites Live Server to my Test Server (and vice versa).

Creating the dump is fairly quick (takes about 30 seconds). However, executing the dump file on my Test Server (or Live Server) in order to setup a copy of the dumped database takes AGES (roughly 2-4 hours).

Is there anyway to make this faster? I currently generate the MySQL Database Dumps using Navicat, and then execute them again using Navicat.


texts: Multiple vs Single COUNT on same col + Multiple vs Single col index

texts:
I have two very simple questions
1. Multiple vs Single COUNT on same col
I need to perform CASE on an aggregate column like this:
SELECT tbl_students.school, COUNT(tbl_students.id) as students, (
  CASE
     WHEN students > 1000 THEN 'SMALL'
     WHEN students > 10000 THEN 'MEDIUM'
     WHEN students > 100000 THEN 'LARGE'
  END
) as size
FROM tbl_students
GROUP BY tbl_students.school

But MySQL does not allow using the column alias within the SELECT part. So instead of above, I'll have to write:
SELECT tbl_students.school, COUNT(tbl_students.id) as students, (
  CASE
     WHEN COUNT(tbl_students.id) > 1000 THEN 'SMALL'
     WHEN COUNT(tbl_students.id) > 10000 THEN 'MEDIUM'
     WHEN COUNT(tbl_students.id) > 100000 THEN 'LARGE'
  END
) as size
FROM tbl_students
GROUP BY tbl_students.school

My question is "will using COUNT multiple times on the same column actually performs COUNT multiple times? If yes, will it also result in performance issues when there are millions of records?".
2. Multiple vs Single col index
If a table has following three columns:
tbl_students(id, username, school, firstname, lastname)
And has composite unique key for username and school. My question is, do I need to create a separate index for school column if I am searching only in school column?


texts: Possibilities to speed up InnoDB INSERTs and UPDATEs

texts:
My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower.

I'm monitoring when, and it turns out it's usually when a bot is hitting my site (googlebot for instance always gets the same session assigned, so each hit will require an UDPATE on the session table).

I presume google hits me so hard that mysql can't keep up with updating the same record. Usually an update takes a few mills, but I've seen it reach a full second. My table btw has never reached more than 7000 records.

Knowing that I have only 1 disk at my disposal, but still some free memory to spare, I was wondering what would be the best possibilities to enhance the UPDATE performance on this particular table (I'm not experiencing any issues on other tables as it's much less write intensive).


texts: Install mysql through deb file on ubuntu 20.04

texts:
Hi I've downloaded the file mysql-server_8.0.21-1ubuntu20.04_amd64.deb-bundle.tar from mysql site.
I've extracted it to see below list of deb files
libmysqlclient21_8.0.21-1ubuntu20.04_amd64.deb
libmysqlclient-dev_8.0.21-1ubuntu20.04_amd64.deb
mysql-client_8.0.21-1ubuntu20.04_amd64.deb
mysql-common_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-client_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-client-core_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-server_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-server-core_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-server-debug_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-test_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-test-debug_8.0.21-1ubuntu20.04_amd64.deb
mysql-server_8.0.21-1ubuntu20.04_amd64.deb
mysql-testsuite_8.0.21-1ubuntu20.04_amd64.deb

Using mysql to build a web project.
I want to understand

what each file is for
what order should be followed to install them
what files can be skipped
any disadvantage of installing through deb file (coming from windows background)
is this server edition suitable in prod env as well (short answer is ok)

I've already installed mysql workbench through a deb file
PS:
I found the dependency among most of these files that helped me install them in order as below:
i had to install libaio1 libmecab2 first
sudo dpkg-preconfigure mysql-community-server_*.deb
    mysql-community-client-core
    mysql-community-client
    mysql-client
    mysql-community-server-core
    mysql-community-server
    mysql-server

this link was helpful
https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/linux-installation-debian.html


texts: Sensible data management tool for importing and exporting of large data files

texts:
I am looking for some sensible suggestions on data management tools for large data files.

I have been experimenting aith excel and access only to find that access has limitations on import files size which caught me off guard.

I've been looking at dbase and some other out of the box solutions, but just hoping to find something that best fits my needs.

My typical data processing procedure is as follows:

Import large .txt files in common, pipe or other common delimited formats.
Run queries on the data once imported such as merging cells, and
Export my final queries into MySQL format...

The tables I am dealing with are millions of records large and as I said Access can no longer handle them..

Thanks for any ideas or suggestions on the best software for the job.


texts:
Using MongoDB in a live server. Everyday in the morning, I find that mongod process is shutdown. The mongodb log is as follows:

2017-11-02T11:19:49.173+0000 I FTDC     [initandlisten] Initializing full-time diagnostic data capture with directory '/var/lib/mongodb/diagnostic.data'
2017-11-02T11:19:49.174+0000 I NETWORK  [thread1] waiting for connections on port 27017
2017-11-02T11:19:49.469+0000 I NETWORK  [thread1] connection accepted from 127.0.0.1:34826 #1 (1 connection now open)
2017-11-02T11:20:20.112+0000 I NETWORK  [thread1] connection accepted from 127.0.0.1:34828 #2 (2 connections now open)
2017-11-02T11:27:34.728+0000 I CONTROL  [signalProcessingThread] got signal 15 (Terminated), will terminate after current cmd ends
2017-11-02T11:27:34.728+0000 I NETWORK  [signalProcessingThread] shutdown: going to close listening sockets...
2017-11-02T11:27:34.728+0000 I NETWORK  [signalProcessingThread] closing listening socket: 7
2017-11-02T11:27:34.728+0000 I NETWORK  [signalProcessingThread] closing listening socket: 8
2017-11-02T11:27:34.728+0000 I NETWORK  [signalProcessingThread] removing socket file: /tmp/mongodb-27017.sock
2017-11-02T11:27:34.728+0000 I NETWORK  [signalProcessingThread] shutdown: going to flush diaglog...
2017-11-02T11:27:34.728+0000 I FTDC     [signalProcessingThread] Shutting down full-time diagnostic data capture
2017-11-02T11:27:34.730+0000 I STORAGE  [signalProcessingThread] WiredTigerKVEngine shutting down
2017-11-02T11:27:34.795+0000 I STORAGE  [signalProcessingThread] shutdown: removing fs lock...
2017-11-02T11:27:34.795+0000 I CONTROL  [signalProcessingThread] now exiting
2017-11-02T11:27:34.795+0000 I CONTROL  [initandlisten] shutting down with code:0

My mongodb configuration file is as follows:

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# Where and how to store data.
storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1

#processManagement:

#security:

#operationProfiling:

#replication:

#sharding:

## Enterprise-Only Options:

#auditLog:

#snmp:

Most of the solutions online are related to setting processManagement.fork to false, which I believe is it's default value.


texts: Table data transfer using SSIS and triggers on destination table

texts:
I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using.

I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do that. I've few triggers on destination table. I'm now confused whether these triggers will be fired or not when I transfer the data from source table to destination table.

If these triggers won't be fired while transferring data from source to destination table, is there anything can be done to fire these triggers during this data transfer?

Giving me some information on the above will be greatly appreciated.

Update:
Selected rows will be added to the destination table. Triggers will update data in some other table in the destination server based on newly inserted data. For example:

    CREATE TRIGGER [MyTableChange]
       ON  [dbo].[DestinationTable]
       AFTER INSERT,UPDATE
    AS
    BEGIN

    declare @newid as int
    select @newid=newid  FROM INSERTED

     Declare @total as int
     declare @lookatme as int
    select @total=total, @lookatme from DestinationTable where newid=@newid
    update anothertable set total=total+@total where lookwhere=@lookatme
    END

GO

Above trigger is kind of example not exactly same logic which I've in original trigger. But it gives info of what exactly I'm trying to do. I'll be accessing newly inserted row details to update another table value. Please do not take this as correct trigger in terms of syntax and way it is written. I just tried to give essence of original trigger.


texts:
CREATE TABLE relations (

  object_id BIGINT(20) NOT NULL PRIMARY KEY,
  object_term BIGINT(20) NOT NULL,

  UNIQUE KEY link(object_id, object_term),

  CONSTRAINT fk_term FOREIGN KEY(object_term)
    REFERENCES terms(id) ON DELETE CASCADE ON UPDATE RESTRICT,

  CONSTRAINT fk_object FOREIGN KEY(object_id)
    REFERENCES objects(id) ON DELETE CASCADE ON UPDATE RESTRICT

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I have a feeling that I'm indexing object_id3 times, and object_term two times. Am I right?

If yes, how can I fix that and still keep the constraints and force (object_id, object_term) to be unique ?

ok, second try:

CREATE TABLE relations (

  object_id BIGINT(20) NOT NULL,
  object_term BIGINT(20) NOT NULL,

  PRIMARY KEY link(object_id, object_term),

  CONSTRAINT fk_term FOREIGN KEY(object_term)
    REFERENCES terms(id) ON DELETE CASCADE ON UPDATE RESTRICT,

  CONSTRAINT fk_object FOREIGN KEY(object_id)
    REFERENCES objects(id) ON DELETE CASCADE ON UPDATE RESTRICT

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

is this better?


texts: How connect by is really working? How is it multiplying rows?

texts:
I know (on high abstract level) how hierarchies in Oracle works, but this one I do not understand.
When issued following query:

select rownum-1 as num from dual

I've got one row as result.
The following query:

select rownum-1 as num from dual connect by level<=1000

is returning 1000 rows.
What is really happening in background of this query? How Oracle engine is multiplying rows when tries to resolve hierarchy.


texts: Django Multi Table Inheritance and Preserving Child and Child History

texts:
Place can have many different one to one fields, that’s why it can have bookstore, restaurant, and hardware store. But can the same place be both a bookstore and a restaurant at the same time? Now if the bank and restaurant tables don't have the same pk as place, I would think the answer is yes. But I also know that unless you put parent_link=True on it, erasing the child row automatically deletes the parent row.

My use case is to preserve the history of Restaurant despite it’s having now become a Bookstore, and if a Restaurant is simultaneously a Bookstore, to be able to keep both places in my db.   Is the best way to do this with a fk to each of the bookstores, restaurants and hardware stores instead of either a OneToOne or multi table inheritance? Or is there some other way I’m not aware of? This has to be a solved problem, but so far I haven't found it. I'm currently looking at NFL databases because players can be on more than one team, (albeit not at the same time), and they have a recorded history of their team and individual stats - to see if I can hack those into what I want.

I'm even willing to consider an ArrayField or Hstore. I'm on Postgres 9.4 and trying to maintain 3NF. All wisdom accepted. Thanks.


texts: Slow storage writes: how many milliseconds exactly? SQL Server 2008 R2

texts:
We're running SQL Server 2008 R2.

I'm using SPBlitz and it's saying that I have slow storage writes on Drive H. Blitz defines these as "averaging over 20ms."

But of course, someone asked "How much over?"

Is there a query I can use to determine what the average storage writes is in milliseconds?

Thanks,
mh


texts: Need Help Designing Tennis Ladder Database

texts:
I'm creating a tennis ladder website using PHP/MySQL/CSS & HTML.  A tennis ladder is basically a system where you challenge a player and if you beat them you go one spot above them on the ladder rankings list.  Originally I designed the website so that it was a single ladder that users could register to join and then participate in.  That was up and running fairly well (in tests), but now I've decided I want to further expand it to allow multiple ladders.  Here's what I'm trying to accomplish:

A user can join many different ladders.
A ladder can have many different users.
Each ladder has a type, name, and description.
A ladder match will keep record of who won, who lost, and the score and date played of the match.
Stats will be collected for each player within that SPECIFIC ladder.

Here's what the tables for the ladder DB look like:

Here's a rudimentary visual representation of what some test data would look like when displayed.  This will change to represent a select ladder's data.

This is the first real web app I've attempted, so any advice on schema structure would be appreciated.  Originally when I had conceived of it as just 1 ladder that everyone could join I had combined the User table fields with the UserLadderStats fields, and there were no Ladder or Ladder_Users tables.  LadderMatch table has remained almost the same, but it now has foreign key references.


texts: Adding a bool column; tracking untouched vs. false

texts:
[backstory] I have a simple database of our current Widget inventory. It averages just dozen columns across five or six tables, but has a huge number of records already.
Some Widgets came with caps, some did not, but we've actually never kept track before. Now management wants users to be able to keep track of whether each individual Widget arrives with a cap or not. We will not be doing an inventory to check our current stock, but when I add the new column for "Arrived Capped," there will be no data on everything received before today. What is the usual, best-practices approach to scenarios like this?

When adding a string column, the meaning of "" is obvious; no data has been entered. But I'm adding a bool, thus existing records will default to a value that does indicate something: FALSE.
My first thought was to make the user portal have, rather than a checkbox, a radio button pair for this question. Upon creating a new record, or even returning to old records with false-falses, if they click Yes or No, that value is recorded, plus another bool indicates this question was actually answered manually. That is; if the first bool is F, but the second bool is T, then the first bool is not a 'default false'.
Their hand-waving solution was to have records dated before today displayed as "unknown" (this was nixed as existing records could not then manually be given a user-verified value of false). Should I use a faux-bool int and think of it in trinary? Or, back to using a separate tracking bit/bool, should I make a new table for that sort of data? Is this a common concept? Any naming conventions?


texts: list of items or menu in mysql workbench

texts:
i am creating a database for an android tourism app.
i am working on MySQL workbench, i want to create a menu in my database,
for example i want to list the cities of my country, or list the branches of any place around the country.
i read about ENUM & SET datatypes here, and how they are evil to my database
http://dev.mysql.com/doc/refman/5.0/en/set.html

what do you all think, what datatype do i need to represent a menu in my database?


texts: Strategy to find the unique bivariate polynomials

texts:
I have table that stores Tutte polynomials of a large set of graphs. These are bivariate polynomials with integer coefficients. An example of a polynomial taken from Wikipedia is:

Since the polynomial may be "sparse", I've saved the data for each graph in an SQLITE table as:

CREATE TABLE IF NOT EXISTS tutte_polynomial(
    graph_id   INTEGER,
    x_degree   UNSIGNED INTEGER,
    y_degree   UNSIGNED INTEGER,
    coeff      INTEGER
);

What I'd like to do now is determine the number of unique polynomials over the graph_id. When my database was small, I used a python script to store each polynomial as a tuple in a dictionary - this solution runs out of memory for larger databases.


texts: no matching unique or primary key for this column-list

texts:
My parent table
Create table spare(
Sid int ,
Sname varchar(20),
Mid int,
primary key(Sid,Sname),
foreign key(Mid) references manu(Mid) on delete cascade

);

My child table
Create table Branch(
Bid int primary key,
Loc varchar(20),
Sid int,
Sname varchar(20),
foreign key(Sid) references spare(Sid),
foreign key(Sname) references spare(Sname)
);

I have made the references to the primary keys itseld but I'm still getting this error


texts: Exporting from SQL Server 2005 for import into ER Studio 9.0

texts:
I am new to ER Studio and need to create an ERD for a remote system. That system can only be accessed by LogMeIn remote control and by FTP. The remote system is running SQL Server 2005. The local system has ER Studio 9.0 installed.

How can I export a file from SQL that will allow me to create a diagram in ER Studio? I can see the import function, but I keep getting errors when I attempt to import a SQL file that I've created from the remote system.

I think I'm simply choosing the wrong options on export. Here's what I'm doing:
SQL Server 2005, Object Explorer
1. right-click on the database I want to diagram
2. Choose Tasks > Generate Scripts
3. Select the desired database, next
4. Leave the default options (I've tried a few things, none worked), next
5. Select Tables only (I don't need anything except the tables, their fields, keys, and relationships)
6. Select all tables (or 4 tables, it doesn't matter)
7. Choose "Script to file", enter a file name, next
8. Finish.

The output script, with a .sql extension, contains commands for creating the tables with all the correct fields. It's lovely.

In ER Studio, New Data Model dialog box
1. Select "Import Model From:" and choose SQL File
2. Click Import
3. Select the appropriate SQL file
4. Select target database platfrom (SQL Server 2005)
5. Don't tick any boxes about inferring keys or domains)
6. Hierarchical layout is the default. Cool with me.
7. Relational model
8. Leave "Use Physical Parser" unchecked. No idea what it does, but checking it doesn't help.
9. OK
10. Invalid token, Line 1, Offset 4.

If I manually delete the line that is giving ER Studio trouble, I still get an error. I googled the heck out of this and found nothing of use. Absolutely any feedback would be appreciated.


texts: Issues with 'plus symbol' output in PSQL on update query

texts:
Simple but frustrating issue... I'm trying to do an update command to correct a misspelling with this field:

grouper=> select * from master_list WHERE group ~ 'JustAdopted';
       group       | activity | usefulness
-------------------+----------+------------
 JustAdoptedAdvice+|          |
                   |          |
(1 row)

But postgres won't give me a straight answer on the field name...

grouper=> UPDATE master_list SET group = 'JustAdoptingAdvice' WHERE group = 'JustAdoptedAdvice';
UPDATE 0

grouper=> UPDATE master_list SET group = 'JustAdoptingAdvice' WHERE group = 'JustAdoptedAdvice+';
UPDATE 0

grouper=> select * from master_list WHERE group ~ '+';
ERROR:  invalid regular expression: quantifier operand invalid

What is going on with that field? Why is that plus symbol there and what does it represent?

And how do I reference it to change it, if the field won't update with or without the symbol being there?


texts: PostgreSQL procedural languages overhead (plpython / plsql / pllua...)

texts:
I'm trying to find information about PostgreSQL user defined functions in procedural languages performance for real time tasks.

How does they compare to builtin functions?
Is there any difference (in overhead) how Postgres call / manage plpython vs plpgsql vs pllua functions (I'm interested in the Postgres integration / context / data transfer side, not the VM itself)?
Is the context a big overhead? Can I use it for realtime data mapping (let's say 1000 queries/s))
Is there any benefit of writing user defined functions in plpgsql then other pg/language? On the documentation they enumerate advantages, but I think they apply to all postgresql procedural languages.

Related findings:

A speed of PL languages for atypical usage
PostgreSQL Function Language Performance: C vs PL/PGSQL


texts: Database Behavior while executing UPDATE and INSERTs on the same table

texts:
My gear:

Pentaho BI Server 8.0
AS400 V7

My scenario:

I have two processes that sometimes run at same time in the same table. This table has about 600M rows, so, an update can take long time to finish.

Because I can't predict the time when this ETL finishes updating the rows, I must insert new rows from other process that match the conditions of the update ETL that was running previously.

Until this post, I had to stop both processes and run them manually because they never finish (24h+).

In the simultaneous UPDATE/INSERT scenario, what is the expected behavior from SQL database?, the new incoming rows from the insert are gonna be part of the previous update?, is there a race condition where the update process can process the new inserted rows?, are both processes independent, so, the update process will take in count only the rows that were in the table before the insert?, are gonna get locks activated at row level here by default?


texts: Can I reorganize a Primary Key after deleting some rows?

texts:
In my project there is a table in which I add testing data for smoke tests, proceed with some tests, then delete the data I added. The Primary Key on that table is and not null int and has an auto incrementing identity, so after the deletion the highest number is left as it is.

My question is: is there a way to reorganize this number so it can return to the value before my smoke test inserts?

I tried to do Rebuild and Reorganize on the primary key hoping for a miracle but it didn't work.

Remark: I can't drop the Id column and create it again, as the existing data must have the same Ids after my tests.


texts:
I am trying to make an ER Schema for a person. The person will have Current Address and Permanent Address. So far I have it so that both Current and Permanent Address are Composite Attributes and they have the same Attributes.

If you look at the picture you see that it is pretty much a copy of the same attributes. How can I combine them/make this better?


texts: CONCURRENT SELECT's SLOW .... LOCK IN SHARE MODE

texts:
My Table has 25 Million rows total and 6 million rows satisfying below where clause.

Select 1:

select max(stamp) from Table where type = "string" LOCK IN SHARE MODE ;  6000+ seconds

Select 2:

select * from Table where type = "string" LOCK IN SHARE MODE ;  3000+ seconds

Select 3:

select * from Table where type = "string" LOCK IN SHARE MODE ;  2000+ seconds

If I try the SELECTS 2 and 3 without LOCK IN SHARE MODE WITH LIMIT, I am getting the data almost immediately.

All three queries have locked d table in shared mode. I don't understand why these are taking too much time when we use LOCK IN SHARE MODE.

Engine is Innodb,

Primary key is a composite key on multiple fields all are varchars

Create Table: CREATE TABLE `T1` (
`TYPE` varchar(25) NOT NULL,
`FIELD2` varchar(50) NOT NULL,
`FIELD3` varchar(130) NOT NULL,
`FIELD4` varchar(100) NOT NULL,
`FIELD5` varchar(30) DEFAULT NULL,
`FIELD6` varchar(20) DEFAULT NULL,
`FIELD7` varchar(110) NOT NULL,
.....
.....
.....
`STAMP` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE
CURRENT_TIMESTAMP(3),

PRIMARY KEY (`TYPE`,`FEILD2`,`FEILD3`,`FEILD4`,`FEILD5`,`FEILD6`,`FEILD7`),
KEY `COM_KEY` (`FIELD9`,`FIELD9`,`STAMP`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: How do I view a Microsoft SQL Server Compact Database

texts:
I have installed SQL Server 2012 Enterprise Edition.

I can use SQL Server Data Tools to add databases and tables and query tables etc.

Next, I used Visual Studio to try out a tutorial on creating a website with a database. The database type created was an SQL Server Compact Local Database.

This database doesn't turn up in the list of databases in SQL Server Data Tools. How do I view compact local databases? (I want to be able to view the data in the DB, add rows using a GUI, and run SQL queries within the tool.)

Or are they different products, in which case does Microsoft have an equivalent tool to SQL Server Data Tools for SQL Server Compact databases?


texts: Password mismatch while logging to sql server

texts:
Alright, I have a classic asp application and I have a connection string to try to connect to db.

MY connection string looks as follows:

 Provider=SQLOLEDB;Data Source=MYPC\MSSQLSERVER;Initial
 Catalog=mydb;database=mydb;User Id=me;Password=123

Now when I'm accessing db though front-en I get this error:

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'me'.

I looked in the sql profiler and I got this:

 Login failed for user 'me'.  Reason: Password did not match that
 for the login provided. [CLIENT: <named pipe>]
 Error: 18456, State:8.

What I've tried:

*checked 100 times that my password is actually correct.
*Tried this: alter login me with check_policy off (Do not even know why I did this)
*Enable ALL possible permissions for this account in SSMS.
* I've tried this connection string: Provider=SQLOLEDB;Data Source=MYPC\MSSQLSERVER;Initial Catalog=mydb;database=mydb; Integrated Security = SSPI

And I got this error:

Microsoft OLE DB Provider for SQL Server error '80004005' Cannot open database mydb requested by the login. The login failed.


texts: Database Size error to restore to SQL Server express 2014

texts:
I have a SQL Server 2008 R2 database, about 7 GB
I make a backup from SQL Server2008R2 and  tried to restore it to SQL Server2014 Express  getting an error:

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database

what steps should i follow to avoid that problem?


texts: 65G SQL Server database has 5.5G of data in tempdb when not in use

texts: I have a SQL Server instance with 1 production database (also ReportServer dbs) that is 65G.  tempdb is 5.5G (well, actually much bigger but 5.5G is in use) when server is not in use.  I believe this is not normal.  How do I diagnose what is causing this space to be filled and not released?


texts: Is it possible to record read access to a row?

texts:
A table has a creation timestamp column with DEFAULT and an update timestamp column with DEFAULT ... ON UPDATE .... Similar to the ON UPDATE|DELETE ... constraint, we're looking for a way to have a nullable timestamp column being set on first SELECT to a row - kind of like a (hypothetical) ON SELECT. I couldn't find anything in the create table docs so far.
The use case would be to record the first read access time (not creation/update) to a row, and persist this to a column of its table. Is this possible via DDL, or via some other means?


texts: Log Shipping: Bulk Remove Alerts of already deleted primary and secondary databases

texts:
I'm facing a rather strange issue with Transaction Log Shipping reports in SSMS which is showing multiple red alerts of databases that have been created and deleted while performing multiple Log Shipping scenario between two servers.
I've used the following post:Remove the log shipping configuration on the secondary server, when the primary is no longer available to help clear those alerts but nothing came out of this with success.   Sometimes i'm getting the following error:

  Msg 32011, Level 16, State 1, Procedure
  sp_delete_log_shipping_primary_database, Line 51 Primary Database
  MyFirstDB has active log shipping secondary database(s). Drop the
  secondary database(s) first

Which is not true as the database is already deleted on both primary and secondary server as well as three log shipping jobs.
I have more than forty orphaned databases in this state. this is saturating SQL server log with the below error for all aforementioned databases:

  Date     19/08/2017 08:30:00 Log     SQL Server (Archive #3 - 19/08/2017
  08:31:00)

  Source        spid53

  Message
  The log shipping secondary database SQLTEST.MyFirstDB has
  restore threshold of 30 minutes and is out of sync. No restore was
  performed for 449177 minutes. Restored latency is 2 minutes. Check agent

Can you help please, clear these entries from wherever system database/tables holds these records.?
I really appreciate the help!


texts: We have a requirement in our Postgres Database, We want to give specific table permissions to a particular set of user, we also have airflow in our environment which syncs tables, but sometimes any new columns are added to a table so we have to drop the table, due to which the specific table access for the user is gone. Access to a specific table is given through GRANT. Can you guys suggest us a way in which specific table access can be given, and will remain if the table is dropped and recreated?


texts:
I have this machine as slave. Below is the command I ran and stuck. I done the change on the config file and set the id=2 too. Anything else I am missing here?

slave start;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
mysql> change master to MASTER_HOST='192.168.1.15', MASTER_USER='replication1', MASTER_PASSWORD='*******',MASTER_LOG_FILE='mysql-bin.000001'; ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log ?

Below is the results of

show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 98
    Binlog_Do_DB: fms,sms
Binlog_Ignore_DB:
1 row in set (0.00 sec)

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.15
                  Master_User: replication1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 98
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 243
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: fms
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 98
              Relay_Log_Space: 399
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)


texts: Why is the OCR&Vote disk group servicing SCAN name

texts:
I have installed 11g R2 Grid Infrastructure followed by database software only installation. I have not created any database yet.

When I open asmca->diskgroup, I can see the diskgroup which I have created during grid installation. When I select that diskgroup->View Serviced Databases, what is the expected result? I am assuming it to be empty. But, in my case, I am seeing my Cluster name( SCAN name) as database name and used space is .26GB. Can somebody clarify on this. Thanks in advance.


texts: How to edit `postgresql.conf` with pgAdmin 4?

texts:
Same Question as this: How to edit postgresql.conf with pgAdmin 3? but updated for the completely new (total re-write) pgAdmin 4.

➥ How does one edit the configuration files with the pgAdmin 4 app?

postgresql.conf
pg_hba.conf

I added the adminpack extension as discussed here. And I restarted pgAdmin 4. Yet I cannot identify any menu item or other interface item for editing this files.


texts: I need help resolving a retrieval problem

texts:
I have the following tables. I need to update the download.refer to show the ip_lookup.id value where (download.ADDRESS >= ip_lookup.start_ip AND download.ADDRESS <= ip_lookup.end_ip) AND download.FILENAME is not NULL. The ip_lookup table has more than 5M records with both IPv4 and IPv6 ip's. I need the download.refer field filled for reporting what ip downloaded which files along with country, area, and city. Generating the report in real time with a join takes over 7 minutes. I guess I could add the country, area, and city fields to the download table and use a trigger to fill them when a record is added but that seems like a waste of space. Does anyone have any suggestions?

CREATE TABLE `download` (
  `ID` int(25) unsigned NOT NULL AUTOINCREMENT,
  `LOG_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ADDRESS` varbinary(16) NOT NULL,
  `FILENAME` varchar(60) CHARACTER SET utf8 COLLATE COLLATE=utf8_general_ci; DEFAULT NULL,
  `refer` int(25) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `ip_lookup` (
  `id` int(25) UNSIGNED NOT NULL AUTOINCREMENT
  `start_ip` varbinary(16) NOT NULL,
  `end_ip` varbinary(16) NOT NULL,
  `continent` varchar(2) COLLATE utf8_general_ci NOT NULL,
  `country` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `area` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `city` varchar(50) COLLATE utf8_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Thanks in advance

EDIT: This is the query that I used to generate the report that took 7 minutes:

select
  d.ADDRESS,
  i.country,
  i.area,
  i.city
from download as d
inner join ip_lookup as i on d.ADDRESS
where (inet6_aton(d.ADDRESS) >= inet6_aton(i.start_ip)  AND (inet6_aton(d.ADDRESS) <= inet6_aton(i.end_ip)) AND d.FILENAME is not null and d.country is null;


texts:
DECLARE @LINKEDSERVER VARCHAR(20)= 'LS_CMT';
        DECLARE @ODS_DBNAME VARCHAR(20)= '';
        SET @ODS_DBNAME = DB_NAME();
        DECLARE @OCS_DBNAME VARCHAR(20)= '';
        SET @OCS_DBNAME = 'CMT' + SUBSTRING(@ODS_DBNAME, CHARINDEX('_', @ODS_DBNAME), LEN(@ODS_DBNAME))
SELECT * FROM @ODS_DBNAME.[dbo].CLI_LOCATIONS


texts: SELECT on column value = X but if no rows available then any column value

texts:
In MSSQL 2016 enterprise edition I have a table column which can contain an int. I'd like to query for 1000 rows where this column equal X, but if no rows then column may contain any value.

Eg:

SELECT TOP 1000 COLS FROM TABLE WHERE  <COL = 4 OR IF NO ROWS THEN COL = ANY VALUE>

How do I write the logic within < and >


texts:
From Mysql documentation it is clearly said that Hash indexes  are used only for equality comparisons that use the = or <=> operators.

I have a small test table:

`CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1;`

And query: explain select * from test where test.name like 'sun%'
And it shows that it uses index:
And query: explain extended select * from test where test.name like 'sun%'

But it can not be according to documantion. Mysql version: 5.6.32-78.0.
Any ideas?


texts: Combining count queries into one table row

texts:
I'm banging my head against the wall because I'm sure this is super easy.

I have a few queries set up that get counts for a few related tables.  This is for an arcade game tracking system.  The queries look at two tables and returns counts for how many of each game is in inventory

(SELECT Machine.MName, Count(Machine.Mid) AS CountOfMID
FROM Machine
GROUP BY Machine.MName
ORDER BY Machine.MName;)

and another for how many games are out for rent, again it counts the number of each game (multiple copies) that are out for rent:

(SELECT Machine.MNAME, Count(Rental.Mid) AS CountOfMID1
FROM Machine LEFT JOIN Rental ON Machine.[MID] = Rental.[MID]
GROUP BY Machine.MNAME;)

My question is how do I make a report that has the each game listed and then out to the side have the appropriate counts pulled in?  Something similar to this is what I'm after:

CATEGORY --- NAME ------ INVENTORY ------ AVAILABLE ------ RENTED
Video........Frogger.........2...............1..............1.....
.............Metroid.........5...............1..............4....
Pinball......Simpsons........10..............8..............2....

I'm currently putting all of my bounded controls in the MName HEADER of my report for grouping and I just need this last piece to put these counts in the same column.  I have tried using the control source for a bounded control to be a sql query just like I had in the actually saved query and it just pops up with a "enter parameter" box meaning I worded it wrong or something.

I'm wondering if I need to put the data in the "details" section of the report to tie each row in to the tables in my queries.  Not sure how to do any of this and I've tried everything and googled all night.

Any help would be greatly appreciated.  Pictures below:


texts: How to set a mongodb node to return as the primary of a replication set?

texts:
There is this question here:

How to force a mongod to become primary in a replica set?

But it was not the answer I was looking for.

and there is the question below, which could be related but it is not the same.

How to force a delayed member to become primary in case of failure in mongodb

and this question which is nearly a duplicate, but deals with version 2.6.11 and mine is version 3.0 and 3.2.
Plus I don't want to use force : 1 unless I need to.

Force a Member to Become Primary in mongodb

I have been struggling to put in practice the majority described in the answer there, maybe with an example or clarification, therefore this question.

I had my replication working fine, 3 nodes, node1 is the primary.

when I run the following command

//-----------------------------
//check the status of the replication
//-----------------------------
rs.status()

I get this result:

{
    "set" : "Krishna",
    "date" : ISODate("2016-04-26T14:59:40.263Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 0,
            "name" : "jpb01275:37001",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 1022,
            "optime" : Timestamp(1461678841, 1),
            "optimeDate" : ISODate("2016-04-26T13:54:01Z"),
            "electionTime" : Timestamp(1461682464, 1),
            "electionDate" : ISODate("2016-04-26T14:54:24Z"),
            "configVersion" : 239068,
            "self" : true
        },
        {
            "_id" : 1,
            "name" : "jpb01275:37002",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 1022,
            "optime" : Timestamp(1461678841, 1),
            "optimeDate" : ISODate("2016-04-26T13:54:01Z"),
            "lastHeartbeat" : ISODate("2016-04-26T14:59:40.206Z"),
            "lastHeartbeatRecv" : ISODate("2016-04-26T14:59:40.179Z"),
            "pingMs" : 0,
            "configVersion" : 239068
        },
        {
            "_id" : 2,
            "name" : "jpb01275:37003",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 1022,
            "optime" : Timestamp(1461678841, 1),
            "optimeDate" : ISODate("2016-04-26T13:54:01Z"),
            "lastHeartbeat" : ISODate("2016-04-26T14:59:40.238Z"),
            "lastHeartbeatRecv" : ISODate("2016-04-26T14:59:39.593Z"),
            "pingMs" : 0,
            "configVersion" : 239068
        }
    ],
    "ok" : 1
}

what happened is that I had to stop node1 (jpb01275:37001) and the node2 became the primary.
When I started node1 again, I wanted it to become the primary again.

what I did is

logged into node2 and node3 run the following command on then, one after another:

rs.stepDown()

and the node1 came back as a primary.

I also tried the following on node1, but it did not work for me.

//-----------------------------
// reconfigure the replica set
// because I stopped this server and now it is secondary
// I want it back to primary
//-----------------------------

cfg = rs.conf()

printjson(cfg)

cfg.members = [cfg.members[0] , cfg.members[1] , cfg.members[2]]

rs.reconfig(cfg, {force : true})

what is the correct, safest way to bring back node1 and set it as a primary for this replication?