texts:
I have an MSSQLServer2000 database that seems to have been working just fine until today. The DB connects to a java application on a remote server that passes the database data (login credentials, various records in tables) to other system elements in this network. This java application needs to connect to the DB for the other systems to do their job.
As far as I can tell there should be no issues with connectivity between this java application and the DB. netstat -an shows an ESTABLISHED connection between both IPs on port 1433, I am able to authtenicate on the SQL server locally with the credentials provided to the java application. There are no apparent errors in the server event viewer logs, nor have there been any changes to the system I am aware of....and yet the application fails to connect.
The application needs the following information in its connection pool fields to connect to the DB: Instance (which I queried with SELECT @@servicename) Schema Owner (which i queried with SELECT schema_name, schema_owner FROM information_schema.schemata) and the login user and password (which I know to be accurate and can login in via SQL Enterprise Manager)

Is there anything else I should be looking out for?

are those the correct queries to collect the information I needed? it seems the Instance is MSSQLSERVER and the schema owner dbo.

I am very new and green to SQL, not a DBA or software guy by any stretch. I have not ruled out reinstalling the java app. I have checked that TCP/IP port 1433 is enabled (as well as Named Pipes in the SQL Server Network Utility), restarted SQL services, and for good measure rebooted both servers. and this java app remains unable to connect to the server.
edit: there was one thing I noticed in the SQL logs. a message about remote access set from 1 to 0? and I ran EXEC sp_configure 'remote access', 1; that didnt seem to have any effect on letting me get connected.
edit #2: 1) OS is MS Server 2000. 2) Yes I can. I have another serer running SQL 2008 on MS Server 2008 and I can connect remotely from that SQL Server to this one, using the same credentials I supply to the java application. 3) no recent security updates to my knowledge.
thanks for any input!


texts: Is there a way to rename database objects such as tables, stored-procedures, etc, during a restore of a .BAK file to a new database?


texts: Does MySQL have a limitation of one index per query?

texts:
MySQL supports spatial index. However, all the spatial index can do is find points in a box. It can't do fancy stuff like find the nearest 1000 points, etc.

I think, and I don't know where I went wrong, that simply finding points in a box is easy – just do constraints:

x < xmax and x > xmin and y < ymax and y > ymin

Yes those are 4 constraints that use at least 2 columns.
The result is that things get too slow when there are 1.6 million rows.

But why? The x and y are sorted. The complexity should be log(n). Yet queries can take 60 seconds.

Using a spatial index shortens it to 1 second with some subquery trick.

What am I missing? Why does using a bunch of constraints in a query take time?

Even without a fancy algorithm it should be easy to quickly get the points in the box. Yet, I feel that the complexity must be O(n) at least.


texts: Can a trigger catch updates done by a select for update + lob.write()?

texts:
It's maybe a stupid question, but I didn't find an explicit answer...

I want to catch any update on my oracle db, so I want to use triggers, but the triggers not been activated when using SELECT FOR UPDATE, then lob.write(...) and COMMIT. (I have tested it with c++ using occi)

Is there any way to catch it in an update (or any other) trigger?

I find it hard to believe that there is command that can't be monitored by any trigger...

If it's matter I'm using version 11g.

Thanks!

EDIT

Trigger for example:

CREATE OR REPLACE TRIGGER TR1
BEFORE DELETE OR INSERT OR UPDATE ON TEST_CLOB
FOR EACH ROW
BEGIN
IF DELETING THEN
  WRITE_LOG('LOG_DIR','deleting:'||:old.filename);
  goto endproc;
END IF;
IF INSERTING THEN
  WRITE_LOG('LOG_DIR','inserting:'||:new.filename);
  goto endproc;
END IF;
IF UPDATING THEN
  WRITE_LOG('LOG_DIR','updating:'||:old.filename||'-'||:new.filename);
  goto endproc;
END IF;
WRITE_LOG('LOG_DIR','other:'||:old.filename||'-'||:new.filename);
<<endproc>>
NULL;
END;

Now in my log I see the inserting and deleting, but I never see any update.


texts: How do I (safely) kill long running operations in MongoDB?

texts:
Occasionally operations get out of control in MongoDB and might end up running for hundreds of seconds, and impact performance until they are killed or completed.

When that happens, I know I have killOp() available to me but how do I kill only targeted long running operations without also killing (for example) the long running operations involved in replication (which can be dangerous)?


texts: Getting error message when opening report server home page ( found 163 but 162 needed)

texts:
I just uninstalled SQL Server reporting services native and installed back again from same media which we used 3 years ago. This version is free one (development use only). I configured the report server but am now getting following message.

  The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '163'. The expected version is '162'. (rsInvalidReportServerDatabase)

Here is the current version of SQL Server info.

Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Please help how to resolve this.


texts: viewing the population? (sql server full text search)

texts:
I'm thinking that when we are working on full text search, we might want to view how sql server FT engine breaks the data and indexes it. For my case the column being indexed are simple nvarchar strings with spaces (no punctuation).

I'd want to be able to take the "tokens" out. Any idea if this is possible?


texts: Describe Interim Table and Basic Concept behind it?

texts:
Hy Everyone...
Can anyone describe me what is interim table in sql Server 2008? what is the basic concept and structure behind it.
thanks in advance.


texts: Multiple schema or multiple databases for better performance

texts:
I need to improve the performance of our entriprise search application(.net) from the database point of view.
The app already has 4 schemas on a separate sql server 2008.

2 schemas for front-end operations ( the tables are small)
2 schemas for back-end indexing, processing etc. (the tables are very
huge here).

Front and back schemas does not share any foreign key relation between each other.
To be highly responsive during the day time, the backend operation run in night time only.

Now the problem is the database server is heavely loaded at night time and often we get timeout errors. I want to spread the backend operations through out the day to overcome this issue with no impact on the front end operations.
when i see the performance graphs, during daytime the server and the DB in it is very minimally used.

My knowledge and exp on DB concepts is limited.
Following are the options which i think of.

I might not get a approval for 2 sql server machines.
Front schemas in a separate DB on the same sql instance. Place the
front DB in separate drive.
Front schemas is separate filegroup. and place the filegroup in
saperate drive.

Please let me know the pros and cons of my options and any other options which you think suits here.
Thanks in advance.


texts: Choose the index for default sort in MySQL

texts:
I have a table in MySQL with 3 columns a, b, c. Each has an index but none is primary key. When I run query
SELECT * FROM table LIMIT 50;

I am trying to have the result sorted by column b ascending, however the result is sorted by column a ascending instead. I cannot make column b primary key because there are some duplicate values in column b. How do I do this without making column b primary key?


texts: How to find current setting of "FORCE PARALLEL QUERY" in Oracle

texts:
I can change (override) the current level of parallelism in an Oracle session using e.g.:

alter session force parallel query parallel 6;

Is there any system view of function that will return me the value 6 that I have requested?

I would like to check (in a stored procedure) if my current session has changed this (ideally the setting for a alter session force parallel dml as well)


texts: Where each partition has only one value in the constraint column, can I avoid filtering?

texts:
I have partitioned a table in postgres 9.6 by date.  Every date within a parition is identical.

When I select data, a relatively expensive Seq Scan filter operation happens against every row in the partition (see below). I know this is unecessary; is there a way I can point this out to the database?

prescribing@prescribing=# EXPLAIN SELECT * FROM rx WHERE rx.processing_date = '2018-11-01'
[more] - > ;
┌───────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                            QUERY PLAN                                             │
├───────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Append  (cost=0.00..230777.40 rows=9670673 width=62)                                              │
│   ->  Seq Scan on rx  (cost=0.00..0.00 rows=1 width=128)                    │
│         Filter: (processing_date = '2018-11-01'::date)                                            │
│   ->  Seq Scan on rx_201811 rx_1  (cost=0.00..230777.40 rows=9670672 width=62) │
│         Filter: (processing_date = '2018-11-01'::date)                                            │
└───────────────────────────────────────────────────────────────────────────────────────────────────┘

The tables are set up thus (with some extra columns omitted):

┌───────────────────┬───────────────────────┐
│      Column       │         Type          │
├───────────────────┼───────────────────────┤
│ id                │ integer               │
│ processing_date   │ date                  │
└───────────────────┴───────────────────────┘
Child tables: rx_201801, rx_201802, ...etc...

Table "rx_201801"

┌───────────────────┬───────────────────────┐
│      Column       │         Type          │
├───────────────────┼───────────────────────┤
│ id                │ integer               │
│ processing_date   │ date                  │
└───────────────────┴───────────────────────┘

Indexes:
    "cnstrt_rx_20101_pkey" PRIMARY KEY, btree (id)
    "idx_rx_201701_date" btree (processing_date)

Check constraints:
    "rx_201801_processing_date_check" CHECK (processing_date >= '2018-01-01'::date AND processing_date < '2018-02-01'::date)


texts: Merge Replication - Deleting from the subscriber database?

texts:
I have 2 SQL Server instances where I use merge replication on a database. A developer accidentally inserted data to both the subscriber and publisher at the same time.  Now I have data in both databases but with different rowguids.

Q: Can I delete those rows with different rowguids from the subscriber and the rows in the publisher remain untouched? If so, will the rows from the publisher replicate back to the subscriber but now with the same rowguid?


texts: How does an index speed up insertion in a table

texts:
How does index speed up insertion in a table?

I have an idea how it happens for retrieving the rows and selection of data, but how does it helps in insertion?

CREATE TABLE  persons(
id NUMBER ,
first_name VARCHAR2(50) NOT NULL,
PRIMARY KEY(id)
);

and if create an index on first_name

create index name_idx on persons(first_name)

how does it speed up the query below?

INSERT INTO suppliers (id,first_name) VALUES (01, 'SAI');
INSERT INTO suppliers (id,first_name) VALUES (02, 'CHRI');
INSERT INTO suppliers (id,first_name) VALUES (03, 'MASSEY');
INSERT INTO suppliers (id,first_name) VALUES (04, 'FREDDIE');
INSERT INTO suppliers (id,first_name) VALUES (05, 'ROGER');


texts: Access write conflicts with views and insteadof triggers?

texts:
I have a legacy linked table in a msaccess database that is linked to a sql-server table.

The linked table is being synced periodically to a different set of tables that have been split up for various reasons and have more columns than the users want to see. I'd like to get rid of the legacy table and the head aches of syncing it, by creating a view with instead of triggers to handle the data access. Let's say my view looks like:

Create view Compatibility_View
with schemabinding,view_metadata
as
select   a.Name,a.col_1 aliasedName,a.col_2 aliasedName2
        ,b.col_1 aliasNamed3 ,convert(float,b.col_2) aliasedName4
        ,a.modified_date,a.modified_by
from dbo.some a
join dbo.other b
  on a.some_Id = b.some_Id
where a.isActive = 1;

And I have instead of triggers that will soft delete the records and route the properties appropriately.

I also automatically update the modified_date and modified_by columns. (These use to be handled by after triggers on the legacy table).

Unfortunately, doing this second set of requirements causes access to issue a warning after each edit that data has been changed. Tracing the queries access issues it appears that it is doing an update to the view, making sure that all other columns haven't changed. e.g.

update "dbo"."Compatibility_View"
set "aliasedName"='v'
where "aliasedName2"=45 and "aliasedName3"='horse'
and "aliasedName4"=1.3e2 and "modified_date"='09/10/2014 12:35:00.00'
and "modified_by"='AD\SomeUser'

Since my trigger change the modified_date and modifed_by columns access views it as a descrepencies and sends out an ugly error prompt?

The users like seeing the modified_date and modified_by columns in access so removing this piece is not really an option. How can I get these columns in the view without access freaking out?


texts:
Hi I have just started reading about this replication concept and one thing I want to ask is that when we change something in the slave db how is it going to effect the master db? Is there a sync algo that will change it in master db as well?
and another thing is if there are multiple databases can we apply replication on them as well? like multiple databases in masters server replicated into multiple databases in slave server. Thanks


texts: Configure SQL Server Database for High Availability & Warehousing

texts:
I have 3 DB servers (DB1, DB2 & DB3 - all on Win 2008 R2 with SQL Standard 2008 R2 in the same datacenter). Let's assume AdventureWorks as an example database.

My goal:
- Setup DB mirroring with automatic failover i.e. DB2 becomes mirroring server
- Setup hourly transaction log backups for point in time recovery
- Setup reporting/warehouse environment i.e. DB3 becomes the warehouse/reporting server

Setting up DB mirroring and hourly txn log backups is easy but my question is - What are  my options for replicating the principal database to the DB3 server for reporting/warehousing needs? Can I use log shipping considering I do txn log backups?

The AdventureWorks database on DB3 needs to be in read-only mode.

EDIT: The reporting database can be up to a day behind.


texts: Why Can't I shrink log file in full recovery mode

texts:
I have a log file that is 302MB.

I have done a log backup which has left the log file mostly free (I can see this through the Disk Usage standard report)

If i try to run

DBCC SHRINKFILE (N'AdventureWorks2014_Log' , 0, TRUNCATEONLY)

or

DBCC SHRINKFILE (N'AdventureWorks2014_Log' , 0)

the file is still showing as 302MB.

I know I can change the database to simple recovery, then run one of the commands above and set back to full recovery (and then do a full backup to make sure the database is not in Pseudo-simple recovery mode)

However, why can I not shrink the file in full recovery mode?

I know shrink isn't something you should do, but in my real world database, since no one had ever backed up the transaction log it had grown to 30GB and now regular log backups have been put in place to prevent growth of this level


texts: How to create a partitioned table based on date?

texts:
I'm trying to create a partitioned table where partitioning is determined by OrderDate. I'm trying to create a clustered index on OrderDate and a nonclustered primary key on OrderID. But I get an error saying:

  Msg 1908, Level 16, State 1, Line 1 Column 'OrderDate' is partitioning
  column of the index 'PK_OrderID'. Partition columns for a unique index
  must be a subset of the index key. Msg 1750, Level 16, State 0, Line 1
  Could not create constraint. See previous errors.

How to modify this to get it work:

CREATE TABLE dbo.Orders
    (
      OrderID INT NOT NULL ,
      Name NVARCHAR(20) NULL ,
      OrderDate DATE NOT NULL ,
    )
ON  PartSchemeOrders(OrderDate)

CREATE CLUSTERED INDEX IX_OrderDate
 on dbo.Orders (OrderDate)
 on PartSchemeOrders(OrderDate)

ALTER TABLE dbo.Orders
    ADD CONSTRAINT PK_OrderID PRIMARY KEY NONCLUSTERED  (OrderID)


texts:
I have a financial system that needs to attach PDF receipts for each record saved on my system.

These receipts will be scanned by a proper device attached to the computer and saved in PDF to be stored in my database.

Today the system saves the PDF file as varbinary(max), but because of the number of rows in my table, the filesize of my DB is increasing too fast. The average file size is about 1 to 2 MB.

What is the best way to store these kind of files without compromising my database performance?


texts: Match similar special character in filters

texts:
I have to perform a search on a table with Turkish city names.

The users would enter the first 4 characters of a city and I need to fetch the results from SQL.

For example, user wants to find the entries for Istanbul, so he would enter ista or Ista or ISTA

Problem is that Istanbul is written with special character. It is Ìstanbùl. So my query

where UPPER(city) like 'UPPER(<entered value>)%'

will not find it. It only finds it when the user enters Ìsta  (with the special character).

Is there a way of converting similar special character to 1 character. So for example ìíî will be converted to i. This way a user can find Ìnstanbùl with whatever variant of i they enter.


texts: Percona XtraDB Cluster and MySQL Replication

texts:
I've setup a 3-node multi master replication with percona xtradb cluster, it works perfectly.

Now I've tried to add some read-only slaves setting up replication as usual but it seems the binlog doesn't include the new inserts,

I've set binlog_do_db on the master of a database, slave says the log position is the same as the master ones but the new data isn't there.

Is there a special way to do replication on xtradb cluster?


texts: Updating one table from another table where non-primary-key field is similar string

texts:
I have a database in Access that I am trying to fix (actually, I'm working on a copy of the original).

There are two tables, one with data input errors, and a second that is a copy of those errors, but fixed, if that makes sense.  However the second table only contains 3 out of 14-ish fields, and the primary key is not among them.

I would like to update the first table with the information from the second, searching for records to update based on similarity between the Title field in both.  The second has had some typos fixed throughout as well...

I know some SQL but not much.


texts: Nightly reload of tables from a linked server

texts:
I have a number of tables that need to be reloaded nightly from a linked server.  My current method of doing this is to create a nightly job to pull the data using something like the following:

TRUNCATE TABLE [TableA]
INSERT INTO dbo.[TableA]
    SELECT [Col1], [Col2], [Col3]
    FROM [linked].[server].[dbo].[TableA]

On some of these tables have millions of rows of data are taking hours to complete.  The job sometimes finishes after 8am which is causing performance issues for the applications using the database due to users logging in and querying the db.  Most of the tables have multiple indexes.

I'm curious if there is a better method to speed up the process. Furthermore, is it possible and would it be faster to not truncate the table and instead only pull over any new or changed data for each of the tables.


texts: Column store index on a large fact table

texts:
I have seen column store index and also tested it. I have no doubt admiring that it is lightning fast. But every tutorial and blogs I have considered, says about its limitation "table will become read only" and I won't be able to insert/update/delete data in a table. And at the same time it is recommended to use them in data warehousing projects where data is in huge amount and updated periodically. On based of that I have come up with the solution to

Disable index, do insert-update-delete and then rebuild the index

Will this be a right approach to deal with a large amount of data in fact table?

Will the cost (in terms of time) in creating/rebuilding index can be more than regular process?
Generally, data in fact table is queried until the cube is processed. I have a cube which works on views instead of table; so will it be cost efficient for me to keep a column store index on fact table?
If I have a fact table which gets updated every day. So, will it be a good practice to disable or rebuild indexes?
Do they behave similar to index in SQL Server? I mean, Select will be fast but update operation will take more time.


texts: db2 convert regular table to partitioned using online table move

texts:
I am converting my regular table to partitioned table using timestamp column. However, the below code fails with sql error 2103 reason code 6.
Please note that there are no referential(or foreign key constraints) on this table so wondering where I am going wrong. There is primary key and few indexes defined on TRANSACTION table. Kindly help !

I am not using any options as well for simplicity though I want to use ' KEEP, COPY_USE_LOAD,COPY_WITH_INDEXES' as option.

  CALL SYSPROC.ADMIN_MOVE_TABLE(
  'ADMIN',
  'TRANSACTION',
  '',
  '',
  '',
  '',
  '',
  '(TRANSACTIONDATE) (STARTING FROM (1/1/2010) ENDING AT (1/1/2017) EVERY 1 MONTH)',
  '',
  '',
  'MOVE'
  )


texts: How could we start Oracle database in Oracle Exadata which's celldisk has proactive failure?

texts:
I am trying to open exadata machine but it has error. That EXADATA  Oracle Sun Fire X4720 M3 SAS, Oracle 11gr2 RAC database installed. I was examining celldisks. When i gave command list griddisk I see that result.

cellcli> list griddisk
    DATA_DM01_00_mycelldisk01 proactive failure

We dropped celldisk, we can't recreated griddisk and celldisk, it has error. we can't replace it because we can't get any disk from anywhere. I need to open that exadata and i plan to open database without that celldisk. should i drop physicaldisk?

cellcli> list physicaldisk
    20:0 KERMXM warning - proactive failure, poor performance

when I give command srvctl start database -d mydb it gives error.

ORA-15040: diskgroup is incomplete
ORA-15066: offlining disk "0" in group DATA_DM01 may result in a data loss
ORA-15042: ASM disk "0" is missing from group number "1"

Could i delete disk "0" which is proactive failure and continue my system?
 Thanks


texts: Can sub-selects change in one single query in a read committed transaction?

texts:
I am querying the same table multiple times in a single query in a transaction with isolation level set to READ COMMITTED (which is the default in PostgreSQL). Examples are:

BEGIN;

WITH
first AS (SELECT * FROM table),
second AS (SELECT * FROM table)
SELECT ...;

END;

or

BEGIN;

SELECT * FROM table
UNION ALL
SELECT * FROM table;

END;

Is it possible that the first sub-SELECT yields other results than the second sub-SELECT? I understand that SELECT queries might yield different results when executed separately (due to the characteristics of a READ COMMITTED transaction), but I am wondering what might happen if they are embedded in the same query.

I am using PostgreSQL 9.5.


texts: Why is multiple independent index always better than concatenated index

texts:
Index Merge: Combining Multiple Indexes

  It is one of the most common question about indexing: is it better to
  have one index for each column or a single index for all columns of a
  where clause? The answer is very simple in most cases: one index with
  multiple columns is better—that is, a concatenated or compound index.
  “Concatenated Indexes” explains them in detail.

I can totally understand this assertion under this scenario:

TABLE( A,B,C )
Indexing 1: Index A, Index B, Index C
Indexing 2: Index (A,B,C)
Query: JOIN ON X.B=B AND X.C=C

because under this scenario, Indexing 2 cannot used at all ( I know about the exceptions )

but under this scenario:

TABLE( A,B,C )
Indexing 1: Index A, Index B, Index C
Indexing 2: Index (A,B,C)
Query: JOIN ON X.A=A X.B=B AND X.C=C

I think Indexing 2 is better, it only needs to traverse 1 index tree instead of 3 right ?


texts: postgresql - transaction log - versioning?

texts: Is it possible to "version" your database so that any change can be reviewed or roll backed? Obviously backups are important but being able to go back to any previous state of the database (at least say  with in the last 2 days) would be very helpful. I am not a database administrator but I am developing an application that needs some fault tolerance. If it is possible can you point me in the right direction for information? If is not possible what are my options?


texts: Updating column with multiple sub-queries

texts:
I have created the following query which is used to calculate a total from the results of a spatial query:

SELECT
  SUM(med_price)
FROM (SELECT
  ST_INTERSECTION(a.geom, (SELECT
    ST_SETSRID(geom, 4326)
  FROM catalogue_table
  WHERE id_number = '2211654')
  ) AS geom,
  small_price,
  med_price,
  large_price
FROM pricing_table AS a
WHERE ST_INTERSECTS(ST_SETSRID(a.geom, 4326), (SELECT
  ST_SETSRID(geom, 4326)
FROM catalogue_table
WHERE id_number = '2211654')
)) AS total_price

My tables look like this:

CREATE TABLE pricing_table
(
  gid serial NOT NULL,
  tile_id numeric,
  small_price numeric,
  med_price numeric,
  large_price numeric,
  geom geometry(MultiPolygon,4326)
)

And:

CREATE TABLE catalogue_table
(
  ogc_fid serial NOT NULL,
  geom geometry(Geometry,4326),
  id_number character varying,
  scale integer,
  scale_code character varying
)

I am trying to modify this query so that it calculates the total price for all rows, instead of just a single row with a specific id_number. However, whenever I try and modify my query to do this, I get:

ERROR: more than one row returned by a subquery used as an expression. SQL state: 21000

I would like to create a 'price' column on the catalogue table, and populate this with calculated prices, which, depending on the 'scale_code' from the catalogue_table (either small, medium or large) will calculate a total from the relevant column in the 'pricing_table'.


texts: How do I get ER Studio Data Architect to automatically add SCD Fields to my Dimensions?

texts:
This is my first try using this tool and Google searches have been fruitless.

How can I get ER Studio Data Architect to automatically add the SCD Maintenance Attributes (fields) to the tables I specify as Dimensions? I'm talking about StartDate, EndDate, and RowIsCurrent?

I'd love it if I could specify those fields and their data types once and not have to mess with it again and again.


texts:
From my misunderstanding, I stored time as timestamps and thought they were stored as UTC+0. But I moved into a different country and found the timestamps were stored in the local time.
The issue is that without noticing this fact, I generated new data and stored them into the same table. As a result, some timestamps are in UTC + 8 and some are in UTC+9. As I thought they were stored in UTC+0, all the input was converted into UTC+0 before it is sent to the MySQL server. Worst, I don't know which data are in UTC + 9 and which date are UTC + 8. I only knew that when I created the new data.
In this situation, is there a way I can make the timestamps consistent?

P.S. As a reference, I have a backup which I made just before I moved.


texts: How to disable HTML output in mysql console?

texts:
I've used several mysql instances on many servers, but this one had a suprise for me tonight when I tried to use its console for the first time :

me@MYSERVER:~$ mysql -H my_base (....)
(...)
Server version: 5.5.37-0ubuntu0.12.10.1 (Ubuntu)
(...)

mysql> show tables;

Tables_in_crocowords

T_BEST_LEVEL

T_CONNECTED

T_CONTEST

T_MONEY

T_MONEY_CREDIT

T_MONEY_DEBIT

T_PARTICIPATION

T_PERFECT

T_PLAYER

T_RANK

T_REPLAY

T_REPLAY_DATA

T_REPLAY_LEVEL

13 rows in set (0.00 sec)

mysql> select * from T_BEST_LEVEL LIMIT 3;

fid_contest	best_level	best_level_score

72	1	13

73	1	60

73	2	44

3 rows in set (0.00 sec)

All outputs are in HTML format !

I've tried to look and browse the documentations about this, but couldn't figure how to remove this html output.

Can you help me ?

:~$ mysql --version
mysql  Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (i686) using readline 6.2


texts: Data Dump Lag With Open Query (MySQL Linked to SQL Server 2016)

texts:
I have a SQL Server 2016 instance and a MySQL instance, where both are on seperate VMs running on the SAME physical server. The MySQL is linked to SQL Server.

My question is, what are possible latency causes for the data transfer? I ran a query on MySQL that executed in 9 seconds. The same query I ran via Linked Server in SSMS (using OPEN QUERY) and that took 56 seconds to execute.

I am trying to pinpoint the causes of the lag to fix this before scaling! All opinions and suggestions are greatly appreciated.


texts:
I have a table that has around 12 billion rows (row size 32 B) which grows each month by 300 million rows. I have setup partitions for each month and each month has 8 data files (since I currently have only 8 CPU's), to store the data I have a SAN attached.
The machine (W2008R2 64bit with MSSQL2008R2) has 192 GB RAM but I still feel it is slow when retrieving data while using the clustered index. I plan later on to use this data with SSRS. What should I consider to increase performance ?


texts: Why use synchronous_standby_names = 'barman_receive_wal' will cause database aberrant?

texts:
Use barman's synchronous_standby_names feature in PostgreSQL's postgresql.conf file
synchronous_standby_names = 'barman_receive_wal'

Can't do these operations:

createuser(hanging)
createdb(hanging)
can't save data in primary database if use repmgr for replication

Main configuration for them is
max_wal_senders = 10
max_replication_slots = 10
shared_preload_libraries = 'repmgr'
wal_level = 'replica'
archive_mode = on
archive_command = 'rsync -a %p barman@backup_server:/var/lib/barman/mydb/incoming/%f'
synchronous_standby_names = 'barman_receive_wal'

If remove synchronous_standby_names from this configuration, it works well.


texts: Adding and populating column in Postgres with data from existing column

texts:
I have a TEXT column in one of my existing database tables that holds both domain names and IP addresses. I'd like to add a new column to the existing table that's of type INET and populate it with the IP address from rows who's TEXT column is an IP so I can take advantage of the IP Address and IP Network features in Postgres.

Adding the column is easy enough, but when I go to run UPDATE foo SET ip_addr = data::inet I (expectedly) get an ERROR: invalid input syntax for type inet for the first row that's a domain name an the update stops.

Is there any way to skip the rows that aren't IP addresses, or am I going to have to write a function for this? I'm needing to do it as part of a migration using a Golang migration tool (github.com/mattes/migrate) so I was hoping the UPDATE command would be sufficient.


texts:
We have a 4h datawarehouse job that runs every 4h on 4h schedule. We want to create a new 'daily' schedule and have some processes run out of hours.

However, if when the daily job comes to run and the 4h job is still running, I would like it to wait for the 4h job to complete, then run (or have a specified duration). I haven't decided which is best yet. How is this possible? Thanks!

Please do not suggest 3rd party options as I have no control over the infrastructure.


texts: Unable to connect to an Oracle 12c Docker container

texts:
I'm working on a project with Oracle, and using a Oracle XE 11 container for testing.  But recent developments have ended up with me running up against a brick wall: I need larger table names.  Support for large table names was apparently added in version 12, so I'm trying to change out the container I'm using.
I'm setting up the container in Java using testcontainers:
db = new OracleContainer("epiclabs/docker-oracle-xe-11g");

When I look around on Docker Hub for Oracle 12 containers, I get a bunch of results, but every one I've tried so far, if I substitute its name in as the image name in the code above, it spins for 10-20 minutes, then errors out with

ORA-12514, TNS:Listener does not currently know of service requested in connect descriptor

And I have no idea what the problem is here.  I have plenty of SQL experience, but next to none with Oracle specifically, and trying to get this Oracle-related project working has been a bit of a disaster so far.  Does anyone know what's going wrong and how I can get connected to a Oracle 12 Docker container?


texts: MySQL / MariaDB Get last 7 days summarized and with avarages calculated

texts:
currently, I have this table:

CREATE TABLE `plant_data` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `plant_id` BIGINT(20) UNSIGNED NOT NULL,
    `temperature` DECIMAL(5,1) UNSIGNED NOT NULL,
    `light` SMALLINT(5) UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
)

with the following example data:

INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1623, 14, 22.2, 35, '2020-02-16 09:00:06', '2020-02-16 09:00:06');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1622, 5, 22.8, 33, '2020-02-16 09:00:06', '2020-02-16 09:00:06');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1621, 14, 22.8, 36, '2020-02-16 08:00:07', '2020-02-16 08:00:07');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1620, 5, 23.3, 33, '2020-02-16 08:00:07', '2020-02-16 08:00:07');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1619, 14, 23.1, 36, '2020-02-15 07:00:11', '2020-02-15 07:00:11');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1618, 5, 23.8, 34, '2020-02-15 07:00:11', '2020-02-15 07:00:11');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1617, 14, 24.4, 38, '2020-02-15 06:00:09', '2020-02-15 06:00:09');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1616, 5, 24.6, 34, '2020-02-15 06:00:09', '2020-02-15 06:00:09');

I want to get the average values of the last X days as one row for each day. I could do it with X querys for each day like

SELECT plant_id, avg(temperature) FROM plant_data WHERE created_at >= '2020-02-16 00:00:00' AND created_at <= '2020-02-17 00:00:00' GROUP BY plant_id;

But I want to know, if it is possible to get the data with one query to get a result like this:

+----------+------------------+------------+
| plant_id | avg(temperature) | day        |
+----------+------------------+------------+
|        5 |         24.58000 | 2020-02-16 |
|       14 |         24.42000 | 2020-02-16 |
|        5 |         23.58000 | 2020-02-15 |
|       14 |         23.42000 | 2020-02-15 |
+----------+------------------+------------+

Would be nice if someone has a good idea for doing this to save me query time.


texts: Importing a mysqldump from MySQL 5.0.95 to MariaDB 10.0.36: Some tables incomplete

texts:
I have an ancient MySQL setup (5.0.95) that I'm trying to shut down. We've mysqldumped the databases we need, and we're trying to import them into MariaDB (10.0.36). We're running into some odd quirks, and I suspect it's because some of the tables are InnoDB. Post-import, several .ibd files are showing up on the new server in /var/lib/mysql/dbname that aren't present on the old server. For each table that has an .ibd file, we've noticed that the data import is incomplete to varying degrees. For example, one table should have more than 4,000 rows in it, but after import, it only has 307. Also, of the three views present in the database, two import correctly, but one appears as an empty table.

Here's some output from the latest import attempt:

mysqldump -u -p live < live_dump_from_old_setup.sql

User time 8.74, System time 0.22
Maximum resident set size 14896, Integral resident set size 0
Non-physical pagefaults 5767, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 2780, Involuntary context switches 33

My application connects to the database successfully after the import, so no problem there, but a lot of the data is missing.

I'm fairly new to MariaDB, even though it's supposed to be a drop-in replacement for MySQL, so I'm sure I'm missing something. How can I ensure that my entire mysqldump is imported correctly?


texts: How to setup Master to Master Replication with Application auto failover

texts:
I have a requirement as follows , need to setup a Multi-Master replication with automatic application failover ,if one master goes down, Can anyone share me some information regarding this.

Thanks and Regards
Jabir Baig Mirza


texts: sp_BlitzFirst @AsOf Functionality Not Working?

texts:
I'm trying to run the sp_BlitzFirst stored procedure (using the 7/15/2016 version) with the @AsOf parameter, and I'm getting this error:

EXEC sp_BlitzFirst @AsOf = '2015-02-23 18:45', @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirstResults'

Setting up configuration variables
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

The SQL server version is 11.0.6020.0 (X64).

I used the example query from the documentation page and got this. It's choking on the triple ''' when trying to cast @AsOf to a varchar.

I've worked around this for now, but the documentation said to post here about errors thrown!


texts: What exactly is MySQL Spin rounds per wait in SEMAPHORES section?

texts:
I am facing slowness issue on MySQL Slave.
I am suspecting this due to OS I/O as i compared Spin rounds per wait values with PROD. Slave have higher values. Also slave has less resources compared to PROD.

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 572783, signal count 1796058
Mutex spin waits 15479300, rounds 12039758, OS waits 109005
RW-shared spins 618729, rounds 8406119, OS waits 254889
RW-excl spins 242434, rounds 7770788, OS waits 205984
Spin rounds per wait: 0.78 mutex, 13.59 RW-shared, 32.05 RW-excl


texts: Avoid tables locking while doing reindex

texts:
We are using PostgreSQL 10 and we need to reindex tables to reorganize data and free space.

The problem we met is that this operation locks tables that we need to remain available at all time.

We found a workaround, but this seems to free much less space than a standard reindex, so we turned that off.

Is there some solution that will allow us to reindex, and to have the table available all the time?


texts: SQL Server 2014 Restore DB only has "Verify Backup Media" option

texts:
I just downloaded SQL Server 2014 Management Studio. I got the backup files ".bak" from my colleague. When I try to do a restore from "Database > Restore Database...", the window dialog only has "Verify Backup Media" instead of "OK", "Cancel", etc.
Clicking the button the message on top of the dialog stated "Backup media verified successfully", but the database is not restored.
Did I miss out any configuration during installation or perhaps user permission issue? I'm using "sa" user.


texts:
I have a table where I'm currently aggregating ~200 rows per day of stats.  These stats include things like # of users, how many people using X feature, etc.

What I'd like to do is query this table so summarize the data in this way:

Daily over the past X days
Weekly over the past year - Take the stats from each Sunday and pulling the current day (assuming today isn't Sunday)
Monthly over the past X years - Take the stats from the last day of each month and pulling the current day (assuming today isn't the last day of the month)

Columns:

date is the day the data was aggregated
value is the numeric value I need to show (e.g. - # of users)

I want empty records/dates to be filled with zeros so I need to use mysql to generate the dates and then join a matching record where available.  How can I use mysql to determine the Sundays for the last year?

Unfortunately I can't provide an example of what I have working at the moment, I'm still investigating potential solutions.  Most queries I come across are querying records by dates, not generating the dates themselves.

My table structure:

CREATE TABLE `daily_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `stat` int(11) NOT NULL,
  `value` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts: How to run query to convert undefined JSON fields into a row dynamically

texts:
Related to this answer
I found a solution above to get undefined JSON files into rows.
However, I need to run query twice so I modify the query to run it once.
I tried to run the query returned from the FORMAT like below:
EXECUTE FORMAT(
  $$ SELECT * FROM %I.%I CROSS JOIN LATERAL json_to_record(%I::json) AS rs(%s); $$,
  'public',
  'vehicles',
  'column_a',
  array_to_string(
                (SELECT ARRAY(SELECT DISTINCT col FROM vehicles CROSS JOIN LATERAL json_object_keys(column_a::json) AS t(col) ORDER BY col)), ' text , '
  ) || ' text'
);

I got an error message saying:

ERROR:  prepared statement "format" does not exist
SQL state: 26000

I have tried the solution from here, but it is not working.
Any advice on how to run the query is welcomed. Thanks.


texts:
I'm trying to create a new user login with sql authentication and also an application role on a specific database. I can create the role and user fine, but when I try to alter the role to add the user as a member I get an error. I must be missing something obvious?

use ciq;

create role rl_saga_ciq_loader;

grant select on ciq.dbo.ciqcountrygeo to rl_saga_ciq_loader;

CREATE LOGIN usr_saga_ciq_loader_dev WITH PASSWORD=N'ItGoesOnForever!', DEFAULT_DATABASE = ciq;

alter role rl_saga_ciq_loader add member usr_saga_ciq_loader_dev;

EXEC sys.sp_addrolemember @rolename = 'rl_saga_ciq_loader', @membername = 'usr_saga_ciq_loader_dev'

Gives

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 35
User or role 'usr_saga_ciq_loader_dev' does not exist in this database.


texts: Can some redundancy in this nested SELECT COUNT query be eliminated?

texts:
For the record, this is not a homework question.  I am a professional brushing up on my SQL skills.

Given a table called world that looks like this,

I want to find the continents where all countries have a population <= 25000000. Specifically, I want the name, continent, and population of the countries associated with these continents.

I wrote this query, and it appears to be functionally correct:

SELECT name, continent, population
FROM world x
WHERE (
  SELECT COUNT(name)
  FROM world y
  WHERE y.population <= 25000000
  AND x.continent = y.continent
) = (
  SELECT COUNT(name)
  FROM world z
  WHERE x.continent = z.continent
)
ORDER BY continent, population DESC

At first glance, that query looks unnecessarily redundant.  I'd like to simplify the query (or improve it in other ways), but I'm unsure how to do that.  Can someone show some better alternatives?

MySQL or Postgres are preferable feature sets, if it matters.

The problem is exactly as it appears in exercise 9 of this page in sqlzoo.  Queries can be tested and iterated upon there as well.


texts: Restore plan cache and buffer pool after database restore?

texts:
When restoring a database, is there a way to restore the plan cache so it doesn't have to be re-created during production use?

Similar question: is there a way to pro-actively restore the buffer cache during or after a database restore?,…. other than to create and run select statements on the tables previously in the buffer cache, a solution which would be time-consuming to create, and inevitably inaccurate.

This is a followup to this question:  "Does restoring database clear buffer cache and stored proc cache".  edit:  This question is a follow-up because I want to know if there is a workaround to get the plan cache and buffer pool back to their source state.


texts: RDS PostgreSQL Transaction Logs grow with nightly backups

texts:
We have a production RDS for PostgreSQL instance that has a strange pattern of disk usage recurring in a manner consistent with the nightly backup schedule.

Nightly, the backup is automatically performed by RDS with no custom configuration beyond specifying the window. I am not sure if this is normal behavior or if something else is going on.
Something to consider here is that we have logical_replication turned on. We need this for a lambda function that reads and flushes the replication slot every 2 minutes. However, the metric for replicationSlotDiskUsage shows that the disk space consumed by the replication slot has never exceeded 10mb and cannot account for this enormous nightly dip in free disk space.

Upon further investigation, it seems clear that transaction log growth is responsible:

It is unclear to me why this is happening. Thank you for any insight that can be given on this issue. Also, I am more than happy to provide additional information that may be relevant, as I found it somewhat difficult to formulate this question without having the slightest idea where to start investigating.
My question:
What is causing the transaction logs to grow nightly during the automated backup window?


texts: Changing the "default database" opton in login

texts:
I am looking for a way to change the option of "default database" to master for all the logins on my instance from current user database in order to maintain the consistency.

Application code logins are all set to use " USE [DBNAME]" so changing all logins to master should be good.

Is there a way to have the script check first or list all logins with default databases and then listing/printing the changes to change the above?

Thanks


texts:
Is there any point at which you become so familiar with your language/database/system that there is no need to test a new feature/configuration/query/etc. by contained/simulated testing before implementing it in your system (especially concerning a feature that modifies data)? Or is it always essential to test a new query by simulation in a test environment?

To specify further, it is clear that it is always safest to test. However, is there a way to determine when the risk is so minimal that testing is not worth the effort? Another way of phrasing that: when or is it ever professional practice to take a measured risk to implement a feature?

Also, let's assume that everything is backed-up, so, worst-case scenario, the data could with some effort be restored.

Can someone cite specific, expert experience to address this? Please include references where appropriate/possible.


texts: How to split url in sql server like this

texts:
Here is the url

www.abc.com/a/b/c/d/e/f/g/h/i

I want result like this

www.abc.com/a/b/c/d/e/f/g/h/i
www.abc.com/a/b/c/d/e/f/g/h
www.abc.com/a/b/c/d/e/f/g
www.abc.com/a/b/c/d/e/f
www.abc.com/a/b/c/d/e
www.abc.com/a/b/c/d
www.abc.com/a/b/c
www.abc.com/a/b
www.abc.com/a
www.abc.com/

Please look out this code..

            declare @length as int
            declare @Totallength as int
            declare @min as int
            declare @strURL as varchar(max)
            set @strURL='www.abc.com/a/b/c/d/e/f/g/h/i'
            set @min=1
            set @length=(SELECT LEN(@strURL) - LEN(REPLACE(@strURL, '/', '')))
            set @Totallength=(select len(@strURL))
            if(@length>1)
            begin
            while(@min<=@length)
            begin
            --select @strURL=left(@strURL,charindex('/',@strURL)-1)
            SELECT  @strURL = SUBSTRING( @strURL, CHARINDEX('/', @strURL) + 1, @Totallength)
            print @strURL
            set @min=@min+1
            -- print @min
            end
            end


texts: What can cause TRUNCATE TABLE to take a really long time?

texts:
I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves).

I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands records.

For some reason, the TRUNCATE TABLE command takes really long time to execute (both on the master and on the slave). It takes about 400K ms to execute!! When it runs on the slave, it causes it to lag from the Master. After the TRUNCATE TABLE finishes, everything is back to normal.

I know that one of the slaves didn't receive any reads while performing the TRUNCATE TABLE since its a dedicated slave and the process that reads from that slave was down. Also, on this slave, it took the same amount of time to execute.

Here is the table structure: http://pastebin.com/qEQB4juR

Any thoughts on how I can speed up the TRUNCATE TABLE ?


texts:
I don't normally work with MySQL but with MS-SQL and am having issues restoring a dump backup of a 9 GB database. I converted it to MS-SQL and it takes a grand total of 4 minutes to restore but the MySQL DB takes over an hour on the same server. The MySQL database is using InnoDB but is there an alternative to speeding up the restores? Both databases are on the same machine, Windows 2008R2 in a VM with a dymanic SANs.

Correction - it takes MS-SQL 1 minute to restore, 1 hour to restore the same database in MySQL

EDIT: mysql.ini (with commented lines removed):

[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:\Program Files\MySQL\MySQL Server 5.5\"
datadir="C:\ProgramData\MySQL\MySQL Server 5.5\data\"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="CM999-SV510.log"
slow-query-log=0
slow_query_log_file="CM999-SV510-slow.log"
long_query_time=10
log-error="CM999-SV510.err"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=22M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=43M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=124M
innodb_log_file_size=63M
innodb_thread_concurrency=9


texts: ALTER TABLE command with no default value taking forever?

texts:
I'm attempting to add a NOT NULL constraint to a table I have (so I can use it as part of a primary key, so I can create a spatial index...) but it is taking a really long time - 1 hour 31 minutes and counting.

I had read this post where the answer seemed to be that it was taking so long because the person was adding a default value but i'm not doing that:

ALTER TABLE unacastVisitRaw ALTER COLUMN timestamp varchar(64) NOT NULL

The table is rather large (290 million records) but it still seems like a long time for just adding a constraint and not changing any values.

Is there anything I can do to speed up this operation?  Why would it be taking so long if I'm not updating the existing records?

UPDATE: Here are the wait stats for the session


texts: How to count unique records within a time range in MySQL?

texts:
I have a table with the below definition:

CREATE TABLE visit_log ( id INT NOT NULL , door_entry TIMESTAMP NOT NULL);

This table has the below records:

insert into visit_log values(1, '2009-04-15 15:00.000');
insert into visit_log values(1, '2009-04-15 15:20.000');
insert into visit_log values(1, '2009-04-15 15:55.000');
insert into visit_log values(2, '2009-04-15 15:00.000');
insert into visit_log values(2, '2009-04-15 15:40.000');
insert into visit_log values(2, '2009-04-15 16:20.000');
insert into visit_log values(3, '2009-04-15 15:00.000');

Now I want to find that how many times a person has visit_log within each 30 minutes, I mean all the visits at the range of 30 minutes should be counted as 1 visit, my desired result is shown below:

id         visits
 1            2
 2            3
 3            1

id 1 gets two visits because the first two visits are counted as one visit as they happened within the time span of 30 minutes, id 2 has three visits because all of three visits came in the time difference of more than 30 minutes and the id 3 has one visit.

Any help is appreciated in advance.


texts:
I'm using mongorestore to move a dump from a location to another. I've to use the quiet flag and I'm on Windows.
How can I check for restore progress? Actually I can only check the size of the db on the mongo shell. Is there a better way? Is there a directory in which I can find logs maybe?


texts:
On Postgresql 12, given:
postgres=# \d bip
                Table "public.bip"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
Indexes:
    "foo_idx_a" hash (a)

I then ran a explain select ...:
postgres=# explain select * from bip where a = 42;
                               QUERY PLAN
-------------------------------------------------------------------------
 Bitmap Heap Scan on bip  (cost=4.10..14.80 rows=13 width=4)
   Recheck Cond: (a = 42)
   ->  Bitmap Index Scan on foo_idx_a  (cost=0.00..4.10 rows=13 width=0)
         Index Cond: (a = 42)
(4 rows)

What is the meaning of the Recheck Cond: (a = 42) in the EXPLAIN output?


texts: Can I enable Login when I can't connect to the instance?

texts:
I have a cloned a customer machine to a Virtual machine.
I had access from my code (SqlConnection) to the database through a database-Login where I could use window autentication.

I disabled my Login(I though I could enable it again).

Is there any way to enable the Login?

The solutions I found where you Grant something, all require that you are connected to the instance, but it's also not possible to connect without first a login.

If I had the sa pwd I would be able to login, but I don't know that either.

Is there any other way, I can enable my Login?


texts: Is it possible in SQL Server to change default "order by" direction from asc to desc?

texts:
This question came up in a discussion at work... given the following T-SQL statement:

select firstname, lastname from users order by lastname;

the default sort order on the order by clause is asc.

Is there a setting within SQL Server that will allow you to change the default order used when the ASC|DESC modifiers are not specified?


texts: How can I get dates that wrap around new year from non-datetime columns?

texts:
I have a table in my MySQL database that stores daily data (an entry for each day of the year), but does not use a DATETIME column for this. This is because the data is the same for every year (it's climatology data, so the "normal" high temperature for Jan 7, 2015 is the same as for Jan 7, 2016 and so on). I have three fields that represent the day this data is valid for, so my table looks like:

month, day_of_month, day_of_year, value
1      1             1            23
1      2             2            22.95
...
12     31            365          23.12

Say I then want to get the values from this table from a certain date, like 01 Dec 2015 to 60 days from then, which would be 30 Jan 2016. Is there a way to get back the date and value for all of those days, working okay across the new year? Ideally I'd love results that flow like:

valid_date, value
...
2015-12-31  23.12
2016-01-01  23
2016-01-02  22.95
...

(hopefully you get the idea).

If you can think of a better way to store this static daily data so that I can achieve the above goal, I'm open to that as well! Thanks for your help!


texts:
In our company we have a lot of MySQL databases. Some of these are critical and we want to take a back-up of all databases. These databases can't be locked because the application can change these database at every moment. I want to write a script that makes a dump of the database an store it somewhere on a backupdisk. What is the best way to achieve this without locking databases and get a consistent dump off all databases from a remote server.

Thanks


texts: JOIN ON syntax error in Microsoft Access 2016

texts:
I am querying a couple tables, and for some reason Microsoft Access 2016 continually informed me that my SQL query JOIN ON syntax is incorrect. Specifically, the JOIN syntax is highlighted, followed by the ON syntax when removing that (the latter for clearer reasons based on prior knowledge of SQL JOIN ON syntax).

Here is the code:

SELECT Column1, Column2, Table1.Column3
FROM Table1
JOIN Table2
    ON Table1.Column3 = Table2.Column3
   AND Column4 = '{NAME}'
ORDER BY Column4;

Is there something incorrect about this syntax? By the way, this is using ANSI-89 Syntax.


texts: Disk write in iotop when doing mysql "Copy to tmp table"

texts:
I have just moved servers from a two-server configuration with the application and database server running separately to a one-server LAMP stack.

Unfortunately the issue that I have encountered on the new server is, when I run a query that requires copying the table to a tmp file "Mysql Status: Copying to tmp table" from what I can see in iotop, the mysql process writes up to approximately 100mb and then slows to a crawl until the file completes at about 400mb (this can take up to 20mins)

On the old server, we were able to write the 400mb to the tmp table within 20 seconds. Is there some setting in MySQL that regulates how fast it can write to the file system?


texts: Which field type is better to store specific play time in video for postgres?

texts:
Friend and I are making video service.

In video table, we store opening start point and opening end point of each video. For example,

opening start time: 00:01:35

opening end time: 00:03:04

At first, I made each column with time(6) type. But my friend told me each column should be interval type.

So in following case, which is better?

1) Each column is time(6) type.

2) Each column is interval type.

3) opening start time column is time(6) type, and opening elasped time is interval type


texts: Populate one table with data from another one on selected cells

texts:
I have a problem with filling empty cells in one table with data from another one.

I got a table with user emails - some of the fields are empty, some are already containing emails. I gathered the list of emails from the users who doesn't have emails in the table. I am going to import it as an separate table, and then update an exiting one. So to demonstrate:

Existing table (USERS):

ID      Email
1       aaa@bbb.com
2       NULL
3       bbb@bbb.com
4       NULL
5       NULL

The second table (MYTABLE) has exactly the same structure, but it has only ID and Emails of users who have NULL in original one.

I used this as a test, but find out that that code is overwriting emails if this user exist in both tables, and I don't want this:

UPDATE users
SET users.email = (SELECT mytable.email
                     FROM mytable
                     WHERE mytable.id = users.id)
WHERE EXISTS (SELECT mytable.email
                     FROM mytable
                     WHERE mytable.id = users.id);

So is it possible to update ONLY cells with NULL value?


texts:
I am attempting to prompt for a username and password when running a database creation script, and I want to hide any text input for my password prompt. In Oracle's sqlplus, I can use HIDE at the end of the line to suppress the user's input from appearing on the screen. Is there an equivalent for PostgreSQL's psql?

Current progress:

\prompt 'Enter database user password: ' ss_password


texts: Don't know why MySQL is not creating table with primary key constraint

texts:
I can't understand why MySQL showing this error:

  #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
  near 'CONSTRAINT (uname_pk) PRIMARY KEY, pwd Varchar(50), fname
  Varchar(50), lname ' at line 1

And this is the query:

create table uinfo_master(uname Varchar(50) NOT NULL CONSTRAINT (uname_pk) PRIMARY KEY,
pwd Varchar(50),
fname Varchar(50),
lname Varchar(50),
email Varchar(50),
phno Varchar(50),
address Varchar(200),
city Varchar(50),
state Varchar(50),
pin Varchar(50),
country Varchar(50));

What is wrong with my query, kindly show my mistake.


texts:
hope you can give me some pointers on this behavior

I have a NC Columnstore and in the same batch I want to drop this column store and "delete" some information.

But I get this error:
DELETE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the DELETE statement, then rebuilding the columnstore index after DELETE is complete.

For what I can image this is due to the compilation throwing the error.


texts: Possible reasons for really long database recovery

texts:
This is cross-posted at SSC, but restore is now going on 6 hours.

Hello,

Overnight I ran restores with NORECOVERY for a 5tb database, along with several other 250GB - 1.3tb databases. I did that because sometimes our 3rd party backup software throws wacky LSN errors if it thinks something timed out that really didn't. Anyway, the restore completed with no errors. This morning I started running the RESTORE WITH RECOVERY commands to bring them online for DBCC checks.

Ran on the first database fine, got stuck on the 5tb database for about an hour, then I ran the commands on the remaining six databases in parallel to the 5tb. All of them recovered very quickly. The 5tb database has been restoring now for about 3 hours.

Any thoughts on:

Why this in particular would be taking much longer than both:

a) how long it usually takes to recover, and
b) much longer than other databases took to recover?

If I should cancel/kill the transaction and try to start over

I can see it doing something in sp_WhoIsActive, i.e. informational columns are incrementing, and the wait info changes (though the wait that it always seems to be on is IO_COMPLETION), though status seems to always be suspended.

While researching I came across one article that suggested high VLF count can cause this, and there are about 5k VLFs in the log file, but I'm on a version of 2012 that is patched for the issue:

Microsoft SQL Server 2012 - 11.0.5532.0 (X64)
Jul 14 2014 15:00:27
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Thanks

EDIT:

This is on my DBA box that I use for offloading DBCC checks, so I'm using a good backup. The growth settings for these databases are controlled by best practices issued by the 3rd party vendor. I'm not allowed to touch them. They're set to percentages, which I disagree with, but it's out of my control. Management isn't open to changing because we charge clients by space used for hosting. I do monthly maintenance on VLFs but they have been this high before and I've run the restore with no issues.

EDIT 2:

Finished after 21 hours!

RESTORE DATABASE successfully processed 0 pages in 77454.736 seconds (0.000 MB/sec).


texts:
I store various events (such as a search on the site) into a table (in fact I have two tables: one for the event and other (linking to the first) with characteristics of the event).

Each city belongs to a region. The search may be restricted to either a city or a region (but not both).

The second table has ENUM('region','city',...) to store an region/city characteristic every time when a region or a city is searched.

Is it good idea to store two records when doing search restricted to a city: for the city itself and for its region?

It looks like a denormalization. But could SQL expert judge if it may be worth.


texts:
Im trying to setting up the Golden gate to sync the data to BigQuery. When I start pushing the initial load, my extractor exported all the data and even from the replicat stats Im able to see the records are received but its not pushed to Bigquery.

Extractor

GGSCI (bhuvi-oracle) 12> info extract load1

EXTRACT    LOAD1     Last Started 2020-05-07 16:52   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table HR.TEST
                     2020-05-07 16:52:41  Record 32768
Task                 SOURCEISTABLE

Replicat stats

GGSCI (bhuvi-1) 1> stats bq

Sending STATS request to REPLICAT BQ ...
gg.handlerlist=bigquery

Start of Statistics at 2020-05-07 16:53:33.

Replicating from HR.TEST to bhuvi_orcl.test:

*** Total statistics since 2020-05-07 16:52:42 ***
        Total inserts                                  32768.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               32768.00

Log file on BigData Golen Gate:

2020-05-07T16:59:04.895+0000  INFO    OGG-00987  Oracle GoldenGate Command Interpreter:  GGSCI command (root): start bq.
2020-05-07T16:59:04.904+0000  INFO    OGG-00963  Oracle GoldenGate Manager, mgr.prm:  Command received from GGSCI on host [10.18.0.61]:42130 (START REPLICAT BQ ).
2020-05-07T16:59:04.905+0000  INFO    OGG-00975  Oracle GoldenGate Manager, mgr.prm:  REPLICAT BQ starting.
2020-05-07T16:59:04.964+0000  INFO    OGG-00995  Oracle GoldenGate Delivery, bq.prm:  REPLICAT BQ starting.
2020-05-07T16:59:04.967+0000  INFO    OGG-03059  Oracle GoldenGate Delivery, bq.prm:  Operating system character set identified as UTF-8.
2020-05-07T16:59:04.967+0000  INFO    OGG-02695  Oracle GoldenGate Delivery, bq.prm:  ANSI SQL parameter syntax is used for parameter parsing.
2020-05-07T16:59:05.173+0000  INFO    OGG-15052  Oracle GoldenGate Delivery, bq.prm:  Using Java class path: .:ggjava/ggjava.jar:ggjava/resources/lib/optional/log4j-api-2.9.1.jar:ggjava/resources/lib/optional/log4j-core-2.9.1.jar:ggjava/resources/lib/optional/log4j-slf4j-impl-2.9.1.jar.
2020-05-07T16:59:10.243+0000  INFO    OGG-01815  Oracle GoldenGate Delivery, bq.prm:  Virtual Memory Facilities for: COM
                                  anon alloc: mmap(MAP_ANON)  anon free: munmap
                                  file alloc: mmap(MAP_SHARED)  file free: munmap
                                  target directories:
                                  /opt/ogg/dirtmp.
2020-05-07T16:59:10.243+0000  INFO    OGG-00996  Oracle GoldenGate Delivery, bq.prm:  REPLICAT BQ started.
2020-05-07T16:59:10.251+0000  INFO    OGG-02243  Oracle GoldenGate Delivery, bq.prm:  Opened trail file /opt/ogg/dirdat/oo000000 at 2020-05-07 16:59:10.249063.
2020-05-07T16:59:10.251+0000  INFO    OGG-03506  Oracle GoldenGate Delivery, bq.prm:  The source database character set, as determined from the trail file, is UTF-8.
2020-05-07T16:59:10.253+0000  INFO    OGG-06505  Oracle GoldenGate Delivery, bq.prm:  MAP resolved (entry HR.TEST): MAP "HR"."TEST", TARGET bhuvi_orcl.test.
2020-05-07T16:59:10.253+0000  INFO    OGG-02756  Oracle GoldenGate Delivery, bq.prm:  The definition for table HR.TEST is obtained from the trail file.
2020-05-07T16:59:10.253+0000  INFO    OGG-06511  Oracle GoldenGate Delivery, bq.prm:  Using following columns in default map by name: ID.
2020-05-07T16:59:10.253+0000  INFO    OGG-06510  Oracle GoldenGate Delivery, bq.prm:  Using the following key columns for target table bhuvi_orcl.test: ID.


texts:
Does anyone know what the net effect of having a block of t-sql with a transaction isolation level of repeatble read and then inside having a select with nolock.

Does the nolock become invalid because the isolation level has already been set?


texts: Understanding "Date Last Modified" for "System Stored Procedures"

texts:
Whilst reviewing code on a SQL 2008R2 Instance I inspected the Date Last Modified of the System Stored Procedures of the databases. The ones in the DBO schema had a datetime that predates the installation however the ones in the dbo schema come later than the installation date. Specifically the machine was built December 2013 and the date of these SPs is 09/07/2014 17:16 (both for Date Last Modified and Create Date). The DB in question was also created December 2013.

Now I assume this was something benign, but how can I understand and determine what caused this date stamp?

I've checked the Windows logs but can't find any installations going on at that time.


texts: Any way to provide Sqlplans to end users who don't have a DBMS

texts:
I work on a cloud application using SQL Server (2008R2 or 2012 depending on the server). Recently some clients have requested the ability to view query plans themselves.

I can get the query plan xml without difficulty, but I've just realised that the majority of these clients aren't going to have a SQL Server available to them to open the *.sqlplan file that we've normally used inhouse for examining them.

Is there any way to provide the sqlplan files to these clients without them requiring SQL Server to view them? It's preferred if I can do it directly via Powershell and SQL, but I'm open to anything.


texts: Inserting into table depending on conditions

texts:
I'm trying to insert data into table but depending on the content it is supposed to be done different ways. My question is whether there exists such function that will work like follows:

WHEN Condition1 THEN INSERT INTO data1 SELECT * FROM table1

WHEN Condition2 THEN INSERT INTO data2 SELECT * FROM table2

...

ELSE SELECT 'done'


texts: MYSQL count based on conditions in same column

texts:
l have the following data sample

CREATE TABLE IF NOT EXISTS `tbl_result` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(10) NOT NULL,
  `subject_id` int(5) NOT NULL,
  `score` int(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

INSERT INTO `tbl_result` (`id`, `student_id`, `subject_id`, `score`) VALUES
(3, 'R135722F', 7112, 43),
(4, 'R135722F', 6118, 55),
(5, 'R135722F', 2076, 45),
(6, 'R135722F', 4402, 76),
(7, 'R135722F', 2234, 34),
(8, 'R134567Y', 6118, 65),
(9, 'R134567Y', 2076, 87),
(10, 'R134567Y', 4402, 43),
(11, 'R134567Y', 2234, 65),
(12, 'R134567Y', 1111, 34),
(13, 'R134567Y', 3454, 65),
(14, 'R134567Y', 3456, 87),
(15, 'R137634H', 7112, 98),
(16, 'R137634H', 6118, 54),
(17, 'R137634H', 2076, 45),
(18, 'R137634H', 4402, 33),
(19, 'R137634H', 2234, 65),
(20, 'R137634H', 2234, 65),
(21, 'R137634H', 1111, 12),
(22, 'R137634H', 3454, 54);

How do l retrieve the following how many students passed the following subjects ie

Those who passed 3 or subjects including subject code 7112 , 6118
Those who passed 4 or more including subject code 1111 , 3454

l have tried to use the in function but it  did not produce desired result

    SELECT COUNT( * )
FROM  `tbl_result`
WHERE  `subject_id`
IN (
 '7112',  '6118'
)

ANd the following but failing . also tried this one

SELECT *
FROM  `tbl_result`
WHERE  `subject_id` =  '7112'
OR  `subject_id` =  '6118'
AND  `score` >49
GROUP BY  `student_id


texts: How do I list all tables in all schemas owned by the current user in Postgresql?

texts:
I can list all tables in all schemas using

> \dt *.*

but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by me in the public schema and any schemas I've defined.

I'm hoping to find a way to do this without having to explicitly add schemas to the search path as I create them as described here:

https://stackoverflow.com/a/12902069

EDIT:

Based on the accepted answer, I've created the following View:

create view my_tables as
select table_catalog, table_schema, table_name, table_type
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema');

And now the following command gives me what I wanted:

select * from my_tables;


texts:
There's data sitting in OpenEdge Progress databases. We'd like to replicate that data to a PostgreSQL "datalake" using a software called HVR (high volume replication I think). What the software does is basically install a local agent and replicate data from a source DB to a target DB.

Does anybody know if Progress allows such a thing?

I know it has its own replication mechanisms but that's not what we're looking for.


texts:
I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk upsert is executed (using INSERT ... ON CONFLICT DO UPDATE statement). Batch size is 4000.

My question is - what performance tips can you give me? When the table was smaller (5-10 million records), the performance was good enough. With 30 million rows it is not good enough, single bulk of 4000 records lasts from 2 to 30 seconds.

Of course, I have few services which do this import in parallel, so I am using advisory locks to synchronize them (only one bulk upsert is being executed at a time). Should I remove advisory locks to execute upserts in parallel? Then I will have to handle deadlocks (and use smaller batch size to reduce deadlock chance?).

What can I do to increase performance of bulk upsert?

Here is my_big_table:

CREATE TABLE my_big_table (
  sender VARCHAR(30) PRIMARY KEY,
  count  INTEGER NOT NULL DEFAULT 0
)
WITH (
  fillfactor = 80,
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold = 40000
);

Here is the UPSERT query:

INSERT INTO my_big_table AS MBT (sender, count)
    SELECT destination, count(*) as received_count
    FROM my_temp_table
    GROUP BY destination
ON CONFLICT (sender) DO UPDATE
    SET count = MBT.count + excluded.count;

Here is execution plan:
https://explain.depesz.com/s/HGfw

Additional info:

I am using Postgres version 11.2
my_big_table has fill factor of 80%
my data set cannot fit into memory (RAM)


texts: How to create encryption certificate using PowerShell in Windows Server 2012 R2?

texts:
I'm implementing the Always Encrypted feature in SQL Server 2016.
I have tried to create the certificate using PowerShell in Windows 10 environment as per the below article.

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-and-store-column-master-keys-always-encrypted?view=sql-server-2017

However, when I tried to execute the same scripts on Windows Server 2012 R2 environment, script fails and not able to generate the certificate.

Could anyone help me on this?


texts: Good idea to include summary of frequently accessed data from another foreign-keyed table?

texts:
Sorry for poor title, I'll try to explain it in more detail here.

I have an application that often gets a row from table A, then looks up a column from that in a foreign key mapping table (table B), and finally looks up several rows with the corresponding foreign key in table C.

My question is: would it be better for table A to have a column containing data in the particular column we often want from table C? This would be anti-normalization, but would save on a lot of queries.

As an example, let's say we have a table "french words" (table A), with columns like:

id, word, pronunciation, frequency

We also have a table (table B) that maps the "french words" id to any number of "german words" id (table C)

And then in "german words" (table C) we also have id, word, pronunciation,  frequency.

When we look up a French word, we often want to get the highest frequency German word that it's associated with via table B. Would it be better to just include a highest_frequency_german_word column in the French words table?


texts: copy an exported dump file to a target DB instance - LOG

texts:
I just imported data into an Amazon RDS DB instance using this guide

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html

But in the source DB the number of tables is 309 whereas that for the target DB is only 4

select count('1') from dba_tables where owner='NUNITO';

Is there a way to see the log files ?

I used

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'sample.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'exp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''SCHEMA_1'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/


texts: Unable to open .sps.bak database backup file with SQL Server 2012 Express edition

texts:
I am using SQL Server 2012 Express edition. Client sent me the SQL Server backup file with the extension .sps.bak. I am not able to open it with SSMS. Please somebody help me here.

I get the following error message.

  Restore of database failed.
  System.Data.SqlClient.SqlError: the
  database was backed up on a server running version 10.50.1600. That
  version is incompatible with this server, which is running 10.00.1600.
  Either restore the database on a server that supports the backup or
  use a backup that is compatible with this server.

I cannot open the database that was developed with a professional edition on an Express edition?


texts: SQL access query to count and delete rows > n

texts:
Can someone help me with a SQL (Access) query to delete all rows greater than n entries ordered by ID?

I.e. say I have 1200 rows of data with ID incremented . I need delete the first amount leaving only  200.

If I have only 199 rows of data, then I need to preserve them all.


texts:
1) The pgpass.conf (of PostgreSQL) is:

localhost:5432:*:postgres:mypassword

I have no experience with batch and I have spent the day searching in the net.
I'm using Windows - if that matters.

2) I tried with a file.bat as:

cd C:
start "" "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" "C:\Users\utente\Desktop\1Giswater impNode.sql"
pause
start "" "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" "C:\Users\utente\Desktop\2Giswater impArc.sql"
pause
start "" "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" "C:\Users\utente\Desktop\3assegnazione enet.sql"
pause

After typing my password the result is:

3) I've written a simple file.bat, which is:

cd C:\Program Files\PostgreSQL\9.4\bin\
pause
rem psql --help
pause
psql   -e -a -f="C:\Users\utente\Desktop\1Giswater impNode.sql"   -d=postgres  -U=postgres

The result is:

What am I missing?


texts: Time stamp of Active and Inactive session by JDBC

texts:
The following explains how exactly the Middle tier handles the connection pool and connections to the DB (Oracle 10g):

Inactive session in Oracle by JDBC

What I would like to know from DB point of view is at which point in time a particular session from the connection pool has become active and how long is that particular session or session ID is been active and idle?

The following gets me the no.of sessions made to the DB

( select sysdate,
         TRUNC(logon_time,''HH''),
         COUNT(*) NO_OF_SESSIONS,
         username
  from v$session
  where username IS NOT NULL
  GROUP BY TRUNC(logon_time,''HH''),
           USERNAME
)


texts: Unable to start the mongo services on debian server

texts:
Mongo DB keep on crashing automatically and all database and collection remove automatically.This is happening randomly and when I check mongod.log I can see it's automatically dropped.

Today I got the error message that mongo service unable to start, and when I start it services it give me the error

Job for mongod.service failed. See 'systemctl status mongod.service' and 'journalctl -xn' for details.

when I type this command

systemctl status mongod.service

Debian Server Configuration are: 40GB HHD 4GB RAM

I have around 2 million data as of now in mongoDB and it will increase day by day.

I have down so much of google but didn't find the perfect solution and as I'm not an expert at server level ,so can some give a suggestion or solution to how can I get rid with this problem.


texts:
I'm aware there are already many questions alike however none of those answers helped me achieve row rankings in a table.

I have a table set up as so:

+----------------------------------+---------------+--------+
| HEX(UUID)                        | Username      | Rating |
+----------------------------------+---------------+--------+
| 0000F659C5854C9DB8BE8984FEC8CFD9 | Aidan8or_     |   1600 |
| 00022E3531F44C36AE058B3CF063C02F | rob160502     |   1600 |
| 000399B665D14979B3C0AF309112625F | HauntedCorpse |   1536 |
| 0003BAC2C0764C0C9FE20C4A3C701D7E | Glitchx0R     |   1234 |
| 000597056E564BF587DAA1D31E51E29F | jkz101022     |   455  |
+----------------------------------+---------------+--------+

What I would like to do here is select a single row with a ranking appended to it (1, 2, 3, 10, 100, ...). If you haven't already guessed it, the rating is an ELO Rating therefore many scores are tied and I need to accommodate for that when creating leaderboards.

I've taken a look at this: Get the rank of a user in a score table and it had some really good answers but I just couldn't get any of them working correctly.

I've tried all those solutions and got close with this:

SELECT HEX(UUID) as UUID, Username, Rating, FIND_IN_SET( Rating, (
  SELECT GROUP_CONCAT( Rating
  ORDER BY Rating DESC )
  FROM table_name )
  ) AS rank
  FROM table_name
WHERE Username = 'jkz101022'

however returns the ranking as 0.


texts: Database Shrinking as a logged operation

texts: I would like to know if shrinking a data file is a logged operation or not. I do not have a definite answer to this. I have heard people say it is a logged operation but so far I did not see an evidence of this.


texts: Create tablespace "error in creating database file, access denied (OS 5)"

texts:
I try to create a database file in this location

I've checked the route and everything, I've tried several times to create the tablespace like this...

...but as you can see, I was just lead to several errors. What can I do? What am I doing wrong?


texts: How to update DynamoDB item by searching on GSI primary key?

texts:
Table has these columns on DynamoDB:

primaryKey
sortKey
GSI_primaryKey
data

And there is GSI added with primary key GSI_primaryKey and sort key on primaryKey

How to update column data if I have only the value of the GSI_primaryKey?

With NodeJS document client.

Thanks :)


texts: Cost of replaying transaction log on sql server HA replica

texts:
I've been reading about the sql azure scale out.

I suppose it makes senses to leverage the replicas hardware for read-only workloads.

But it got me wondering, which is actually doing more work? The primary or the replicas?

In particular it's not clear to me how expensive it is for the the replicas to  'catch up' on the transaction log from the primary.

If I understand correctly, if the primary executes a query like UPDATE dbo.LargeTable SET [SomeColumn] = 10, then it would probably do an index scan to update all the rows, but the transaction log will actually contain one entry per row? That would mean the replica would need to do a key lookup for each row instead of an index scan, which would be a lot more expensive.

I'm sure my logic is wrong somewhere but I'd love to understand more about this.

Is this assumed that catching up on the primary is low cost (relative to the cost incurred by the primary to actually run the transaction)?

Also, are the physical pages of the replicas exactly identical to the primary at the byte level? (i.e. fragmentation is the same too) Or are there just logically the same?


texts:
As I understand it, when you send a query to Postgres like:

SELECT id FROM table1 WHERE name = $1;

Postgres will create a query plan.  The plan will be cached for the same query in the same session.  But if you create a function:

CREATE OR REPLACE FUNCTION get_table1 (parname text) RETURNS bigint
LANGUAGE plpgsql AS
$$BEGIN
  RETURN (SELECT id FROM table1 where name = parname);
END$$;

The query plan will be cached for all future sessions.

I'd like to verify this theory by checking how often the query analyzer is invoked.  Is there a way to inspect the number of times Postgres parses a query?

If possible, I'd like to monitor things like #parses per second and the min/max/avg parse duration.


texts: Are there any disadvantages to shrinking a database?

texts:
My production server's database grows unexpectedly and almost I am going to create a job for shrinking the database which runs once in a month. Is it a good idea?

The backups was failed and the time of failure huge transactions were logged which rapidly grows the DB.

I am using SQL Server 2008 R2.


texts: pg_dump --exclude-table not working on one specific table

texts:
Wanting to exclude a few very large table in a pg_dump, I use the -T option multiple times, like this:
pg_dump --clean clientportal -T article_plain -T callstats -T cdrviewer_cdr -T calls -T cdrviewer_datasource -T django_sesssion -T django_admin_log

article_plain however still gets included in the dump.  Using --schema-only here for demonstration purposes:
postgres@server:~/bin$ pg_dump -s --clean clientportal -T article_plain -T callstats -T cdrviewer_cdr -T calls -T cdrviewer_datasource -T django_sesssion -T django_admin_log | grep article_plain
ALTER TABLE ONLY otrs.article_plain DROP CONSTRAINT fk_article_plain_create_by_id;
ALTER TABLE ONLY otrs.article_plain DROP CONSTRAINT fk_article_plain_change_by_id;
ALTER TABLE ONLY otrs.article_plain DROP CONSTRAINT fk_article_plain_article_id_id;
DROP INDEX otrs.article_plain_article_id;
ALTER TABLE ONLY otrs.article_plain DROP CONSTRAINT article_plain_pkey;
ALTER TABLE otrs.article_plain ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE otrs.article_plain_id_seq;
DROP TABLE otrs.article_plain;
-- Name: article_plain; Type: TABLE; Schema: otrs; Owner: sg1admin
CREATE TABLE article_plain (                                                 <----- there is article_plain
ALTER TABLE article_plain OWNER TO sg1admin;
...

Why is that?


texts: how to drop a db2 database that is in an inconsistent state

texts:
I have a DB2 database that suffered a power outage in the storage array, its a test system I just want to kill it and rebuild.
db2 connect to mydb
SQL1015N  The database is in an inconsistent state.  SQLSTATE=55025

db2 drop db mydb
SQL1035N  The operation failed because the specified database cannot be
connected to in the mode requested.  SQLSTATE=57019

db2 restart db mydb
SQL1015N  The database is in an inconsistent state.  SQLSTATE=55025

db2stop
SQL1025N  The database manager was not stopped because databases are still active.

db2 backup db mydb to /dev/null
SQL1015N  The database is in an inconsistent state.  SQLSTATE=55025


texts:
I have a table called VHS_Metadata_Aug52014 with columns:

Title, Description,Tag, Speaker, VIP, FilmingLocation, Notes

I'm trying to write a query that returns all records for which the word "News" is not in any value. There are indeed records of this type. However, the query below returns nothing. So, I'm guessing I'm not quite understanding how to do it. I'm using MS Access, where the wildcard is *.

UPDATE: I just tried also using the syntax:

Not VHS_Metadata_Aug52014!Description like '*News*'

And I'm still getting no hits.

select * from VHS_Metadata_Aug52014 where (
VHS_Metadata_Aug52014!Title Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!Description Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!Tag Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!Speaker Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!VIP Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!FilmingLocation Not like '*' & 'News' & '*'
And
VHS_Metadata_Aug52014!Notes Not like '*' & 'News' & '*'
) ;


texts: What is considered for output from an sql server job step?

texts:
In short: It seems I only get PRINT messages to my SQL Server job history. What is the definition of valid history output?

Background: Just executing a statement like UPDATE product SET ... will leave no trace in the job history (no matter whether "Include step output in history" is checked or not). Running the same statement in Query Analyzer shows
"(X row(s) affected)".

By creating a variable and assigning it the @@ROWCOUNT contents after the update I can build and PRINT a message that will include the number of affected rows. This will be included in the job history.
This works fine, but I'd like to know the definition of output (that is written to history) from a Transact-SQL Script job Step. What is included except PRINTed strings?

One might easily think that the history would show exactly the same as is shown in the Messages pane of the Query window (formerly known as the Query Analyzer) of SQL Server Management Studio – but that is not the case.


texts: Best Way to Index an INT Column Used for BitWise Queries

texts:
What is the best way to index a column for an efficient look-up based on a bitwise comparison in the WHERE clause e.g.

SELECT ID, FLAGS, NAME FROM TABLE WHERE FLAGS & 4 = 4

I am in a SQL server 2012 environment.

I understand that an index on the FLAGS column won't be used in this type of query.

I know the other option is to break the FLAGS column out into individual columns, but I wanted to avoid that because in some cases, that would mean 25+ new columns, each of which would need a combination of indexes to support the common queries.

Are there any ways to index the single column so that the bitwise queries are more efficient?

I tried using a computed column with an index as suggested by dudu below, but when I used in on a table which has a clustered index, and start selecting columns (instead of just select count(*) it no longer uses the index on that new computed column.

SELECT
*
INTO
    MYTABLE
FROM
(
    SELECT 'A' COL_A, 'A' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'B' COL_A, 'D' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'C' COL_A, 'S' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'D' COL_A, 'F' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'E' COL_A, 'T' COL_B, CAST(128 AS INT) FLAGS UNION
    SELECT 'F' COL_A, 'D' COL_B, CAST(128 AS INT) FLAGS UNION
    SELECT 'G' COL_A, 'D' COL_B, CAST(128 AS INT) FLAGS UNION
    SELECT 'H' COL_A, 'X' COL_B, CAST(128 AS INT) FLAGS
) SRC

CREATE CLUSTERED INDEX IX_CLU_A ON MYTABLE(COL_A)

ALTER TABLE MYTABLE ADD FLAG_BIT_1 AS (CAST ((FLAGS & 1) AS BIT))
CREATE INDEX MYTABLE_IX_FLAG_BIT_1 ON MYTABLE (FLAG_BIT_1)

SELECT * FROM MYTABLE WHERE FLAGS & 1 = 1
SELECT * FROM MYTABLE WHERE FLAG_BIT_1 = 1

SELECT COUNT(*) FROM MYTABLE WHERE FLAGS & 1 = 1
SELECT COUNT(*) FROM MYTABLE WHERE FLAG_BIT_1 = 1


texts:
I am creating a site where users can sell their products(let say a little version of eBay, just assumption). Concerning the database design am a little bit stuck and looking for some ideas how it is done in real world.
My initial thoughts

I wanted to create one database for each user (seller). But I realize that if let say 70,000 people (70,000 is just assumption) want to sell, then 70,000 databases must be created. I think this may not the right idea. Or?

I am also thinking of creating one database for all sellers. I don't know if it is the best thing to do.

Questions

My question is what is the best way of designing a database for the above purpose?

Sites like eBay, how do they design their database for such large people to sell? Do they use one database, or each user has his own database?
I have googled "eBay database design" but the results i got were not what i was looking for. The results were too high level(not how they designed their database).

Is it advisable to create for each user a sub-domain?

A pointer to where I can find how such systems are designed will be appreciated.
I hope someone can help me with some specific answers.
Thank you


texts:
I'm having an issue getting MySQL Server 5.6 (community edition) running. It was working just fine until I restarted my server. Now it won't start at all.

After doing a bit of research, I discovered there is a log file that it writes to, which has helped a little, but I'm not entirely sure how to fix the issue it's reporting.

After doing more research I discovered trying to start mysqld manually using the options "--standalone" and "--debug" might help, but they don't seem to make a difference, and I've looked everywhere for the mysterious debug log, but was unable to find it. I checked in the C drive root, and in MySQL server folders, but nothing. Here is the most recent .err file report:

  2014-09-18 18:47:29 2240 [Note] Plugin 'FEDERATED' is disabled.

  2014-09-18 18:47:29 2240 [Note] InnoDB: Using atomics to ref count buffer pool pages

  2014-09-18 18:47:29 2240 [Note] InnoDB: The InnoDB memory heap is disabled

  2014-09-18 18:47:29 2240 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions

  2014-09-18 18:47:29 2240 [Note] InnoDB: Memory barrier is not used

  2014-09-18 18:47:29 2240 [Note] InnoDB: Compressed tables use zlib 1.2.3

  2014-09-18 18:47:29 2240 [Note] InnoDB: Not using CPU crc32 instructions

  2014-09-18 18:47:29 2240 [Note] InnoDB: Initializing buffer pool, size = 128.0M

  2014-09-18 18:47:29 2240 [Note] InnoDB: Completed initialization of buffer pool

  2014-09-18 18:47:33 2240 [Note] InnoDB: Highest supported file format is Barracuda.

  2014-09-18 18:47:33 2240 [Note] InnoDB: The log sequence numbers 1625977 and 1625977 in ibdata
  files do not match the log sequence number 28347554 in the ib_logfiles!

  2014-09-18 18:47:33 2240 [Note] InnoDB: Database was not shutdown normally!

  2014-09-18 18:47:33 2240 [Note] InnoDB: Starting crash recovery.

  2014-09-18 18:47:33 2240 [Note] InnoDB: Reading tablespace information from the .ibd files...

  2014-09-18 18:47:34 2240 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous
  tablespace osticket/ost_ticket__cdata uses space ID: 54 at filepath: .\osticket\ost_ticket__cdata.ibd. Cannot open tablespace osticket_2/ost_api_key which uses space ID: 54 at filepath: .\osticket_2\ost_api_key.ibd

  InnoDB: Error: could not open single-table tablespace file .\osticket_2\ost_api_key.ibd

  InnoDB: We do not continue the crash recovery, because the table may become

  InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.

  InnoDB: To fix the problem and start mysqld:

  InnoDB: 1) If there is a permission problem in the file and mysqld cannot

  InnoDB: open the file, you should modify the permissions.

  InnoDB: 2) If the table is not needed, or you can restore it from a backup,

  InnoDB: then you can remove the .ibd file, and InnoDB will do a normal

  InnoDB: crash recovery and ignore that table.

  InnoDB: 3) If the file system or the disk is broken, and you cannot remove

  InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf

  InnoDB: and force InnoDB to continue crash recovery here.

Any clue as to what might fix this problem? I'd prefer a solution that doesn't involve losing my MySQL database files/entries, as I've already had to redo over half of my SQL-connected web services, and would prefer not to have to do it again.


texts: I have run truncate table but it is still showing table occupying space?

texts:
I have run truncate table but the Heidi SQL is still showing table occupying space.
Database: MYSQL 5.6.26

I have truncated the con_consumer table but heidi is still showing it taking space. Even other tools like Toad shows it taking up space.
Mysql Engine:Inno DB


texts: Should I assimilate all address lines to a single line?

texts:
I'm new here, so I'll keep my question straight and short. I am building a webapp that lets users select some product, submit their details and place an order.

Currently, my ORDER table contains only one field for the address. I was planning to write code to assimilate all address lines to one, including the state, city, etc. I will then add functions to retrieve the address field and parse it for state, city, etc.

Is this a good approach, or will the database be faster if there is seperate field for states, cities and the like?